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

[23 Oct 2020 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 2020 22:36] MySQL Verification Team 
Thank you for the bug report.
[23 Apr 2021 6:46] Rahul Gupta 
Can you please suggest if this issue is fixed in MySQL 8.0.24?
I did run the test given in MySQL 8.0.24 and it gives correct results each time. I did check MySQL 8.0.24 release notes but didn't see
any reference of this issue. If it is not yet fixed, Please suggest in which version fix would be available.
[23 Apr 2021 8:07] Roy Lyseng 
As far as I can tell, this bug was fixed in 8.0.24 as a side-effect of another fix, however we have not been able to formally close the bug yet.
[23 Apr 2021 9:45] Rahul Gupta 
Thanks for your response. So do we expect this bug to be closed in next release of MySQL or in coming days post verfication?
[23 Apr 2021 10:54] Roy Lyseng 
Yes, I think that will happen.
[13 May 2021 5:22] Harikesh Tripathi 
Could you please confirm if this bug is verified and closed in MySQL 8.0.25?
[3 Jun 2021 18:14] Paul DuBois 
Posted by developer:
 
Fixed in MySQL 8.0.24.

Part of work for WL#14333 -- no separate changelog entry needed.

The work for 8.0.26 was to strengthen the code, also needing no separate changelog entry.
[4 Jun 2021 11:22] Laurents Meyer 
I can confirm, that this appears to be fixed at least in 8.0.25.