Bug #110356 the presence of case when cause inconsistant columns mapping in order by

• Submitted: 14 Mar 2023 8:39
• Modified: 14 Mar 2023 9:31
• Reporter: moi moi
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.7, 8.0
• OS: Any
• CPU Architecture: Any

[14 Mar 2023 8:39] moi moi

Description:
The two following request doesn't return the same result even if stat column have only 0 and 1 values

SELECT 
`Project1`.`C1` AS `C2`, 
`Project1`.`C3` AS `C1`
 FROM (
 SELECT `Extent1`.`dt` as  C1, 
 `Extent1`.`stat` as C3
 FROM `tmp` AS `Extent1`) Project1
 ORDER BY 
Project1.C1 ASC, 
Project1.C3 desc

SELECT 
`Project1`.`C1` AS `C2`, 
`Project1`.`C3` AS `C1`
 FROM (
 SELECT `Extent1`.`dt` as  C1, 
case when `Extent1`.`stat` = 1 then 1 else 0 end as C3
 FROM `tmp` AS `Extent1`) Project1
 ORDER BY 
Project1.C1 ASC, 
Project1.C3 desc

the only thing that change between this two request is the presence of a "case when" that doesn't change the data so the final result should not change between the two requests

How to repeat:
CREATE TABLE `tmp` (
  `dt` date DEFAULT NULL,
  `stat` int DEFAULT NULL
);

insert the following rows
INSERT INTO tmp (dt, stat) VALUES('2023-03-10', 1);
INSERT INTO tmp (dt, stat) VALUES('2023-03-10', 1);
INSERT INTO tmp (dt, stat) VALUES('2023-03-20', 0);
INSERT INTO tmp (dt, stat) VALUES('2023-03-13', 0);
INSERT INTO tmp (dt, stat) VALUES('2023-03-29', 0);

this request

SELECT 
`Project1`.`C1` AS `C2`, 
`Project1`.`C3` AS `C1`
 FROM (
 SELECT `Extent1`.`dt` as  C1, 
 `Extent1`.`stat` as C3
 FROM `tmp` AS `Extent1`) Project1
 ORDER BY 
Project1.C1 ASC, 
Project1.C3 desc

 gives the good results:

2023-03-10	1
2023-03-10	1
2023-03-13	0
2023-03-20	0
2023-03-29	0

this one 
SELECT 
`Project1`.`C1` AS `C2`, 
`Project1`.`C3` AS `C1`
 FROM (
 SELECT `Extent1`.`dt` as  C1, 
case when `Extent1`.`stat` = 1 then 1 else 0 end as C3
 FROM `tmp` AS `Extent1`) Project1
 ORDER BY 
Project1.C1 ASC, 
Project1.C3 desc

gives wrong result
2023-03-20	0
2023-03-13	0
2023-03-29	0
2023-03-10	1
2023-03-10	1

[14 Mar 2023 9:31] MySQL Verification Team

Hello moi moi,

Thank you for the report and test case.
Observed that 5.7 and 8.0 are affected.

regards,
Umesh