MySQL Bugs: #110017: SELECT returns incorrect value when ORDER BY a CASE WHEN value in left-joined su

Bug #110017	SELECT returns incorrect value when ORDER BY a CASE WHEN value in left-joined su
Submitted:	10 Feb 2023 6:27	Modified:	10 Feb 2023 9:03
Reporter:	Taro Suzuki	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.7.41	OS:	Ubuntu
Assigned to:		CPU Architecture:	Any

Description:
SELECT returns incorrect value when ORDER BY a CASE WHEN value in left-joined subquery.

How to repeat:
```
$ docker run --name test -e MYSQL_ROOT_PASSWORD=password -d mysql:5.7.41  &&  sleep 10  &&  docker exec -it test mysql -ppassword  &&  docker rm -f test
7c7c2cd339982d1849c5c8b8218cce51028fd8181218fd925a3cd8b98f503881
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.41 MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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> CREATE DATABASE db1;
Query OK, 1 row affected (0.00 sec)

mysql> USE db1;
Database changed
mysql> 
mysql> CREATE TABLE headers (id int PRIMARY KEY);
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE histories (id int PRIMARY KEY, header_id int);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO headers VALUES (1);
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> SELECT *
    -> FROM headers
    -> LEFT JOIN (
    ->   SELECT h.*, CASE WHEN true THEN 1 END AS cased
    ->   FROM histories AS h
    -> ) AS histories ON headers.id = histories.header_id
    -> ORDER BY histories.id;
+----+------+-----------+-------+
| id | id   | header_id | cased |
+----+------+-----------+-------+
|  1 | NULL |      NULL |     1 |
+----+------+-----------+-------+
1 row in set (0.00 sec)
```

expected result:

+----+------+-----------+-------+
| id | id   | header_id | cased |
+----+------+-----------+-------+
|  1 | NULL |      NULL |  NULL |  <- cased is null
+----+------+-----------+-------+

Hello Taro Suzuki,

Thank you for the report and test case.

regards,
Umesh

btw how can I edit my first post ?  > [10 Feb 6:27] Taro Suzuki

Hello Taro San,

>>btw how can I edit my first post ?  > [10 Feb 6:27] Taro Suzuki

IMHO, you cannot edit, same for me as well.
Is there anything you would like to edit etc, I can forward it to concern team or I can mark the description private if you wish to. Please let me know.

regards,
Umesh

>you cannot edit
ok. thanks

The patch will work well

Attachment: case_when_not_null_value_in_left_join_bugfix.patch (application/octet-stream, text), 988 bytes.