Bug #101308 CROSS JOIN LATERAL and semantically equal CROSS JOIN WHERE have different result
Submitted: 26 Oct 2020 0:35 Modified: 26 Oct 2020 8:46
Reporter: Laurents Meyer (OCA) Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:8.0.21 OS:Any
Assigned to: CPU Architecture:Any
Tags: LATERAL

[26 Oct 2020 0:35] Laurents Meyer 
Description:
Run the script from the "How to repeat" section.

Then run the following query, that works as expected:

SELECT `t`.`Nickname`, `t`.`SquadId`, `t`.`AssignedCityName`, `t`.`HasSoulPatch`, `t`.`LeaderNickname`, `t`.`LeaderSquadId`, `t`.`Rank`
FROM `LocustLeaders` AS `l`
CROSS JOIN (
    SELECT `g`.`Nickname`, `g`.`SquadId`, `g`.`AssignedCityName`, `g`.`HasSoulPatch`, `g`.`LeaderNickname`, `g`.`LeaderSquadId`, `g`.`Rank`
    FROM `Gears` AS `g`
) AS `t`
ORDER BY `t`.`Nickname`, `t`.`SquadId`, `t`.`AssignedCityName`, `t`.`HasSoulPatch`, `t`.`LeaderNickname`, `t`.`LeaderSquadId`, `t`.`Rank`;

The following result is correct:

+------------+---------+------------------+--------------+----------------+---------------+------+
| Nickname   | SquadId | AssignedCityName | HasSoulPatch | LeaderNickname | LeaderSquadId | Rank |
+------------+---------+------------------+--------------+----------------+---------------+------+
| Baird      |       1 | Jacinto          |            1 | Marcus         |             1 |    2 |
| Baird      |       1 | Jacinto          |            1 | Marcus         |             1 |    2 |
| Baird      |       1 | Jacinto          |            1 | Marcus         |             1 |    2 |
| Baird      |       1 | Jacinto          |            1 | Marcus         |             1 |    2 |
| Baird      |       1 | Jacinto          |            1 | Marcus         |             1 |    2 |
| Baird      |       1 | Jacinto          |            1 | Marcus         |             1 |    2 |
| Cole Train |       1 | Jacinto          |            0 | Marcus         |             1 |    1 |
| Cole Train |       1 | Jacinto          |            0 | Marcus         |             1 |    1 |
| Cole Train |       1 | Jacinto          |            0 | Marcus         |             1 |    1 |
| Cole Train |       1 | Jacinto          |            0 | Marcus         |             1 |    1 |
| Cole Train |       1 | Jacinto          |            0 | Marcus         |             1 |    1 |
| Cole Train |       1 | Jacinto          |            0 | Marcus         |             1 |    1 |
| Dom        |       1 | Ephyra           |            0 | Marcus         |             1 |    2 |
| Dom        |       1 | Ephyra           |            0 | Marcus         |             1 |    2 |
| Dom        |       1 | Ephyra           |            0 | Marcus         |             1 |    2 |
| Dom        |       1 | Ephyra           |            0 | Marcus         |             1 |    2 |
| Dom        |       1 | Ephyra           |            0 | Marcus         |             1 |    2 |
| Dom        |       1 | Ephyra           |            0 | Marcus         |             1 |    2 |
| Marcus     |       1 | NULL             |            1 | NULL           |             0 |    4 |
| Marcus     |       1 | NULL             |            1 | NULL           |             0 |    4 |
| Marcus     |       1 | NULL             |            1 | NULL           |             0 |    4 |
| Marcus     |       1 | NULL             |            1 | NULL           |             0 |    4 |
| Marcus     |       1 | NULL             |            1 | NULL           |             0 |    4 |
| Marcus     |       1 | NULL             |            1 | NULL           |             0 |    4 |
| Paduk      |       2 | Unknown          |            0 | Baird          |             1 |    1 |
| Paduk      |       2 | Unknown          |            0 | Baird          |             1 |    1 |
| Paduk      |       2 | Unknown          |            0 | Baird          |             1 |    1 |
| Paduk      |       2 | Unknown          |            0 | Baird          |             1 |    1 |
| Paduk      |       2 | Unknown          |            0 | Baird          |             1 |    1 |
| Paduk      |       2 | Unknown          |            0 | Baird          |             1 |    1 |
+------------+---------+------------------+--------------+----------------+---------------+------+
30 rows in set (0.00 sec)

