Bug #96947 | [REGRESSION] Order by and constant from left joined table result in NULL value | ||
---|---|---|---|
Submitted: | 19 Sep 2019 15:13 | Modified: | 28 Jun 2021 7:26 |
Reporter: | Laurents Meyer (OCA) | Email Updates: | |
Status: | Duplicate | Impact on me: | |
Category: | MySQL Server: DML | Severity: | S1 (Critical) |
Version: | 5.7/8.0 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | left join, order by |
[19 Sep 2019 15:13]
Laurents Meyer
[19 Sep 2019 16:09]
MySQL Verification Team
Thank you for the bug report. 5.7 and 8.0 return both the wrong result you reported however 5.6 behaves in the correct way you reported: d:\dbs>d:\dbs\5.6\bin\mysql -uroot --local-infile --port=3560 -p --ssl --ssl-ca=c:\dbs\ssl\ca.pem --ssl-cert=c:\dbs\ssl\client-cert.pem --ssl-key=c:\dbs\ssl\client-key.pem --prompt="mysql 5.6 > " Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.45-log MySQL Community Server (GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.6 > CREATE DATABASE db; Query OK, 1 row affected (0.01 sec) mysql 5.6 > USE db Database changed mysql 5.6 > CREATE TABLE `Orders` ( -> `OrderID` int(11) NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (`OrderID`) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) mysql 5.6 > mysql 5.6 > CREATE TABLE `Order Details` ( -> `OrderID` int(11) NOT NULL, -> PRIMARY KEY (`OrderID`) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) mysql 5.6 > mysql 5.6 > INSERT INTO `Orders` (`OrderID`) VALUES (1); Query OK, 1 row affected (0.00 sec) mysql 5.6 > INSERT INTO `Orders` (`OrderID`) VALUES (2); Query OK, 1 row affected (0.00 sec) mysql 5.6 > mysql 5.6 > INSERT INTO `Order Details` (`OrderID`) VALUES (1); Query OK, 1 row affected (0.00 sec) mysql 5.6 > INSERT INTO `Order Details` (`OrderID`) VALUES (2); Query OK, 1 row affected (0.00 sec) mysql 5.6 > mysql 5.6 > SELECT `od1`.`Constant`, `od1`.`OrderID` -> FROM `Orders` AS `o` -> LEFT JOIN ( -> SELECT 'MyConstantValue' AS `Constant`, `od`.`OrderID` -> FROM `Order Details` AS `od` -> ) AS `od1` ON `o`.`OrderID` = `od1`.`OrderID` -> ORDER BY `od1`.`OrderID`; +-----------------+---------+ | Constant | OrderID | +-----------------+---------+ | MyConstantValue | 1 | | MyConstantValue | 2 | +-----------------+---------+ 2 rows in set (0.00 sec) mysql 5.6 > D:\dbs>d:\dbs\8.0\bin\mysql -uroot --port=3580 -p --prompt="mysql 8.0 > " --default-character-set=utf8mb4 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.18 Source distribution BUILT: 2019-AUG-26 Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 8.0 > CREATE DATABASE db; Query OK, 1 row affected (0.01 sec) mysql 8.0 > USE db Database changed mysql 8.0 > CREATE TABLE `Orders` ( -> `OrderID` int(11) NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (`OrderID`) -> ) ENGINE=InnoDB; Query OK, 0 rows affected, 1 warning (0.03 sec) mysql 8.0 > mysql 8.0 > CREATE TABLE `Order Details` ( -> `OrderID` int(11) NOT NULL, -> PRIMARY KEY (`OrderID`) -> ) ENGINE=InnoDB; Query OK, 0 rows affected, 1 warning (0.02 sec) mysql 8.0 > mysql 8.0 > INSERT INTO `Orders` (`OrderID`) VALUES (1); Query OK, 1 row affected (0.01 sec) mysql 8.0 > INSERT INTO `Orders` (`OrderID`) VALUES (2); Query OK, 1 row affected (0.01 sec) mysql 8.0 > mysql 8.0 > INSERT INTO `Order Details` (`OrderID`) VALUES (1); Query OK, 1 row affected (0.01 sec) mysql 8.0 > INSERT INTO `Order Details` (`OrderID`) VALUES (2); Query OK, 1 row affected (0.00 sec) mysql 8.0 > mysql 8.0 > SELECT `od1`.`Constant`, `od1`.`OrderID` -> FROM `Orders` AS `o` -> LEFT JOIN ( -> SELECT 'MyConstantValue' AS `Constant`, `od`.`OrderID` -> FROM `Order Details` AS `od` -> ) AS `od1` ON `o`.`OrderID` = `od1`.`OrderID` -> ORDER BY `od1`.`OrderID`; +----------+---------+ | Constant | OrderID | +----------+---------+ | NULL | 1 | | NULL | 2 | +----------+---------+ 2 rows in set (0.00 sec) mysql 8.0 > D:\dbs>d:\dbs\5.7\bin\mysql -uroot --port=3570 -p --prompt="mysql 5.7 > " Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.28-log Source distribution BUILT: 2019-AUG-26 Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.7 > CREATE DATABASE db; Query OK, 1 row affected (0.01 sec) mysql 5.7 > USE db Database changed mysql 5.7 > CREATE TABLE `Orders` ( -> `OrderID` int(11) NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (`OrderID`) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.03 sec) mysql 5.7 > mysql 5.7 > CREATE TABLE `Order Details` ( -> `OrderID` int(11) NOT NULL, -> PRIMARY KEY (`OrderID`) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec) mysql 5.7 > mysql 5.7 > INSERT INTO `Orders` (`OrderID`) VALUES (1); Query OK, 1 row affected (0.01 sec) mysql 5.7 > INSERT INTO `Orders` (`OrderID`) VALUES (2); Query OK, 1 row affected (0.00 sec) mysql 5.7 > mysql 5.7 > INSERT INTO `Order Details` (`OrderID`) VALUES (1); Query OK, 1 row affected (0.01 sec) mysql 5.7 > INSERT INTO `Order Details` (`OrderID`) VALUES (2); Query OK, 1 row affected (0.00 sec) mysql 5.7 > mysql 5.7 > SELECT `od1`.`Constant`, `od1`.`OrderID` -> FROM `Orders` AS `o` -> LEFT JOIN ( -> SELECT 'MyConstantValue' AS `Constant`, `od`.`OrderID` -> FROM `Order Details` AS `od` -> ) AS `od1` ON `o`.`OrderID` = `od1`.`OrderID` -> ORDER BY `od1`.`OrderID`; +----------+---------+ | Constant | OrderID | +----------+---------+ | NULL | 1 | | NULL | 2 | +----------+---------+ 2 rows in set (0.00 sec) mysql 5.7 > ]
[15 Jan 2021 5:13]
Laurents Meyer
This is still a MAJOR bug and has not been addressed yet. As a workaround, explicitly casting all constant values does work: SELECT `od1`.`Constant`, `od1`.`OrderID` FROM `Orders` AS `o` LEFT JOIN ( SELECT CAST('MyConstantValue' as char) AS `Constant`, `od`.`OrderID` FROM `Order Details` AS `od` ) AS `od1` ON `o`.`OrderID` = `od1`.`OrderID` ORDER BY `od1`.`OrderID`; +-----------------+---------+ | Constant | OrderID | +-----------------+---------+ | MyConstantValue | 1 | | MyConstantValue | 2 | +-----------------+---------+ 2 rows in set (0.01 sec)
[25 Jun 2021 10:44]
Laurents Meyer
It appears this has been fixed recently. I am not able to reproduce the issue with MySQL 8.0.25. Can someone confirm and state the exact version this was fixed in?
[28 Jun 2021 7:26]
Knut Anders Hatlen
It looks like this was fixed by bug#101628 in MySQL 8.0.23.