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

[19 Sep 13:23] Laurents Meyer
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.
[19 Sep 13:27] Laurents Meyer
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;
[25 Sep 16:11] Bogdan Kecman
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
[26 Sep 7:32] Guilhem Bichot
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'
[13 Nov 14:41] Guilhem Bichot
See similar request here:

https://stackoverflow.com/questions/58777342/issue-with-all-mysql-select-queries-containin...
[17 Nov 17:33] Roy Lyseng
See also bug#35242.