Now run the following query, that is semantically equal to the previous query:

SELECT `t`.`Nickname`, `t`.`SquadId`, `t`.`AssignedCityName`, `t`.`HasSoulPatch`, `t`.`LeaderNickname`, `t`.`LeaderSquadId`, `t`.`Rank`
FROM `LocustLeaders` AS `l`
CROSS JOIN LATERAL (
    SELECT `g`.`Nickname`, `g`.`SquadId`, `g`.`AssignedCityName`, `g`.`HasSoulPatch`, `g`.`LeaderNickname`, `g`.`LeaderSquadId`, `g`.`Rank`
    FROM `Gears` AS `g`
    WHERE EXISTS (
        SELECT 1
        FROM `LocustLeaders` AS `l0`
        WHERE (`l0`.`ThreatLevelNullableByte` = `l`.`ThreatLevelNullableByte`) OR (`l0`.`ThreatLevelNullableByte` IS NULL AND `l`.`ThreatLevelNullableByte` IS NULL))
) AS `t`
ORDER BY `t`.`Nickname`, `t`.`SquadId`, `t`.`AssignedCityName`, `t`.`HasSoulPatch`, `t`.`LeaderNickname`, `t`.`LeaderSquadId`, `t`.`Rank`;

Because both queries are semantically equal, they should both return the same results set. However, this second query leads to a completely different result, which is incorrect and unexpected:

+------------+---------+------------------+--------------+----------------+---------------+------+
| Nickname   | SquadId | AssignedCityName | HasSoulPatch | LeaderNickname | LeaderSquadId | Rank |
+------------+---------+------------------+--------------+----------------+---------------+------+
| Baird      |       1 | Jacinto          |            1 | Marcus         |             1 |    2 |
| Baird      |       1 | Jacinto          |            1 | Marcus         |             1 |    2 |
| Cole Train |       1 | Jacinto          |            0 | Marcus         |             1 |    1 |
| Cole Train |       1 | Jacinto          |            0 | Marcus         |             1 |    1 |
| Dom        |       1 | Ephyra           |            0 | Marcus         |             1 |    2 |
| Dom        |       1 | Ephyra           |            0 | Marcus         |             1 |    2 |
| Marcus     |       1 | NULL             |            1 | NULL           |             0 |    4 |
| Marcus     |       1 | NULL             |            1 | NULL           |             0 |    4 |
| Paduk      |       2 | Unknown          |            0 | Baird          |             1 |    1 |
| Paduk      |       2 | Unknown          |            0 | Baird          |             1 |    1 |
+------------+---------+------------------+--------------+----------------+---------------+------+
10 rows in set (0.00 sec)

How to repeat:
Run the following script:

-- MySQL dump 10.13  Distrib 8.0.22, for Win64 (x86_64)
--
-- Host: localhost    Database: GearsOfWarQueryTest
-- ------------------------------------------------------
-- Server version	8.0.21

