MySQL Bugs: #96946: Outer reference in join condition isn't allowed

Bug #96946	Outer reference in join condition isn't allowed
Submitted:	19 Sep 2019 13:23	Modified:	22 Jan 2020 4:40
Reporter:	Laurents Meyer (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.17	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
There is LATERAL support for InnoDB since 8.0.14.

It will not work though, if the reference from the derived table to the column of a preceding table is part of a join search condition.

The following (example) query will not run:

SELECT `o`.`OrderDate`
FROM `Orders` AS `o`
LEFT JOIN LATERAL (
	SELECT `c`.`ContactName`
	FROM `Customers` AS `c`
    INNER JOIN (
        SELECT `od`.`Discount`
        FROM `Order Details` AS `od`
        WHERE `od`.`Quantity` > 1
    ) AS `od1` ON `o`.`OrderID` = `od1`.`OrderID`
) AS `c1` ON `o`.`CustomerID` = `c1`.`CustomerID`;

It returns the following error:
Error Code: 1054. Unknown column 'o.OrderID' in 'on clause'

That is, because MySQL does not recognize `o`.`OrderID` from the following line:
    ) AS `od1` ON `o`.`OrderID` = `od1`.`OrderID`

Rewriting the query, so that `o`.`OrderID` is not part of the join search condition will work:

SELECT `o`.`OrderDate`
FROM `Orders` AS `o`
LEFT JOIN LATERAL (
	SELECT `c`.`ContactName`
	FROM `Customers` AS `c`,
    (
        SELECT `od`.`OrderID`, `od`.`Discount`
        FROM `Order Details` AS `od`
        WHERE `od`.`Quantity` > 1
    ) AS `od1`
    WHERE `o`.`OrderID` = `od1`.`OrderID`
) AS `c1` ON `o`.`CustomerID` = `c1`.`CustomerID`;

How to repeat:
Create the following tables:

CREATE TABLE `Orders` (
  `OrderID` int(11) NOT NULL AUTO_INCREMENT,
  `CustomerID` varchar(255) DEFAULT NULL,
  `OrderDate` datetime(6) DEFAULT NULL,
  PRIMARY KEY (`OrderID`),
  KEY `IX_Orders_CustomerID` (`CustomerID`),
  CONSTRAINT `FK_Orders_Customers_CustomerID` FOREIGN KEY (`CustomerID`) REFERENCES `customers` (`CustomerID`) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE `Customers` (
  `CustomerID` varchar(255) NOT NULL,
  `CompanyName` longtext,
  `ContactName` longtext,
  `ContactTitle` longtext,
  `Address` longtext,
  `City` longtext,
  `Region` longtext,
  `PostalCode` longtext,
  `Country` longtext,
  `Phone` longtext,
  `Fax` longtext,
  PRIMARY KEY (`CustomerID`)
) ENGINE=InnoDB;

CREATE TABLE `Order Details` (
  `OrderID` int(11) NOT NULL,
  `ProductID` int(11) NOT NULL,
  `UnitPrice` decimal(65,30) NOT NULL,
  `Quantity` smallint(6) NOT NULL,
  `Discount` float NOT NULL,
  PRIMARY KEY (`OrderID`,`ProductID`),
  KEY `IX_Order Details_ProductID` (`ProductID`),
  CONSTRAINT `FK_Order Details_Orders_OrderID` FOREIGN KEY (`OrderID`) REFERENCES `orders` (`OrderID`) ON DELETE CASCADE,
  CONSTRAINT `FK_Order Details_Products_ProductID` FOREIGN KEY (`ProductID`) REFERENCES `products` (`ProductID`) ON DELETE CASCADE
) ENGINE=InnoDB;

Run the query.

Suggested fix:
The reference from the derived table to the column of a preceding table should also work as part of a join search condition.

Please remove the CONSTRAINTS from the tables, to make them work correctly (they are part of the larger Northwind database):

CREATE TABLE `Orders` (
  `OrderID` int(11) NOT NULL AUTO_INCREMENT,
  `CustomerID` varchar(255) DEFAULT NULL,
  `OrderDate` datetime(6) DEFAULT NULL,
  PRIMARY KEY (`OrderID`),
  KEY `IX_Orders_CustomerID` (`CustomerID`)
) ENGINE=InnoDB;

CREATE TABLE `Customers` (
  `CustomerID` varchar(255) NOT NULL,
  `CompanyName` longtext,
  `ContactName` longtext,
  `ContactTitle` longtext,
  `Address` longtext,
  `City` longtext,
  `Region` longtext,
  `PostalCode` longtext,
  `Country` longtext,
  `Phone` longtext,
  `Fax` longtext,
  PRIMARY KEY (`CustomerID`)
) ENGINE=InnoDB;

CREATE TABLE `Order Details` (
  `OrderID` int(11) NOT NULL,
  `ProductID` int(11) NOT NULL,
  `UnitPrice` decimal(65,30) NOT NULL,
  `Quantity` smallint(6) NOT NULL,
  `Discount` float NOT NULL,
  PRIMARY KEY (`OrderID`,`ProductID`),
  KEY `IX_Order Details_ProductID` (`ProductID`)
) ENGINE=InnoDB;

Hi,

Rather easy to verify, thanks for your report. I'll see what our Innodb & Standards team will say about it, IMO it is a bug.

kind regards

Thanks for the report.
For completeness, this isn't specific of LATERAL, happens with ordinary subqueries too:

- Condition in WHERE, works:

SELECT `o`.`OrderDate` FROM `Orders` AS `o` where o.OrderDate IN ( SELECT `c`.`ContactName` FROM `Customers` AS `c`     INNER JOIN `Order Details` AS `od`  ON 1 WHERE `o`.`OrderID` = `od`.`Discount` ) ; 

- Condition in ON, fails:
SELECT `o`.`OrderDate` FROM `Orders` AS `o` where o.OrderDate IN ( SELECT `c`.`ContactName` FROM `Customers` AS `c`     INNER JOIN `Order Details` AS `od`  ON `o`.`OrderID` = `od`.`Discount` );

ERROR 1054 (42S22): Unknown column 'o.OrderID' in 'on clause'

See similar request here:

https://stackoverflow.com/questions/58777342/issue-with-all-mysql-select-queries-containin...

See also bug#35242.

Documented fix as follows in the MySQL 8.0.20 changelog:

    References to columns from tables of outer query blocks in an ON
    condition of a JOIN did not work, and could be used only in a
    WHERE. The fix for this problem means that a query such as this
    one now works correctly:

SELECT o.order_date FROM orders o 
WHERE o.order_date IN  ( SELECT c.contact_name FROM customers c     
                    INNER JOIN order_details od
                    ON o.order_id = od.discount );  

    Previously this had to be rewritten as as shown here:

SELECT o.order_date FROM orders o 
WHERE o.order_date IN  ( SELECT c.contact_name FROM customers c
                    INNER JOIN order_details od
                    ON 1 
                    WHERE o.order_id = od.discount );  

    References to other tables of the same FROM clause as the JOIN,
    as in the query SELECT * FROM t1 CROSS JOIN (t2 LEFT JOIN t3 ON
    t1.c=3) are not outer references and remain forbidden. In this
    case, a lateral join is required, like this: SELECT * FROM t1
    JOIN LATERAL (SELECT * FROM t2 LEFT JOIN t3 ON t1.c=3).

Closed.

(Note: This also fixes BUG#35242.)