Bug #101297 [REGRESSION] Inconsequent results when running the same query
Submitted: 23 Oct 20:00 Modified: 23 Oct 22:37
Reporter: Laurents Meyer (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S1 (Critical)
Version:8.0.22 OS:Any
Assigned to: CPU Architecture:Any

[23 Oct 20:00] Laurents Meyer
Description:
Run the script as posted under `How to repeat`.

Then run the following read-only SELECT query a first time:

SELECT `t`.`Id`, `w`.`Name`, `w`.`Id`
FROM `Tags` AS `t`
LEFT JOIN (
    SELECT `g`.`Nickname`, `g`.`SquadId`, `g`.`AssignedCityName`, `g`.`CityOfBirthName`, `g`.`Discriminator`, `g`.`FullName`, `g`.`HasSoulPatch`, `g`.`LeaderNickname`, `g`.`LeaderSquadId`, `g`.`Rank`
    FROM `Gears` AS `g`
    WHERE `g`.`Discriminator` IN ('Gear', 'Officer')
) AS `t0` ON `t`.`GearNickName` = `t0`.`Nickname`
LEFT JOIN `Weapons` AS `w` ON `t0`.`FullName` = `w`.`OwnerFullName`
ORDER BY `t`.`Note`, `t`.`Id`, `w`.`Id`

The query result is the following, which is correct and expected:

+--------------------------------------+-------------------+------+
| Id                                   | Name              | Id   |
+--------------------------------------+-------------------+------+
| 70534e05-782c-4052-8720-c2c54481ce5f | Baird's Lancer    |    7 |
| 70534e05-782c-4052-8720-c2c54481ce5f | Baird's Gnasher   |    8 |
| a8ad98f9-e023-4e2a-9a70-c2728455bd34 | Cole's Gnasher    |    5 |
| a8ad98f9-e023-4e2a-9a70-c2728455bd34 | Cole's Mulcher    |    6 |
| df36f493-463f-4123-83f9-6b135deeb7ba | Dom's Hammerburst |    3 |
| df36f493-463f-4123-83f9-6b135deeb7ba | Dom's Gnasher     |    4 |
| b39a6fba-9026-4d69-828e-fd7068673e57 | NULL              | NULL |
| 34c8d86e-a4ac-4be5-827f-584dda348a07 | Marcus' Lancer    |    1 |
| 34c8d86e-a4ac-4be5-827f-584dda348a07 | Marcus' Gnasher   |    2 |
| a7be028a-0cf2-448f-ab55-ce8bc5d8cf69 | Paduk's Markza    |    9 |
+--------------------------------------+-------------------+------+
10 rows in set (0.01 sec)

It contains `NULL` values for the `Name` and `Id` in one row.

Now run the same read-only SELECT query again.
This second query execution result is the following, which is incorrect and unexpected:

+--------------------------------------+-------------------+------+
| Id                                   | Name              | Id   |
+--------------------------------------+-------------------+------+
| 70534e05-782c-4052-8720-c2c54481ce5f | Baird's Lancer    |    7 |
| 70534e05-782c-4052-8720-c2c54481ce5f | Baird's Gnasher   |    8 |
| a8ad98f9-e023-4e2a-9a70-c2728455bd34 | Cole's Gnasher    |    5 |
| a8ad98f9-e023-4e2a-9a70-c2728455bd34 | Cole's Mulcher    |    6 |
| df36f493-463f-4123-83f9-6b135deeb7ba | Dom's Hammerburst |    3 |
| df36f493-463f-4123-83f9-6b135deeb7ba | Dom's Gnasher     |    4 |
| b39a6fba-9026-4d69-828e-fd7068673e57 | Mauler's Flail    |   10 |
| 34c8d86e-a4ac-4be5-827f-584dda348a07 | Marcus' Lancer    |    1 |
| 34c8d86e-a4ac-4be5-827f-584dda348a07 | Marcus' Gnasher   |    2 |
| a7be028a-0cf2-448f-ab55-ce8bc5d8cf69 | Paduk's Markza    |    9 |
+--------------------------------------+-------------------+------+
10 rows in set (0.00 sec)

The row that correctly contained `NULL` values before, does not anymore.
Repeated query executions will result in the same incorrect result, until you recreate the tables again.

This query repeatedly worked fine in all versions before 8.0.22, so this is a regression bug.

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	5.7.32-log

/*!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 NOT NULL,
  `SquadId` int(11) NOT NULL,
  `FullName` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
  `CityOfBirthName` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
  `AssignedCityName` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  `Rank` int(11) NOT NULL,
  `LeaderNickname` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  `LeaderSquadId` int(11) NOT NULL,
  `HasSoulPatch` tinyint(1) NOT NULL,
  `Discriminator` longtext CHARACTER SET utf8mb4 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_SquadId` (`SquadId`),
  KEY `IX_Gears_LeaderNickname_LeaderSquadId` (`LeaderNickname`,`LeaderSquadId`),
  CONSTRAINT `FK_Gears_Cities_AssignedCityName` FOREIGN KEY (`AssignedCityName`) REFERENCES `Cities` (`Name`),
  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`),
  CONSTRAINT `FK_Gears_Squads_SquadId` FOREIGN KEY (`SquadId`) REFERENCES `Squads` (`Id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
/*!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',1,'Marcus',1,1,'Officer'),('Cole Train',1,'Augustus Cole','Hanover','Jacinto',0,'Marcus',1,0,'Gear'),('Dom',1,'Dominic Santiago','Ephyra','Ephyra',1,'Marcus',1,0,'Gear'),('Marcus',1,'Marcus Fenix','Jacinto',NULL,2,NULL,0,1,'Officer'),('Paduk',2,'Garron Paduk','Unknown','Unknown',0,'Baird',1,0,'Gear');
/*!40000 ALTER TABLE `Gears` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `Tags`
--

DROP TABLE IF EXISTS `Tags`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `Tags` (
  `Id` char(36) COLLATE utf8mb4_bin NOT NULL,
  `Note` longtext CHARACTER SET utf8mb4,
  `GearNickName` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  `GearSquadId` int(11) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `IX_Tags_GearNickName_GearSquadId` (`GearNickName`,`GearSquadId`),
  CONSTRAINT `FK_Tags_Gears_GearNickName_GearSquadId` FOREIGN KEY (`GearNickName`, `GearSquadId`) REFERENCES `Gears` (`Nickname`, `SquadId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `Tags`
--

LOCK TABLES `Tags` WRITE;
/*!40000 ALTER TABLE `Tags` DISABLE KEYS */;
INSERT INTO `Tags` (`Id`, `Note`, `GearNickName`, `GearSquadId`) VALUES ('34c8d86e-a4ac-4be5-827f-584dda348a07','Marcus\' Tag','Marcus',1),('70534e05-782c-4052-8720-c2c54481ce5f','Baird\'s Tag','Baird',1),('a7be028a-0cf2-448f-ab55-ce8bc5d8cf69','Paduk\'s Tag','Paduk',2),('a8ad98f9-e023-4e2a-9a70-c2728455bd34','Cole\'s Tag','Cole Train',1),('b39a6fba-9026-4d69-828e-fd7068673e57','K.I.A.',NULL,NULL),('df36f493-463f-4123-83f9-6b135deeb7ba','Dom\'s Tag','Dom',1);
/*!40000 ALTER TABLE `Tags` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `Weapons`
--

DROP TABLE IF EXISTS `Weapons`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `Weapons` (
  `Id` int(11) NOT NULL,
  `Name` longtext CHARACTER SET utf8mb4,
  `AmmunitionType` int(11) DEFAULT NULL,
  `IsAutomatic` tinyint(1) NOT NULL,
  `SynergyWithId` int(11) DEFAULT NULL,
  `OwnerFullName` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `IX_Weapons_SynergyWithId` (`SynergyWithId`),
  KEY `IX_Weapons_IsAutomatic` (`IsAutomatic`),
  KEY `IX_Weapons_OwnerFullName` (`OwnerFullName`),
  CONSTRAINT `FK_Weapons_Gears_OwnerFullName` FOREIGN KEY (`OwnerFullName`) REFERENCES `Gears` (`FullName`),
  CONSTRAINT `FK_Weapons_Weapons_SynergyWithId` FOREIGN KEY (`SynergyWithId`) REFERENCES `Weapons` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `Weapons`
--

LOCK TABLES `Weapons` WRITE;
/*!40000 ALTER TABLE `Weapons` DISABLE KEYS */;
INSERT INTO `Weapons` (`Id`, `Name`, `AmmunitionType`, `IsAutomatic`, `SynergyWithId`, `OwnerFullName`) VALUES (1,'Marcus\' Lancer',1,1,NULL,'Marcus Fenix'),(2,'Marcus\' Gnasher',2,0,1,'Marcus Fenix'),(3,'Dom\'s Hammerburst',1,0,NULL,'Dominic Santiago'),(4,'Dom\'s Gnasher',2,0,NULL,'Dominic Santiago'),(5,'Cole\'s Gnasher',2,0,NULL,'Augustus Cole'),(6,'Cole\'s Mulcher',1,1,NULL,'Augustus Cole'),(7,'Baird\'s Lancer',1,1,NULL,'Damon Baird'),(8,'Baird\'s Gnasher',2,0,NULL,'Damon Baird'),(9,'Paduk\'s Markza',1,0,NULL,'Garron Paduk'),(10,'Mauler\'s Flail',NULL,0,NULL,NULL);
/*!40000 ALTER TABLE `Weapons` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping events 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 */;

-- Dump completed on 2020-10-23 21:38:00

Suggested fix:
The query should repeatedly return the correct result (deterministically).
[23 Oct 22:36] MySQL Verification Team
Thank you for the bug report.