/*!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 `Gears`
--

DROP TABLE IF EXISTS `Gears`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `Gears` (
  `Nickname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `SquadId` int NOT NULL,
  `FullName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `CityOfBirthName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `AssignedCityName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `Rank` int NOT NULL,
  `LeaderNickname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `LeaderSquadId` int NOT NULL,
  `HasSoulPatch` tinyint(1) NOT NULL,
  `Discriminator` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`Nickname`,`SquadId`),
  UNIQUE KEY `AK_Gears_FullName` (`FullName`),
  KEY `IX_Gears_AssignedCityName` (`AssignedCityName`),
  KEY `IX_Gears_CityOfBirthName` (`CityOfBirthName`),
  KEY `IX_Gears_LeaderNickname_LeaderSquadId` (`LeaderNickname`,`LeaderSquadId`),
  KEY `IX_Gears_SquadId` (`SquadId`),
  CONSTRAINT `FK_Gears_Cities_AssignedCityName` FOREIGN KEY (`AssignedCityName`) REFERENCES `Cities` (`Name`) ON DELETE RESTRICT,
  CONSTRAINT `FK_Gears_Cities_CityOfBirthName` FOREIGN KEY (`CityOfBirthName`) REFERENCES `Cities` (`Name`) ON DELETE CASCADE,
  CONSTRAINT `FK_Gears_Gears_LeaderNickname_LeaderSquadId` FOREIGN KEY (`LeaderNickname`, `LeaderSquadId`) REFERENCES `Gears` (`Nickname`, `SquadId`) ON DELETE RESTRICT,
  CONSTRAINT `FK_Gears_Squads_SquadId` FOREIGN KEY (`SquadId`) REFERENCES `Squads` (`Id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `Gears`
--

LOCK TABLES `Gears` WRITE;
/*!40000 ALTER TABLE `Gears` DISABLE KEYS */;
INSERT INTO `Gears` (`Nickname`, `SquadId`, `FullName`, `CityOfBirthName`, `AssignedCityName`, `Rank`, `LeaderNickname`, `LeaderSquadId`, `HasSoulPatch`, `Discriminator`) VALUES ('Baird',1,'Damon Baird','Unknown','Jacinto',2,'Marcus',1,1,'Officer'),('Cole Train',1,'Augustus Cole','Hanover','Jacinto',1,'Marcus',1,0,'Gear'),('Dom',1,'Dominic Santiago','Ephyra','Ephyra',2,'Marcus',1,0,'Gear'),('Marcus',1,'Marcus Fenix','Jacinto',NULL,4,NULL,0,1,'Officer'),('Paduk',2,'Garron Paduk','Unknown','Unknown',1,'Baird',1,0,'Gear');
/*!40000 ALTER TABLE `Gears` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `LocustLeaders`
--

DROP TABLE IF EXISTS `LocustLeaders`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `LocustLeaders` (
  `Name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `ThreatLevel` smallint NOT NULL,
  `ThreatLevelByte` tinyint unsigned NOT NULL,
  `ThreatLevelNullableByte` tinyint unsigned DEFAULT NULL,
  `Discriminator` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `LocustHordeId` int DEFAULT NULL,
  `DefeatedByNickname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `DefeatedBySquadId` int DEFAULT NULL,
  `HighCommandId` int DEFAULT NULL,
  PRIMARY KEY (`Name`),
  UNIQUE KEY `IX_LocustLeaders_DefeatedByNickname_DefeatedBySquadId` (`DefeatedByNickname`,`DefeatedBySquadId`),
  KEY `IX_LocustLeaders_HighCommandId` (`HighCommandId`),
  KEY `IX_LocustLeaders_LocustHordeId` (`LocustHordeId`),
  CONSTRAINT `FK_LocustLeaders_Factions_LocustHordeId` FOREIGN KEY (`LocustHordeId`) REFERENCES `Factions` (`Id`) ON DELETE RESTRICT,
  CONSTRAINT `FK_LocustLeaders_Gears_DefeatedByNickname_DefeatedBySquadId` FOREIGN KEY (`DefeatedByNickname`, `DefeatedBySquadId`) REFERENCES `Gears` (`Nickname`, `SquadId`) ON DELETE RESTRICT,
  CONSTRAINT `FK_LocustLeaders_LocustHighCommands_HighCommandId` FOREIGN KEY (`HighCommandId`) REFERENCES `LocustHighCommands` (`Id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `LocustLeaders`
--

LOCK TABLES `LocustLeaders` WRITE;
/*!40000 ALTER TABLE `LocustLeaders` DISABLE KEYS */;
INSERT INTO `LocustLeaders` (`Name`, `ThreatLevel`, `ThreatLevelByte`, `ThreatLevelNullableByte`, `Discriminator`, `LocustHordeId`, `DefeatedByNickname`, `DefeatedBySquadId`, `HighCommandId`) VALUES ('General Karn',3,3,3,'LocustLeader',1,NULL,NULL,NULL),('General RAAM',4,4,4,'LocustLeader',1,NULL,NULL,NULL),('High Priest Skorge',1,1,1,'LocustLeader',1,NULL,NULL,NULL),('Queen Myrrah',5,5,5,'LocustCommander',1,'Marcus',1,1),('The Speaker',3,3,3,'LocustLeader',2,NULL,NULL,NULL),('Unknown',0,0,NULL,'LocustCommander',2,NULL,NULL,1);
/*!40000 ALTER TABLE `LocustLeaders` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping events for database 'GearsOfWarQueryTest'
--

--
-- Dumping routines for database 'GearsOfWarQueryTest'
--
/*!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 */;

