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:
None 
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
Description:
The following query works fine in 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`;

It returns a resultset like this:

Constant        | OrderID
----------------|--------
MyConstantValue | 1
MyConstantValue | 2

In 8.0.17, the same query returns the following resultset:
Constant        | OrderID
----------------|--------
NULL            | 1
NULL            | 2

It appears that, when returning a constant value from a left joined table and then sorting over the final query by using another column from the left joined table, the constant value becomes NULL.

So the following queries DO work in 8.0.17:

/* Change >> ORDER BY `od1`.`OrderID` << to >> ORDER BY `o`.`OrderID` <<*/
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 `o`.`OrderID`;

/* Remove entire ORDER BY clause. */
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`;

/* Change LEFT JOIN to INNER JOIN */
SELECT `od1`.`Constant`, `od1`.`OrderID`
FROM `Orders` AS `o`
INNER JOIN (
    SELECT 'MyConstantValue' AS `Constant`, `od`.`OrderID`
    FROM `Order Details` AS `od`
) AS `od1` ON `o`.`OrderID` = `od1`.`OrderID`
ORDER BY `od1`.`OrderID`;

This is a regression bug!

How to repeat:
Run the following statements in 8.0.17 and 5.7:

CREATE TABLE `Orders` (
  `OrderID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`OrderID`)
) ENGINE=InnoDB;

CREATE TABLE `Order Details` (
  `OrderID` int(11) NOT NULL,
  PRIMARY KEY (`OrderID`)
) ENGINE=InnoDB;

INSERT INTO `Orders` (`OrderID`) VALUES (1);
INSERT INTO `Orders` (`OrderID`) VALUES (2);

INSERT INTO `Order Details` (`OrderID`) VALUES (1);
INSERT INTO `Order Details` (`OrderID`) VALUES (2);

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`;

Suggested fix:
Sorting over a returned column from a LEFT JOINed table should not set it's returned constant values to NULL.

The following is the expected result of the original query:

Constant        | OrderID
----------------|--------
MyConstantValue | 1
MyConstantValue | 2
[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.