Description:
When using a LEFT JOIN in an outer query, that is applied over the result of an inner query that uses a LEFT JOIN LATERAL, the first match of the inner query will be used to resolve all the rows of the outer query (instead of combining every row from the outer query with every matching row from the inner query).
With the script applied from `How to repeat`, run the following query:
SELECT `l2`.`Id` AS `l2_Id`, `l2`.`OneToMany_Optional_Inverse2Id` AS `l2_OneToMany_Optional_Inverse2Id`, `j1`.`l3_Id`
FROM `LevelTwo` AS `l2`
LEFT JOIN LATERAL (
SELECT `t1`.`l3_Id`
FROM (
SELECT `l3`.`Id` AS `l3_Id`
FROM `LevelThree` AS `l3`
WHERE (`l2`.`Id` = `l3`.`OneToMany_Optional_Inverse3Id`) AND ((`l3`.`Name` <> 'Foo') OR `l3`.`Name` IS NULL)
ORDER BY `l3`.`Id`
LIMIT 1
) AS `t1`
LEFT JOIN `LevelFour` AS `l4a` ON `t1`.`l3_Id` = `l4a`.`OneToMany_Optional_Inverse4Id`
LEFT JOIN `LevelFour` AS `l4b` ON `t1`.`l3_Id` = `l4b`.`OneToMany_Required_Inverse4Id`
) AS `j1` ON TRUE;
The result is as follows, which is correct and expected:
+-------+----------------------------------+-------+
| l2_Id | l2_OneToMany_Optional_Inverse2Id | l3_Id |
+-------+----------------------------------+-------+
| 1 | NULL | 2 |
| 3 | NULL | NULL |
| 5 | NULL | NULL |
| 7 | NULL | NULL |
| 9 | NULL | NULL |
| 11 | NULL | NULL |
| 2 | 1 | 4 |
| 4 | 1 | NULL |
| 6 | 1 | NULL |
| 8 | 1 | NULL |
| 10 | 1 | NULL |
+-------+----------------------------------+-------+
11 rows in set (0.00 sec)
We will now add one more outer query around the query we just ran. The resulting query structure will look like this:
SELECT `l1`.`Id` AS `l1_Id`, `j2`.`l2_Id`, `j2`.`l2_OneToMany_Optional_Inverse2Id`, `j2`.`l3_Id`
FROM `LevelOne` AS `l1`
LEFT JOIN (
--
-- Our query we previously ran.
--
) AS `j2` ON `l1`.`Id` = `j2`.`l2_OneToMany_Optional_Inverse2Id`
ORDER BY `l1`.`Id`, `j2`.`l2_Id`, `j2`.`l3_Id`;
As can be seen, we are LEFT JOINing the `Id` column of the outer query on the `l2_OneToMany_Optional_Inverse2Id` column of the inner query.
The table of the outer query has exactly 1 row with an `Id` of 1. Therefore, we expect the result to have 5 rows with a matching (NOT NULL) `l2_OneToMany_Optional_Inverse2Id` value, of which one row will have an `l3_Id` value of 4, while the 4 remaining matching rows will have an `l3_Id` value of NULL.
However, that is not the case. Run the following full query:
SELECT `l1`.`Id` AS `l1_Id`, `j2`.`l2_Id`, `j2`.`l2_OneToMany_Optional_Inverse2Id`, `j2`.`l3_Id`
FROM `LevelOne` AS `l1`
LEFT JOIN (
SELECT `l2`.`Id` AS `l2_Id`, `l2`.`OneToMany_Optional_Inverse2Id` AS `l2_OneToMany_Optional_Inverse2Id`, `j1`.`l3_Id`
FROM `LevelTwo` AS `l2`
LEFT JOIN LATERAL (
SELECT `t1`.`l3_Id`
FROM (
SELECT `l3`.`Id` AS `l3_Id`
FROM `LevelThree` AS `l3`
WHERE (`l2`.`Id` = `l3`.`OneToMany_Optional_Inverse3Id`) AND ((`l3`.`Name` <> 'Foo') OR `l3`.`Name` IS NULL)
ORDER BY `l3`.`Id`
LIMIT 1
) AS `t1`
LEFT JOIN `LevelFour` AS `l4a` ON `t1`.`l3_Id` = `l4a`.`OneToMany_Optional_Inverse4Id`
LEFT JOIN `LevelFour` AS `l4b` ON `t1`.`l3_Id` = `l4b`.`OneToMany_Required_Inverse4Id`
) AS `j1` ON TRUE
) AS `j2` ON `l1`.`Id` = `j2`.`l2_OneToMany_Optional_Inverse2Id`
ORDER BY `l1`.`Id`, `j2`.`l2_Id`, `j2`.`l3_Id`;
The result is an unexpected:
+-------+-------+----------------------------------+-------+
| l1_Id | l2_Id | l2_OneToMany_Optional_Inverse2Id | l3_Id |
+-------+-------+----------------------------------+-------+
| 1 | 2 | 1 | 4 |
| 1 | 4 | 1 | 4 |
| 1 | 6 | 1 | 4 |
| 1 | 8 | 1 | 4 |
| 1 | 10 | 1 | 4 |
| 2 | NULL | NULL | NULL |
| 3 | NULL | NULL | NULL |
| 4 | NULL | NULL | NULL |
| 5 | NULL | NULL | NULL |
| 6 | NULL | NULL | NULL |
| 7 | NULL | NULL | NULL |
| 8 | NULL | NULL | NULL |
| 9 | NULL | NULL | NULL |
| 10 | NULL | NULL | NULL |
| 11 | NULL | NULL | NULL |
| 12 | NULL | NULL | NULL |
| 13 | NULL | NULL | NULL |
+-------+-------+----------------------------------+-------+
17 rows in set (0.00 sec)
It *should* have looked like this:
+-------+-------+----------------------------------+-------+
| l1_Id | l2_Id | l2_OneToMany_Optional_Inverse2Id | l3_Id |
+-------+-------+----------------------------------+-------+
| 1 | 2 | 1 | 4 |
| 1 | 4 | 1 | NULL |
| 1 | 6 | 1 | NULL |
| 1 | 8 | 1 | NULL |
| 1 | 10 | 1 | NULL |
| 2 | NULL | NULL | NULL |
| 3 | NULL | NULL | NULL |
| 4 | NULL | NULL | NULL |
| 5 | NULL | NULL | NULL |
| 6 | NULL | NULL | NULL |
| 7 | NULL | NULL | NULL |
| 8 | NULL | NULL | NULL |
| 9 | NULL | NULL | NULL |
| 10 | NULL | NULL | NULL |
| 11 | NULL | NULL | NULL |
| 12 | NULL | NULL | NULL |
| 13 | NULL | NULL | NULL |
+-------+-------+----------------------------------+-------+
17 rows in set (0.00 sec)
How to repeat:
Run the following SQL script:
-- MySQL dump 10.13 Distrib 8.0.22, for Win64 (x86_64)
--
-- Host: localhost Database: ComplexNavigations
-- ------------------------------------------------------
-- Server version 8.0.22
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `LevelFour`
--
DROP TABLE IF EXISTS `LevelFour`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `LevelFour` (
`Id` int NOT NULL,
`Name` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
`Level3_Required_Id` int NOT NULL,
`Level3_Optional_Id` int DEFAULT NULL,
`OneToOne_Optional_PK_Inverse4Id` int DEFAULT NULL,
`OneToMany_Required_Inverse4Id` int NOT NULL,
`OneToMany_Optional_Inverse4Id` int DEFAULT NULL,
`OneToOne_Optional_Self4Id` int DEFAULT NULL,
`OneToMany_Required_Self_Inverse4Id` int NOT NULL,
`OneToMany_Optional_Self_Inverse4Id` int DEFAULT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `IX_LevelFour_Level3_Required_Id` (`Level3_Required_Id`),
UNIQUE KEY `IX_LevelFour_Level3_Optional_Id` (`Level3_Optional_Id`),
UNIQUE KEY `IX_LevelFour_OneToOne_Optional_PK_Inverse4Id` (`OneToOne_Optional_PK_Inverse4Id`),
UNIQUE KEY `IX_LevelFour_OneToOne_Optional_Self4Id` (`OneToOne_Optional_Self4Id`),
KEY `IX_LevelFour_OneToMany_Optional_Inverse4Id` (`OneToMany_Optional_Inverse4Id`),
KEY `IX_LevelFour_OneToMany_Optional_Self_Inverse4Id` (`OneToMany_Optional_Self_Inverse4Id`),
KEY `IX_LevelFour_OneToMany_Required_Inverse4Id` (`OneToMany_Required_Inverse4Id`),
KEY `IX_LevelFour_OneToMany_Required_Self_Inverse4Id` (`OneToMany_Required_Self_Inverse4Id`),
CONSTRAINT `FK_LevelFour_LevelFour_OneToMany_Optional_Self_Inverse4Id` FOREIGN KEY (`OneToMany_Optional_Self_Inverse4Id`) REFERENCES `LevelFour` (`Id`) ON DELETE RESTRICT,
CONSTRAINT `FK_LevelFour_LevelFour_OneToMany_Required_Self_Inverse4Id` FOREIGN KEY (`OneToMany_Required_Self_Inverse4Id`) REFERENCES `LevelFour` (`Id`) ON DELETE RESTRICT,
CONSTRAINT `FK_LevelFour_LevelFour_OneToOne_Optional_Self4Id` FOREIGN KEY (`OneToOne_Optional_Self4Id`) REFERENCES `LevelFour` (`Id`) ON DELETE RESTRICT,
CONSTRAINT `FK_LevelFour_LevelThree_Id` FOREIGN KEY (`Id`) REFERENCES `LevelThree` (`Id`) ON DELETE RESTRICT,
CONSTRAINT `FK_LevelFour_LevelThree_Level3_Optional_Id` FOREIGN KEY (`Level3_Optional_Id`) REFERENCES `LevelThree` (`Id`) ON DELETE RESTRICT,
CONSTRAINT `FK_LevelFour_LevelThree_Level3_Required_Id` FOREIGN KEY (`Level3_Required_Id`) REFERENCES `LevelThree` (`Id`) ON DELETE RESTRICT,
CONSTRAINT `FK_LevelFour_LevelThree_OneToMany_Optional_Inverse4Id` FOREIGN KEY (`OneToMany_Optional_Inverse4Id`) REFERENCES `LevelThree` (`Id`) ON DELETE RESTRICT,
CONSTRAINT `FK_LevelFour_LevelThree_OneToMany_Required_Inverse4Id` FOREIGN KEY (`OneToMany_Required_Inverse4Id`) REFERENCES `LevelThree` (`Id`) ON DELETE RESTRICT,
CONSTRAINT `FK_LevelFour_LevelThree_OneToOne_Optional_PK_Inverse4Id` FOREIGN KEY (`OneToOne_Optional_PK_Inverse4Id`) REFERENCES `LevelThree` (`Id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `LevelFour`
--
LOCK TABLES `LevelFour` WRITE;
/*!40000 ALTER TABLE `LevelFour` DISABLE KEYS */;
INSERT INTO `LevelFour` (`Id`, `Name`, `Level3_Required_Id`, `Level3_Optional_Id`, `OneToOne_Optional_PK_Inverse4Id`, `OneToMany_Required_Inverse4Id`, `OneToMany_Optional_Inverse4Id`, `OneToOne_Optional_Self4Id`, `OneToMany_Required_Self_Inverse4Id`, `OneToMany_Optional_Self_Inverse4Id`) VALUES (1,'L4 01',10,10,1,1,NULL,NULL,1,2),(2,'L4 02',9,NULL,NULL,1,1,NULL,1,NULL),(3,'L4 03',8,8,3,1,NULL,NULL,2,4),(4,'L4 04',7,NULL,NULL,1,1,NULL,3,NULL),(5,'L4 05',6,6,5,1,NULL,NULL,4,6),(6,'L4 06',5,NULL,NULL,1,1,NULL,5,NULL),(7,'L4 07',4,4,7,1,NULL,NULL,6,8),(8,'L4 08',3,NULL,NULL,1,1,NULL,7,NULL),(9,'L4 09',2,2,9,1,NULL,NULL,8,10),(10,'L4 10',1,NULL,NULL,1,1,NULL,9,NULL);
/*!40000 ALTER TABLE `LevelFour` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `LevelOne`
--
DROP TABLE IF EXISTS `LevelOne`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `LevelOne` (
`Id` int NOT NULL,
`Name` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
`Date` datetime(6) NOT NULL,
`OneToOne_Optional_Self1Id` int DEFAULT NULL,
`OneToMany_Required_Self_Inverse1Id` int NOT NULL,
`OneToMany_Optional_Self_Inverse1Id` int DEFAULT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `IX_LevelOne_OneToOne_Optional_Self1Id` (`OneToOne_Optional_Self1Id`),
KEY `IX_LevelOne_OneToMany_Optional_Self_Inverse1Id` (`OneToMany_Optional_Self_Inverse1Id`),
KEY `IX_LevelOne_OneToMany_Required_Self_Inverse1Id` (`OneToMany_Required_Self_Inverse1Id`),
CONSTRAINT `FK_LevelOne_LevelOne_OneToMany_Optional_Self_Inverse1Id` FOREIGN KEY (`OneToMany_Optional_Self_Inverse1Id`) REFERENCES `LevelOne` (`Id`) ON DELETE RESTRICT,
CONSTRAINT `FK_LevelOne_LevelOne_OneToMany_Required_Self_Inverse1Id` FOREIGN KEY (`OneToMany_Required_Self_Inverse1Id`) REFERENCES `LevelOne` (`Id`) ON DELETE RESTRICT,
CONSTRAINT `FK_LevelOne_LevelOne_OneToOne_Optional_Self1Id` FOREIGN KEY (`OneToOne_Optional_Self1Id`) REFERENCES `LevelOne` (`Id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `LevelOne`
--
LOCK TABLES `LevelOne` WRITE;
/*!40000 ALTER TABLE `LevelOne` DISABLE KEYS */;
INSERT INTO `LevelOne` (`Id`, `Name`, `Date`, `OneToOne_Optional_Self1Id`, `OneToMany_Required_Self_Inverse1Id`, `OneToMany_Optional_Self_Inverse1Id`) VALUES (1,'L1 01','2001-01-01 00:00:00.000000',10,1,2),(2,'L1 02','2002-02-02 00:00:00.000000',9,1,NULL),(3,'L1 03','2003-03-03 00:00:00.000000',8,2,4),(4,'L1 04','2004-04-04 00:00:00.000000',7,3,NULL),(5,'L1 05','2005-05-05 00:00:00.000000',6,4,6),(6,'L1 06','2006-06-06 00:00:00.000000',NULL,5,NULL),(7,'L1 07','2007-07-07 00:00:00.000000',NULL,6,8),(8,'L1 08','2008-08-08 00:00:00.000000',NULL,7,NULL),(9,'L1 09','2009-09-09 00:00:00.000000',NULL,8,10),(10,'L1 10','2010-10-10 00:00:00.000000',NULL,9,NULL),(11,'L1 11','2009-11-11 00:00:00.000000',NULL,11,NULL),(12,'L1 12','2008-12-12 00:00:00.000000',NULL,1,NULL),(13,'L1 13','2007-01-01 00:00:00.000000',NULL,2,NULL);
/*!40000 ALTER TABLE `LevelOne` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `LevelThree`
--
DROP TABLE IF EXISTS `LevelThree`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `LevelThree` (
`Id` int NOT NULL,
`Name` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
`Level2_Required_Id` int NOT NULL,
`Level2_Optional_Id` int DEFAULT NULL,
`OneToOne_Optional_PK_Inverse3Id` int DEFAULT NULL,
`OneToMany_Required_Inverse3Id` int NOT NULL,
`OneToMany_Optional_Inverse3Id` int DEFAULT NULL,
`OneToOne_Optional_Self3Id` int DEFAULT NULL,
`OneToMany_Required_Self_Inverse3Id` int NOT NULL,
`OneToMany_Optional_Self_Inverse3Id` int DEFAULT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `IX_LevelThree_Level2_Required_Id` (`Level2_Required_Id`),
UNIQUE KEY `IX_LevelThree_Level2_Optional_Id` (`Level2_Optional_Id`),
UNIQUE KEY `IX_LevelThree_OneToOne_Optional_PK_Inverse3Id` (`OneToOne_Optional_PK_Inverse3Id`),
UNIQUE KEY `IX_LevelThree_OneToOne_Optional_Self3Id` (`OneToOne_Optional_Self3Id`),
KEY `IX_LevelThree_OneToMany_Optional_Inverse3Id` (`OneToMany_Optional_Inverse3Id`),
KEY `IX_LevelThree_OneToMany_Optional_Self_Inverse3Id` (`OneToMany_Optional_Self_Inverse3Id`),
KEY `IX_LevelThree_OneToMany_Required_Inverse3Id` (`OneToMany_Required_Inverse3Id`),
KEY `IX_LevelThree_OneToMany_Required_Self_Inverse3Id` (`OneToMany_Required_Self_Inverse3Id`),
CONSTRAINT `FK_LevelThree_LevelThree_OneToMany_Optional_Self_Inverse3Id` FOREIGN KEY (`OneToMany_Optional_Self_Inverse3Id`) REFERENCES `LevelThree` (`Id`) ON DELETE RESTRICT,
CONSTRAINT `FK_LevelThree_LevelThree_OneToMany_Required_Self_Inverse3Id` FOREIGN KEY (`OneToMany_Required_Self_Inverse3Id`) REFERENCES `LevelThree` (`Id`) ON DELETE RESTRICT,
CONSTRAINT `FK_LevelThree_LevelThree_OneToOne_Optional_Self3Id` FOREIGN KEY (`OneToOne_Optional_Self3Id`) REFERENCES `LevelThree` (`Id`) ON DELETE RESTRICT,
CONSTRAINT `FK_LevelThree_LevelTwo_Id` FOREIGN KEY (`Id`) REFERENCES `LevelTwo` (`Id`) ON DELETE RESTRICT,
CONSTRAINT `FK_LevelThree_LevelTwo_Level2_Optional_Id` FOREIGN KEY (`Level2_Optional_Id`) REFERENCES `LevelTwo` (`Id`) ON DELETE RESTRICT,
CONSTRAINT `FK_LevelThree_LevelTwo_Level2_Required_Id` FOREIGN KEY (`Level2_Required_Id`) REFERENCES `LevelTwo` (`Id`) ON DELETE RESTRICT,
CONSTRAINT `FK_LevelThree_LevelTwo_OneToMany_Optional_Inverse3Id` FOREIGN KEY (`OneToMany_Optional_Inverse3Id`) REFERENCES `LevelTwo` (`Id`) ON DELETE RESTRICT,
CONSTRAINT `FK_LevelThree_LevelTwo_OneToMany_Required_Inverse3Id` FOREIGN KEY (`OneToMany_Required_Inverse3Id`) REFERENCES `LevelTwo` (`Id`) ON DELETE RESTRICT,
CONSTRAINT `FK_LevelThree_LevelTwo_OneToOne_Optional_PK_Inverse3Id` FOREIGN KEY (`OneToOne_Optional_PK_Inverse3Id`) REFERENCES `LevelTwo` (`Id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `LevelThree`
--
LOCK TABLES `LevelThree` WRITE;
/*!40000 ALTER TABLE `LevelThree` DISABLE KEYS */;
INSERT INTO `LevelThree` (`Id`, `Name`, `Level2_Required_Id`, `Level2_Optional_Id`, `OneToOne_Optional_PK_Inverse3Id`, `OneToMany_Required_Inverse3Id`, `OneToMany_Optional_Inverse3Id`, `OneToOne_Optional_Self3Id`, `OneToMany_Required_Self_Inverse3Id`, `OneToMany_Optional_Self_Inverse3Id`) VALUES (1,'L3 01',10,9,1,1,NULL,10,1,2),(2,'L3 02',9,NULL,NULL,1,1,9,1,NULL),(3,'L3 03',8,7,3,1,NULL,8,2,4),(4,'L3 04',7,NULL,NULL,1,2,7,3,NULL),(5,'L3 05',6,5,6,1,NULL,6,4,6),(6,'L3 06',5,NULL,NULL,1,1,NULL,5,NULL),(7,'L3 07',4,4,8,1,NULL,NULL,6,8),(8,'L3 08',3,NULL,NULL,1,2,NULL,7,NULL),(9,'L3 09',2,2,10,1,NULL,NULL,8,10),(10,'L3 10',1,NULL,NULL,1,1,NULL,9,NULL);
/*!40000 ALTER TABLE `LevelThree` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `LevelTwo`
--
DROP TABLE IF EXISTS `LevelTwo`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `LevelTwo` (
`Id` int NOT NULL,
`Name` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
`Date` datetime(6) NOT NULL,
`Level1_Required_Id` int NOT NULL,
`Level1_Optional_Id` int DEFAULT NULL,
`OneToOne_Optional_PK_Inverse2Id` int DEFAULT NULL,
`OneToMany_Required_Inverse2Id` int NOT NULL,
`OneToMany_Optional_Inverse2Id` int DEFAULT NULL,
`OneToOne_Optional_Self2Id` int DEFAULT NULL,
`OneToMany_Required_Self_Inverse2Id` int NOT NULL,
`OneToMany_Optional_Self_Inverse2Id` int DEFAULT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `IX_LevelTwo_Level1_Required_Id` (`Level1_Required_Id`),
UNIQUE KEY `IX_LevelTwo_Level1_Optional_Id` (`Level1_Optional_Id`),
UNIQUE KEY `IX_LevelTwo_OneToOne_Optional_PK_Inverse2Id` (`OneToOne_Optional_PK_Inverse2Id`),
UNIQUE KEY `IX_LevelTwo_OneToOne_Optional_Self2Id` (`OneToOne_Optional_Self2Id`),
KEY `IX_LevelTwo_OneToMany_Optional_Inverse2Id` (`OneToMany_Optional_Inverse2Id`),
KEY `IX_LevelTwo_OneToMany_Optional_Self_Inverse2Id` (`OneToMany_Optional_Self_Inverse2Id`),
KEY `IX_LevelTwo_OneToMany_Required_Inverse2Id` (`OneToMany_Required_Inverse2Id`),
KEY `IX_LevelTwo_OneToMany_Required_Self_Inverse2Id` (`OneToMany_Required_Self_Inverse2Id`),
CONSTRAINT `FK_LevelTwo_LevelOne_Id` FOREIGN KEY (`Id`) REFERENCES `LevelOne` (`Id`) ON DELETE RESTRICT,
CONSTRAINT `FK_LevelTwo_LevelOne_Level1_Optional_Id` FOREIGN KEY (`Level1_Optional_Id`) REFERENCES `LevelOne` (`Id`) ON DELETE RESTRICT,
CONSTRAINT `FK_LevelTwo_LevelOne_Level1_Required_Id` FOREIGN KEY (`Level1_Required_Id`) REFERENCES `LevelOne` (`Id`) ON DELETE RESTRICT,
CONSTRAINT `FK_LevelTwo_LevelOne_OneToMany_Optional_Inverse2Id` FOREIGN KEY (`OneToMany_Optional_Inverse2Id`) REFERENCES `LevelOne` (`Id`) ON DELETE RESTRICT,
CONSTRAINT `FK_LevelTwo_LevelOne_OneToMany_Required_Inverse2Id` FOREIGN KEY (`OneToMany_Required_Inverse2Id`) REFERENCES `LevelOne` (`Id`) ON DELETE RESTRICT,
CONSTRAINT `FK_LevelTwo_LevelOne_OneToOne_Optional_PK_Inverse2Id` FOREIGN KEY (`OneToOne_Optional_PK_Inverse2Id`) REFERENCES `LevelOne` (`Id`) ON DELETE RESTRICT,
CONSTRAINT `FK_LevelTwo_LevelTwo_OneToMany_Optional_Self_Inverse2Id` FOREIGN KEY (`OneToMany_Optional_Self_Inverse2Id`) REFERENCES `LevelTwo` (`Id`) ON DELETE RESTRICT,
CONSTRAINT `FK_LevelTwo_LevelTwo_OneToMany_Required_Self_Inverse2Id` FOREIGN KEY (`OneToMany_Required_Self_Inverse2Id`) REFERENCES `LevelTwo` (`Id`) ON DELETE RESTRICT,
CONSTRAINT `FK_LevelTwo_LevelTwo_OneToOne_Optional_Self2Id` FOREIGN KEY (`OneToOne_Optional_Self2Id`) REFERENCES `LevelTwo` (`Id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `LevelTwo`
--
LOCK TABLES `LevelTwo` WRITE;
/*!40000 ALTER TABLE `LevelTwo` DISABLE KEYS */;
INSERT INTO `LevelTwo` (`Id`, `Name`, `Date`, `Level1_Required_Id`, `Level1_Optional_Id`, `OneToOne_Optional_PK_Inverse2Id`, `OneToMany_Required_Inverse2Id`, `OneToMany_Optional_Inverse2Id`, `OneToOne_Optional_Self2Id`, `OneToMany_Required_Self_Inverse2Id`, `OneToMany_Optional_Self_Inverse2Id`) VALUES (1,'L2 01','2010-10-10 00:00:00.000000',10,10,1,1,NULL,10,1,2),(2,'L2 02','2002-02-02 00:00:00.000000',9,NULL,NULL,1,1,9,1,NULL),(3,'L2 03','2008-08-08 00:00:00.000000',8,8,3,1,NULL,8,2,4),(4,'L2 04','2004-04-04 00:00:00.000000',7,NULL,NULL,1,1,7,3,NULL),(5,'L2 05','2006-06-06 00:00:00.000000',6,6,5,1,NULL,6,4,6),(6,'L2 06','2005-05-05 00:00:00.000000',5,NULL,NULL,1,1,NULL,5,NULL),(7,'L2 07','2007-07-07 00:00:00.000000',4,4,7,1,NULL,NULL,6,8),(8,'L2 08','2003-03-03 00:00:00.000000',3,NULL,NULL,1,1,NULL,7,NULL),(9,'L2 09','2009-09-09 00:00:00.000000',2,2,9,1,NULL,NULL,8,10),(10,'L2 10','2001-01-01 00:00:00.000000',1,NULL,NULL,1,1,NULL,9,NULL),(11,'L2 11','2000-01-01 00:00:00.000000',11,NULL,NULL,1,NULL,NULL,1,NULL);
/*!40000 ALTER TABLE `LevelTwo` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Dumping events for database 'ComplexNavigations'
--
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2020-10-22 16:39:41
--
-- Queries:
--
SELECT `l2`.`Id` AS `l2_Id`, `l2`.`OneToMany_Optional_Inverse2Id` AS `l2_OneToMany_Optional_Inverse2Id`, `j1`.`l3_Id`
FROM `LevelTwo` AS `l2`
LEFT JOIN LATERAL (
SELECT `t1`.`l3_Id`
FROM (
SELECT `l3`.`Id` AS `l3_Id`
FROM `LevelThree` AS `l3`
WHERE (`l2`.`Id` = `l3`.`OneToMany_Optional_Inverse3Id`) AND ((`l3`.`Name` <> 'Foo') OR `l3`.`Name` IS NULL)
ORDER BY `l3`.`Id`
LIMIT 1
) AS `t1`
LEFT JOIN `LevelFour` AS `l4a` ON `t1`.`l3_Id` = `l4a`.`OneToMany_Optional_Inverse4Id`
LEFT JOIN `LevelFour` AS `l4b` ON `t1`.`l3_Id` = `l4b`.`OneToMany_Required_Inverse4Id`
) AS `j1` ON TRUE;
SELECT `l1`.`Id` AS `l1_Id`, `j2`.`l2_Id`, `j2`.`l2_OneToMany_Optional_Inverse2Id`, `j2`.`l3_Id`
FROM `LevelOne` AS `l1`
LEFT JOIN (
SELECT `l2`.`Id` AS `l2_Id`, `l2`.`OneToMany_Optional_Inverse2Id` AS `l2_OneToMany_Optional_Inverse2Id`, `j1`.`l3_Id`
FROM `LevelTwo` AS `l2`
LEFT JOIN LATERAL (
SELECT `t1`.`l3_Id`
FROM (
SELECT `l3`.`Id` AS `l3_Id`
FROM `LevelThree` AS `l3`
WHERE (`l2`.`Id` = `l3`.`OneToMany_Optional_Inverse3Id`) AND ((`l3`.`Name` <> 'Foo') OR `l3`.`Name` IS NULL)
ORDER BY `l3`.`Id`
LIMIT 1
) AS `t1`
LEFT JOIN `LevelFour` AS `l4a` ON `t1`.`l3_Id` = `l4a`.`OneToMany_Optional_Inverse4Id`
LEFT JOIN `LevelFour` AS `l4b` ON `t1`.`l3_Id` = `l4b`.`OneToMany_Required_Inverse4Id`
) AS `j1` ON TRUE
) AS `j2` ON `l1`.`Id` = `j2`.`l2_OneToMany_Optional_Inverse2Id`
ORDER BY `l1`.`Id`, `j2`.`l2_Id`, `j2`.`l3_Id`;
Suggested fix:
When using a LEFT JOIN in an outer query, that is applied over the result of an inner query that uses a LEFT JOIN LATERAL, every row of the outer query needs to be combined with every matching row of the inner query (or use NULL values).
Description: When using a LEFT JOIN in an outer query, that is applied over the result of an inner query that uses a LEFT JOIN LATERAL, the first match of the inner query will be used to resolve all the rows of the outer query (instead of combining every row from the outer query with every matching row from the inner query). With the script applied from `How to repeat`, run the following query: SELECT `l2`.`Id` AS `l2_Id`, `l2`.`OneToMany_Optional_Inverse2Id` AS `l2_OneToMany_Optional_Inverse2Id`, `j1`.`l3_Id` FROM `LevelTwo` AS `l2` LEFT JOIN LATERAL ( SELECT `t1`.`l3_Id` FROM ( SELECT `l3`.`Id` AS `l3_Id` FROM `LevelThree` AS `l3` WHERE (`l2`.`Id` = `l3`.`OneToMany_Optional_Inverse3Id`) AND ((`l3`.`Name` <> 'Foo') OR `l3`.`Name` IS NULL) ORDER BY `l3`.`Id` LIMIT 1 ) AS `t1` LEFT JOIN `LevelFour` AS `l4a` ON `t1`.`l3_Id` = `l4a`.`OneToMany_Optional_Inverse4Id` LEFT JOIN `LevelFour` AS `l4b` ON `t1`.`l3_Id` = `l4b`.`OneToMany_Required_Inverse4Id` ) AS `j1` ON TRUE; The result is as follows, which is correct and expected: +-------+----------------------------------+-------+ | l2_Id | l2_OneToMany_Optional_Inverse2Id | l3_Id | +-------+----------------------------------+-------+ | 1 | NULL | 2 | | 3 | NULL | NULL | | 5 | NULL | NULL | | 7 | NULL | NULL | | 9 | NULL | NULL | | 11 | NULL | NULL | | 2 | 1 | 4 | | 4 | 1 | NULL | | 6 | 1 | NULL | | 8 | 1 | NULL | | 10 | 1 | NULL | +-------+----------------------------------+-------+ 11 rows in set (0.00 sec) We will now add one more outer query around the query we just ran. The resulting query structure will look like this: SELECT `l1`.`Id` AS `l1_Id`, `j2`.`l2_Id`, `j2`.`l2_OneToMany_Optional_Inverse2Id`, `j2`.`l3_Id` FROM `LevelOne` AS `l1` LEFT JOIN ( -- -- Our query we previously ran. -- ) AS `j2` ON `l1`.`Id` = `j2`.`l2_OneToMany_Optional_Inverse2Id` ORDER BY `l1`.`Id`, `j2`.`l2_Id`, `j2`.`l3_Id`; As can be seen, we are LEFT JOINing the `Id` column of the outer query on the `l2_OneToMany_Optional_Inverse2Id` column of the inner query. The table of the outer query has exactly 1 row with an `Id` of 1. Therefore, we expect the result to have 5 rows with a matching (NOT NULL) `l2_OneToMany_Optional_Inverse2Id` value, of which one row will have an `l3_Id` value of 4, while the 4 remaining matching rows will have an `l3_Id` value of NULL. However, that is not the case. Run the following full query: SELECT `l1`.`Id` AS `l1_Id`, `j2`.`l2_Id`, `j2`.`l2_OneToMany_Optional_Inverse2Id`, `j2`.`l3_Id` FROM `LevelOne` AS `l1` LEFT JOIN ( SELECT `l2`.`Id` AS `l2_Id`, `l2`.`OneToMany_Optional_Inverse2Id` AS `l2_OneToMany_Optional_Inverse2Id`, `j1`.`l3_Id` FROM `LevelTwo` AS `l2` LEFT JOIN LATERAL ( SELECT `t1`.`l3_Id` FROM ( SELECT `l3`.`Id` AS `l3_Id` FROM `LevelThree` AS `l3` WHERE (`l2`.`Id` = `l3`.`OneToMany_Optional_Inverse3Id`) AND ((`l3`.`Name` <> 'Foo') OR `l3`.`Name` IS NULL) ORDER BY `l3`.`Id` LIMIT 1 ) AS `t1` LEFT JOIN `LevelFour` AS `l4a` ON `t1`.`l3_Id` = `l4a`.`OneToMany_Optional_Inverse4Id` LEFT JOIN `LevelFour` AS `l4b` ON `t1`.`l3_Id` = `l4b`.`OneToMany_Required_Inverse4Id` ) AS `j1` ON TRUE ) AS `j2` ON `l1`.`Id` = `j2`.`l2_OneToMany_Optional_Inverse2Id` ORDER BY `l1`.`Id`, `j2`.`l2_Id`, `j2`.`l3_Id`; The result is an unexpected: +-------+-------+----------------------------------+-------+ | l1_Id | l2_Id | l2_OneToMany_Optional_Inverse2Id | l3_Id | +-------+-------+----------------------------------+-------+ | 1 | 2 | 1 | 4 | | 1 | 4 | 1 | 4 | | 1 | 6 | 1 | 4 | | 1 | 8 | 1 | 4 | | 1 | 10 | 1 | 4 | | 2 | NULL | NULL | NULL | | 3 | NULL | NULL | NULL | | 4 | NULL | NULL | NULL | | 5 | NULL | NULL | NULL | | 6 | NULL | NULL | NULL | | 7 | NULL | NULL | NULL | | 8 | NULL | NULL | NULL | | 9 | NULL | NULL | NULL | | 10 | NULL | NULL | NULL | | 11 | NULL | NULL | NULL | | 12 | NULL | NULL | NULL | | 13 | NULL | NULL | NULL | +-------+-------+----------------------------------+-------+ 17 rows in set (0.00 sec) It *should* have looked like this: +-------+-------+----------------------------------+-------+ | l1_Id | l2_Id | l2_OneToMany_Optional_Inverse2Id | l3_Id | +-------+-------+----------------------------------+-------+ | 1 | 2 | 1 | 4 | | 1 | 4 | 1 | NULL | | 1 | 6 | 1 | NULL | | 1 | 8 | 1 | NULL | | 1 | 10 | 1 | NULL | | 2 | NULL | NULL | NULL | | 3 | NULL | NULL | NULL | | 4 | NULL | NULL | NULL | | 5 | NULL | NULL | NULL | | 6 | NULL | NULL | NULL | | 7 | NULL | NULL | NULL | | 8 | NULL | NULL | NULL | | 9 | NULL | NULL | NULL | | 10 | NULL | NULL | NULL | | 11 | NULL | NULL | NULL | | 12 | NULL | NULL | NULL | | 13 | NULL | NULL | NULL | +-------+-------+----------------------------------+-------+ 17 rows in set (0.00 sec) How to repeat: Run the following SQL script: -- MySQL dump 10.13 Distrib 8.0.22, for Win64 (x86_64) -- -- Host: localhost Database: ComplexNavigations -- ------------------------------------------------------ -- Server version 8.0.22 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!50503 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `LevelFour` -- DROP TABLE IF EXISTS `LevelFour`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `LevelFour` ( `Id` int NOT NULL, `Name` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci, `Level3_Required_Id` int NOT NULL, `Level3_Optional_Id` int DEFAULT NULL, `OneToOne_Optional_PK_Inverse4Id` int DEFAULT NULL, `OneToMany_Required_Inverse4Id` int NOT NULL, `OneToMany_Optional_Inverse4Id` int DEFAULT NULL, `OneToOne_Optional_Self4Id` int DEFAULT NULL, `OneToMany_Required_Self_Inverse4Id` int NOT NULL, `OneToMany_Optional_Self_Inverse4Id` int DEFAULT NULL, PRIMARY KEY (`Id`), UNIQUE KEY `IX_LevelFour_Level3_Required_Id` (`Level3_Required_Id`), UNIQUE KEY `IX_LevelFour_Level3_Optional_Id` (`Level3_Optional_Id`), UNIQUE KEY `IX_LevelFour_OneToOne_Optional_PK_Inverse4Id` (`OneToOne_Optional_PK_Inverse4Id`), UNIQUE KEY `IX_LevelFour_OneToOne_Optional_Self4Id` (`OneToOne_Optional_Self4Id`), KEY `IX_LevelFour_OneToMany_Optional_Inverse4Id` (`OneToMany_Optional_Inverse4Id`), KEY `IX_LevelFour_OneToMany_Optional_Self_Inverse4Id` (`OneToMany_Optional_Self_Inverse4Id`), KEY `IX_LevelFour_OneToMany_Required_Inverse4Id` (`OneToMany_Required_Inverse4Id`), KEY `IX_LevelFour_OneToMany_Required_Self_Inverse4Id` (`OneToMany_Required_Self_Inverse4Id`), CONSTRAINT `FK_LevelFour_LevelFour_OneToMany_Optional_Self_Inverse4Id` FOREIGN KEY (`OneToMany_Optional_Self_Inverse4Id`) REFERENCES `LevelFour` (`Id`) ON DELETE RESTRICT, CONSTRAINT `FK_LevelFour_LevelFour_OneToMany_Required_Self_Inverse4Id` FOREIGN KEY (`OneToMany_Required_Self_Inverse4Id`) REFERENCES `LevelFour` (`Id`) ON DELETE RESTRICT, CONSTRAINT `FK_LevelFour_LevelFour_OneToOne_Optional_Self4Id` FOREIGN KEY (`OneToOne_Optional_Self4Id`) REFERENCES `LevelFour` (`Id`) ON DELETE RESTRICT, CONSTRAINT `FK_LevelFour_LevelThree_Id` FOREIGN KEY (`Id`) REFERENCES `LevelThree` (`Id`) ON DELETE RESTRICT, CONSTRAINT `FK_LevelFour_LevelThree_Level3_Optional_Id` FOREIGN KEY (`Level3_Optional_Id`) REFERENCES `LevelThree` (`Id`) ON DELETE RESTRICT, CONSTRAINT `FK_LevelFour_LevelThree_Level3_Required_Id` FOREIGN KEY (`Level3_Required_Id`) REFERENCES `LevelThree` (`Id`) ON DELETE RESTRICT, CONSTRAINT `FK_LevelFour_LevelThree_OneToMany_Optional_Inverse4Id` FOREIGN KEY (`OneToMany_Optional_Inverse4Id`) REFERENCES `LevelThree` (`Id`) ON DELETE RESTRICT, CONSTRAINT `FK_LevelFour_LevelThree_OneToMany_Required_Inverse4Id` FOREIGN KEY (`OneToMany_Required_Inverse4Id`) REFERENCES `LevelThree` (`Id`) ON DELETE RESTRICT, CONSTRAINT `FK_LevelFour_LevelThree_OneToOne_Optional_PK_Inverse4Id` FOREIGN KEY (`OneToOne_Optional_PK_Inverse4Id`) REFERENCES `LevelThree` (`Id`) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `LevelFour` -- LOCK TABLES `LevelFour` WRITE; /*!40000 ALTER TABLE `LevelFour` DISABLE KEYS */; INSERT INTO `LevelFour` (`Id`, `Name`, `Level3_Required_Id`, `Level3_Optional_Id`, `OneToOne_Optional_PK_Inverse4Id`, `OneToMany_Required_Inverse4Id`, `OneToMany_Optional_Inverse4Id`, `OneToOne_Optional_Self4Id`, `OneToMany_Required_Self_Inverse4Id`, `OneToMany_Optional_Self_Inverse4Id`) VALUES (1,'L4 01',10,10,1,1,NULL,NULL,1,2),(2,'L4 02',9,NULL,NULL,1,1,NULL,1,NULL),(3,'L4 03',8,8,3,1,NULL,NULL,2,4),(4,'L4 04',7,NULL,NULL,1,1,NULL,3,NULL),(5,'L4 05',6,6,5,1,NULL,NULL,4,6),(6,'L4 06',5,NULL,NULL,1,1,NULL,5,NULL),(7,'L4 07',4,4,7,1,NULL,NULL,6,8),(8,'L4 08',3,NULL,NULL,1,1,NULL,7,NULL),(9,'L4 09',2,2,9,1,NULL,NULL,8,10),(10,'L4 10',1,NULL,NULL,1,1,NULL,9,NULL); /*!40000 ALTER TABLE `LevelFour` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `LevelOne` -- DROP TABLE IF EXISTS `LevelOne`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `LevelOne` ( `Id` int NOT NULL, `Name` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci, `Date` datetime(6) NOT NULL, `OneToOne_Optional_Self1Id` int DEFAULT NULL, `OneToMany_Required_Self_Inverse1Id` int NOT NULL, `OneToMany_Optional_Self_Inverse1Id` int DEFAULT NULL, PRIMARY KEY (`Id`), UNIQUE KEY `IX_LevelOne_OneToOne_Optional_Self1Id` (`OneToOne_Optional_Self1Id`), KEY `IX_LevelOne_OneToMany_Optional_Self_Inverse1Id` (`OneToMany_Optional_Self_Inverse1Id`), KEY `IX_LevelOne_OneToMany_Required_Self_Inverse1Id` (`OneToMany_Required_Self_Inverse1Id`), CONSTRAINT `FK_LevelOne_LevelOne_OneToMany_Optional_Self_Inverse1Id` FOREIGN KEY (`OneToMany_Optional_Self_Inverse1Id`) REFERENCES `LevelOne` (`Id`) ON DELETE RESTRICT, CONSTRAINT `FK_LevelOne_LevelOne_OneToMany_Required_Self_Inverse1Id` FOREIGN KEY (`OneToMany_Required_Self_Inverse1Id`) REFERENCES `LevelOne` (`Id`) ON DELETE RESTRICT, CONSTRAINT `FK_LevelOne_LevelOne_OneToOne_Optional_Self1Id` FOREIGN KEY (`OneToOne_Optional_Self1Id`) REFERENCES `LevelOne` (`Id`) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `LevelOne` -- LOCK TABLES `LevelOne` WRITE; /*!40000 ALTER TABLE `LevelOne` DISABLE KEYS */; INSERT INTO `LevelOne` (`Id`, `Name`, `Date`, `OneToOne_Optional_Self1Id`, `OneToMany_Required_Self_Inverse1Id`, `OneToMany_Optional_Self_Inverse1Id`) VALUES (1,'L1 01','2001-01-01 00:00:00.000000',10,1,2),(2,'L1 02','2002-02-02 00:00:00.000000',9,1,NULL),(3,'L1 03','2003-03-03 00:00:00.000000',8,2,4),(4,'L1 04','2004-04-04 00:00:00.000000',7,3,NULL),(5,'L1 05','2005-05-05 00:00:00.000000',6,4,6),(6,'L1 06','2006-06-06 00:00:00.000000',NULL,5,NULL),(7,'L1 07','2007-07-07 00:00:00.000000',NULL,6,8),(8,'L1 08','2008-08-08 00:00:00.000000',NULL,7,NULL),(9,'L1 09','2009-09-09 00:00:00.000000',NULL,8,10),(10,'L1 10','2010-10-10 00:00:00.000000',NULL,9,NULL),(11,'L1 11','2009-11-11 00:00:00.000000',NULL,11,NULL),(12,'L1 12','2008-12-12 00:00:00.000000',NULL,1,NULL),(13,'L1 13','2007-01-01 00:00:00.000000',NULL,2,NULL); /*!40000 ALTER TABLE `LevelOne` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `LevelThree` -- DROP TABLE IF EXISTS `LevelThree`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `LevelThree` ( `Id` int NOT NULL, `Name` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci, `Level2_Required_Id` int NOT NULL, `Level2_Optional_Id` int DEFAULT NULL, `OneToOne_Optional_PK_Inverse3Id` int DEFAULT NULL, `OneToMany_Required_Inverse3Id` int NOT NULL, `OneToMany_Optional_Inverse3Id` int DEFAULT NULL, `OneToOne_Optional_Self3Id` int DEFAULT NULL, `OneToMany_Required_Self_Inverse3Id` int NOT NULL, `OneToMany_Optional_Self_Inverse3Id` int DEFAULT NULL, PRIMARY KEY (`Id`), UNIQUE KEY `IX_LevelThree_Level2_Required_Id` (`Level2_Required_Id`), UNIQUE KEY `IX_LevelThree_Level2_Optional_Id` (`Level2_Optional_Id`), UNIQUE KEY `IX_LevelThree_OneToOne_Optional_PK_Inverse3Id` (`OneToOne_Optional_PK_Inverse3Id`), UNIQUE KEY `IX_LevelThree_OneToOne_Optional_Self3Id` (`OneToOne_Optional_Self3Id`), KEY `IX_LevelThree_OneToMany_Optional_Inverse3Id` (`OneToMany_Optional_Inverse3Id`), KEY `IX_LevelThree_OneToMany_Optional_Self_Inverse3Id` (`OneToMany_Optional_Self_Inverse3Id`), KEY `IX_LevelThree_OneToMany_Required_Inverse3Id` (`OneToMany_Required_Inverse3Id`), KEY `IX_LevelThree_OneToMany_Required_Self_Inverse3Id` (`OneToMany_Required_Self_Inverse3Id`), CONSTRAINT `FK_LevelThree_LevelThree_OneToMany_Optional_Self_Inverse3Id` FOREIGN KEY (`OneToMany_Optional_Self_Inverse3Id`) REFERENCES `LevelThree` (`Id`) ON DELETE RESTRICT, CONSTRAINT `FK_LevelThree_LevelThree_OneToMany_Required_Self_Inverse3Id` FOREIGN KEY (`OneToMany_Required_Self_Inverse3Id`) REFERENCES `LevelThree` (`Id`) ON DELETE RESTRICT, CONSTRAINT `FK_LevelThree_LevelThree_OneToOne_Optional_Self3Id` FOREIGN KEY (`OneToOne_Optional_Self3Id`) REFERENCES `LevelThree` (`Id`) ON DELETE RESTRICT, CONSTRAINT `FK_LevelThree_LevelTwo_Id` FOREIGN KEY (`Id`) REFERENCES `LevelTwo` (`Id`) ON DELETE RESTRICT, CONSTRAINT `FK_LevelThree_LevelTwo_Level2_Optional_Id` FOREIGN KEY (`Level2_Optional_Id`) REFERENCES `LevelTwo` (`Id`) ON DELETE RESTRICT, CONSTRAINT `FK_LevelThree_LevelTwo_Level2_Required_Id` FOREIGN KEY (`Level2_Required_Id`) REFERENCES `LevelTwo` (`Id`) ON DELETE RESTRICT, CONSTRAINT `FK_LevelThree_LevelTwo_OneToMany_Optional_Inverse3Id` FOREIGN KEY (`OneToMany_Optional_Inverse3Id`) REFERENCES `LevelTwo` (`Id`) ON DELETE RESTRICT, CONSTRAINT `FK_LevelThree_LevelTwo_OneToMany_Required_Inverse3Id` FOREIGN KEY (`OneToMany_Required_Inverse3Id`) REFERENCES `LevelTwo` (`Id`) ON DELETE RESTRICT, CONSTRAINT `FK_LevelThree_LevelTwo_OneToOne_Optional_PK_Inverse3Id` FOREIGN KEY (`OneToOne_Optional_PK_Inverse3Id`) REFERENCES `LevelTwo` (`Id`) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `LevelThree` -- LOCK TABLES `LevelThree` WRITE; /*!40000 ALTER TABLE `LevelThree` DISABLE KEYS */; INSERT INTO `LevelThree` (`Id`, `Name`, `Level2_Required_Id`, `Level2_Optional_Id`, `OneToOne_Optional_PK_Inverse3Id`, `OneToMany_Required_Inverse3Id`, `OneToMany_Optional_Inverse3Id`, `OneToOne_Optional_Self3Id`, `OneToMany_Required_Self_Inverse3Id`, `OneToMany_Optional_Self_Inverse3Id`) VALUES (1,'L3 01',10,9,1,1,NULL,10,1,2),(2,'L3 02',9,NULL,NULL,1,1,9,1,NULL),(3,'L3 03',8,7,3,1,NULL,8,2,4),(4,'L3 04',7,NULL,NULL,1,2,7,3,NULL),(5,'L3 05',6,5,6,1,NULL,6,4,6),(6,'L3 06',5,NULL,NULL,1,1,NULL,5,NULL),(7,'L3 07',4,4,8,1,NULL,NULL,6,8),(8,'L3 08',3,NULL,NULL,1,2,NULL,7,NULL),(9,'L3 09',2,2,10,1,NULL,NULL,8,10),(10,'L3 10',1,NULL,NULL,1,1,NULL,9,NULL); /*!40000 ALTER TABLE `LevelThree` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `LevelTwo` -- DROP TABLE IF EXISTS `LevelTwo`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `LevelTwo` ( `Id` int NOT NULL, `Name` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci, `Date` datetime(6) NOT NULL, `Level1_Required_Id` int NOT NULL, `Level1_Optional_Id` int DEFAULT NULL, `OneToOne_Optional_PK_Inverse2Id` int DEFAULT NULL, `OneToMany_Required_Inverse2Id` int NOT NULL, `OneToMany_Optional_Inverse2Id` int DEFAULT NULL, `OneToOne_Optional_Self2Id` int DEFAULT NULL, `OneToMany_Required_Self_Inverse2Id` int NOT NULL, `OneToMany_Optional_Self_Inverse2Id` int DEFAULT NULL, PRIMARY KEY (`Id`), UNIQUE KEY `IX_LevelTwo_Level1_Required_Id` (`Level1_Required_Id`), UNIQUE KEY `IX_LevelTwo_Level1_Optional_Id` (`Level1_Optional_Id`), UNIQUE KEY `IX_LevelTwo_OneToOne_Optional_PK_Inverse2Id` (`OneToOne_Optional_PK_Inverse2Id`), UNIQUE KEY `IX_LevelTwo_OneToOne_Optional_Self2Id` (`OneToOne_Optional_Self2Id`), KEY `IX_LevelTwo_OneToMany_Optional_Inverse2Id` (`OneToMany_Optional_Inverse2Id`), KEY `IX_LevelTwo_OneToMany_Optional_Self_Inverse2Id` (`OneToMany_Optional_Self_Inverse2Id`), KEY `IX_LevelTwo_OneToMany_Required_Inverse2Id` (`OneToMany_Required_Inverse2Id`), KEY `IX_LevelTwo_OneToMany_Required_Self_Inverse2Id` (`OneToMany_Required_Self_Inverse2Id`), CONSTRAINT `FK_LevelTwo_LevelOne_Id` FOREIGN KEY (`Id`) REFERENCES `LevelOne` (`Id`) ON DELETE RESTRICT, CONSTRAINT `FK_LevelTwo_LevelOne_Level1_Optional_Id` FOREIGN KEY (`Level1_Optional_Id`) REFERENCES `LevelOne` (`Id`) ON DELETE RESTRICT, CONSTRAINT `FK_LevelTwo_LevelOne_Level1_Required_Id` FOREIGN KEY (`Level1_Required_Id`) REFERENCES `LevelOne` (`Id`) ON DELETE RESTRICT, CONSTRAINT `FK_LevelTwo_LevelOne_OneToMany_Optional_Inverse2Id` FOREIGN KEY (`OneToMany_Optional_Inverse2Id`) REFERENCES `LevelOne` (`Id`) ON DELETE RESTRICT, CONSTRAINT `FK_LevelTwo_LevelOne_OneToMany_Required_Inverse2Id` FOREIGN KEY (`OneToMany_Required_Inverse2Id`) REFERENCES `LevelOne` (`Id`) ON DELETE RESTRICT, CONSTRAINT `FK_LevelTwo_LevelOne_OneToOne_Optional_PK_Inverse2Id` FOREIGN KEY (`OneToOne_Optional_PK_Inverse2Id`) REFERENCES `LevelOne` (`Id`) ON DELETE RESTRICT, CONSTRAINT `FK_LevelTwo_LevelTwo_OneToMany_Optional_Self_Inverse2Id` FOREIGN KEY (`OneToMany_Optional_Self_Inverse2Id`) REFERENCES `LevelTwo` (`Id`) ON DELETE RESTRICT, CONSTRAINT `FK_LevelTwo_LevelTwo_OneToMany_Required_Self_Inverse2Id` FOREIGN KEY (`OneToMany_Required_Self_Inverse2Id`) REFERENCES `LevelTwo` (`Id`) ON DELETE RESTRICT, CONSTRAINT `FK_LevelTwo_LevelTwo_OneToOne_Optional_Self2Id` FOREIGN KEY (`OneToOne_Optional_Self2Id`) REFERENCES `LevelTwo` (`Id`) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `LevelTwo` -- LOCK TABLES `LevelTwo` WRITE; /*!40000 ALTER TABLE `LevelTwo` DISABLE KEYS */; INSERT INTO `LevelTwo` (`Id`, `Name`, `Date`, `Level1_Required_Id`, `Level1_Optional_Id`, `OneToOne_Optional_PK_Inverse2Id`, `OneToMany_Required_Inverse2Id`, `OneToMany_Optional_Inverse2Id`, `OneToOne_Optional_Self2Id`, `OneToMany_Required_Self_Inverse2Id`, `OneToMany_Optional_Self_Inverse2Id`) VALUES (1,'L2 01','2010-10-10 00:00:00.000000',10,10,1,1,NULL,10,1,2),(2,'L2 02','2002-02-02 00:00:00.000000',9,NULL,NULL,1,1,9,1,NULL),(3,'L2 03','2008-08-08 00:00:00.000000',8,8,3,1,NULL,8,2,4),(4,'L2 04','2004-04-04 00:00:00.000000',7,NULL,NULL,1,1,7,3,NULL),(5,'L2 05','2006-06-06 00:00:00.000000',6,6,5,1,NULL,6,4,6),(6,'L2 06','2005-05-05 00:00:00.000000',5,NULL,NULL,1,1,NULL,5,NULL),(7,'L2 07','2007-07-07 00:00:00.000000',4,4,7,1,NULL,NULL,6,8),(8,'L2 08','2003-03-03 00:00:00.000000',3,NULL,NULL,1,1,NULL,7,NULL),(9,'L2 09','2009-09-09 00:00:00.000000',2,2,9,1,NULL,NULL,8,10),(10,'L2 10','2001-01-01 00:00:00.000000',1,NULL,NULL,1,1,NULL,9,NULL),(11,'L2 11','2000-01-01 00:00:00.000000',11,NULL,NULL,1,NULL,NULL,1,NULL); /*!40000 ALTER TABLE `LevelTwo` ENABLE KEYS */; UNLOCK TABLES; -- -- Dumping events for database 'ComplexNavigations' -- /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2020-10-22 16:39:41 -- -- Queries: -- SELECT `l2`.`Id` AS `l2_Id`, `l2`.`OneToMany_Optional_Inverse2Id` AS `l2_OneToMany_Optional_Inverse2Id`, `j1`.`l3_Id` FROM `LevelTwo` AS `l2` LEFT JOIN LATERAL ( SELECT `t1`.`l3_Id` FROM ( SELECT `l3`.`Id` AS `l3_Id` FROM `LevelThree` AS `l3` WHERE (`l2`.`Id` = `l3`.`OneToMany_Optional_Inverse3Id`) AND ((`l3`.`Name` <> 'Foo') OR `l3`.`Name` IS NULL) ORDER BY `l3`.`Id` LIMIT 1 ) AS `t1` LEFT JOIN `LevelFour` AS `l4a` ON `t1`.`l3_Id` = `l4a`.`OneToMany_Optional_Inverse4Id` LEFT JOIN `LevelFour` AS `l4b` ON `t1`.`l3_Id` = `l4b`.`OneToMany_Required_Inverse4Id` ) AS `j1` ON TRUE; SELECT `l1`.`Id` AS `l1_Id`, `j2`.`l2_Id`, `j2`.`l2_OneToMany_Optional_Inverse2Id`, `j2`.`l3_Id` FROM `LevelOne` AS `l1` LEFT JOIN ( SELECT `l2`.`Id` AS `l2_Id`, `l2`.`OneToMany_Optional_Inverse2Id` AS `l2_OneToMany_Optional_Inverse2Id`, `j1`.`l3_Id` FROM `LevelTwo` AS `l2` LEFT JOIN LATERAL ( SELECT `t1`.`l3_Id` FROM ( SELECT `l3`.`Id` AS `l3_Id` FROM `LevelThree` AS `l3` WHERE (`l2`.`Id` = `l3`.`OneToMany_Optional_Inverse3Id`) AND ((`l3`.`Name` <> 'Foo') OR `l3`.`Name` IS NULL) ORDER BY `l3`.`Id` LIMIT 1 ) AS `t1` LEFT JOIN `LevelFour` AS `l4a` ON `t1`.`l3_Id` = `l4a`.`OneToMany_Optional_Inverse4Id` LEFT JOIN `LevelFour` AS `l4b` ON `t1`.`l3_Id` = `l4b`.`OneToMany_Required_Inverse4Id` ) AS `j1` ON TRUE ) AS `j2` ON `l1`.`Id` = `j2`.`l2_OneToMany_Optional_Inverse2Id` ORDER BY `l1`.`Id`, `j2`.`l2_Id`, `j2`.`l3_Id`; Suggested fix: When using a LEFT JOIN in an outer query, that is applied over the result of an inner query that uses a LEFT JOIN LATERAL, every row of the outer query needs to be combined with every matching row of the inner query (or use NULL values).