Suggested fix:
Make sure semantically equal queries, like the ones posted, return the same results.
[26 Oct 2020 5:49] MySQL Verification Team 
Hello Laurents,

Thank you for the report and test case.
Imho this issue exists in 8.0.21 but is no longer reproducible with 8.0.22 build. At the moment I'm unable t locate the commit which fixed this issue in 8.0.22. Could you please try at your end and confirm? Thank you!

- 8.0.22
mysql> SELECT `t`.`Nickname`, `t`.`SquadId`, `t`.`AssignedCityName`, `t`.`HasSoulPatch`, `t`.`LeaderNickname`, `t`.`LeaderSquadId`, `t`.`Rank`
    -> FROM `LocustLeaders` AS `l`
    -> CROSS JOIN (
    ->     SELECT `g`.`Nickname`, `g`.`SquadId`, `g`.`AssignedCityName`, `g`.`HasSoulPatch`, `g`.`LeaderNickname`, `g`.`LeaderSquadId`, `g`.`Rank`
    ->     FROM `Gears` AS `g`
    -> ) AS `t`
    -> ORDER BY `t`.`Nickname`, `t`.`SquadId`, `t`.`AssignedCityName`, `t`.`HasSoulPatch`, `t`.`LeaderNickname`, `t`.`LeaderSquadId`, `t`.`Rank`;
+------------+---------+------------------+--------------+----------------+---------------+------+
| Nickname   | SquadId | AssignedCityName | HasSoulPatch | LeaderNickname | LeaderSquadId | Rank |
+------------+---------+------------------+--------------+----------------+---------------+------+
| Baird      |       1 | Jacinto          |            1 | Marcus         |             1 |    2 |
.
.
| Paduk      |       2 | Unknown          |            0 | Baird          |             1 |    1 |
| Paduk      |       2 | Unknown          |            0 | Baird          |             1 |    1 |
+------------+---------+------------------+--------------+----------------+---------------+------+
30 rows in set (0.00 sec)

mysql> SELECT `t`.`Nickname`, `t`.`SquadId`, `t`.`AssignedCityName`, `t`.`HasSoulPatch`, `t`.`LeaderNickname`, `t`.`LeaderSquadId`, `t`.`Rank`
    -> FROM `LocustLeaders` AS `l`
    -> CROSS JOIN LATERAL (
    ->     SELECT `g`.`Nickname`, `g`.`SquadId`, `g`.`AssignedCityName`, `g`.`HasSoulPatch`, `g`.`LeaderNickname`, `g`.`LeaderSquadId`, `g`.`Rank`
    ->     FROM `Gears` AS `g`
    ->     WHERE EXISTS (
    ->         SELECT 1
    ->         FROM `LocustLeaders` AS `l0`
    ->         WHERE (`l0`.`ThreatLevelNullableByte` = `l`.`ThreatLevelNullableByte`) OR (`l0`.`ThreatLevelNullableByte` IS NULL AND `l`.`ThreatLevelNullableByte` IS NULL))
    -> ) AS `t`
    -> ORDER BY `t`.`Nickname`, `t`.`SquadId`, `t`.`AssignedCityName`, `t`.`HasSoulPatch`, `t`.`LeaderNickname`, `t`.`LeaderSquadId`, `t`.`Rank`;
+------------+---------+------------------+--------------+----------------+---------------+------+
| Nickname   | SquadId | AssignedCityName | HasSoulPatch | LeaderNickname | LeaderSquadId | Rank |
+------------+---------+------------------+--------------+----------------+---------------+------+
| Baird      |       1 | Jacinto          |            1 | Marcus         |             1 |    2 |
.
.
| Paduk      |       2 | Unknown          |            0 | Baird          |             1 |    1 |
+------------+---------+------------------+--------------+----------------+---------------+------+
30 rows in set (0.00 sec)

regards,
Umesh
[26 Oct 2020 8:46] Laurents Meyer 
I can confirm that this has been fixed in 8.0.22.
Therefore this issue can be closed.

(Unfortunately we cannot use 8.0.22 due to Bug #101297.)