MySQL Bugs: #80068: Invalid result of Query

Bug #80068	Invalid result of Query
Submitted:	20 Jan 2016 10:57	Modified:	24 Dec 2019 15:46
Reporter:	Viktor Kostiuk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	5.7.10, 5.7.15	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
I run the same script on MySQL 5.6 Windows 10 Pro and on MySQL 5.7.20 Windows Server 2012 R2. Got differents results.

How to repeat:
SELECT 
CASE WHEN (NOT((`Ex`.`S` = 1) AND (`Ex`.`S` IS NOT NULL))) THEN ('0X')  ELSE ('0X0X') END AS `C1` From (SELECT 1) as `fake` LEFT OUTER JOIN
(
	Select 1 AS `S` FROM `User` WHERE FALSE
) AS `Ex` on 1 = 1

For MySQL 5.6 C1 = '0X'
For MySQL 5.7 C1 = '0X0X'

MySQL 5.7.20 Windows Server 2012 R2 -> MySQL 5.7.10 Windows Server 2012 R2

Hello Viktor,

Thank you for the report.
I'm not seeing this issue on Liniux/Win7 with 5.7.10/5.6.28. Please could you provide user table and subset of data to reproduce the issue at our end? If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

-- 5.7.10

[root@cluster-repo ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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>
mysql>
mysql> SELECT
    -> CASE WHEN (NOT((`Ex`.`S` = 1) AND (`Ex`.`S` IS NOT NULL))) THEN ('0X')  ELSE ('0X0X') END AS `C1` From (SELECT 1) as `fake` LEFT OUTER JOIN
    -> (
    -> Select 1 AS `S` FROM dual WHERE FALSE
    -> ) AS `Ex` on 1 = 1;
+----+
| C1 |
+----+
| 0X |
+----+
1 row in set (0.00 sec)

-- windows

D:\ushastry\MySQL\mysql-advanced-5.7.10-winx64>bin\mysql -uroot --port=5710 --protocol=tcp
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.10-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, 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> SELECT
    -> CASE WHEN (NOT((Ex.S = 1) AND (Ex.S IS NOT NULL))) THEN ('0X')  ELSE ('0X0X') END AS C1 From (SELECT 1) as fake LEFT
    -> (
    ->  Select 1 AS S FROM dual WHERE FALSE
    -> ) AS Ex on 1 = 1;
+----+
| C1 |
+----+
| 0X |
+----+
1 row in set (0.00 sec)

mysql>

-- 5.6.28

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.28: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.28-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, 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> SELECT
    -> CASE WHEN (NOT((`Ex`.`S` = 1) AND (`Ex`.`S` IS NOT NULL))) THEN ('0X')  ELSE ('0X0X') END AS `C1` From (SELECT 1) as `fake` LEFT OUTER JOIN
    -> (
    -> Select 1 AS `S` FROM dual WHERE FALSE
    -> ) AS `Ex` on 1 = 1;
+------+
| C1   |
+------+
| 0X   |
+------+
1 row in set (0.00 sec)

Thanks,
Umesh

Bug

Attachment: 2L2nZ[1] (application/octet-stream, text), 11.55 KiB.

Thank you for the feedback!
Looks like real table needed instead of dual etc.

-- 5.6.33

mysql> create table t1(id int);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    -> CASE
    -> WHEN (NOT((`Ex`.`S` = 1) AND (`Ex`.`S` IS NOT NULL)))
    -> THEN
    -> ('0X')
    -> ELSE ('0X0X') END AS `C1`
    -> From (SELECT 1) as `fake` LEFT OUTER JOIN (Select 1 AS `S` FROM dual WHERE FALSE) AS `Ex` on 1 = 1;
+------+
| C1   |
+------+
| 0X   |
+------+
1 row in set (0.00 sec)

mysql> SELECT  CASE  WHEN (NOT((`Ex`.`S` = 1) AND (`Ex`.`S` IS NOT NULL)))  THEN ('0X')   ELSE ('0X0X') END AS `C1`  From (SELECT 1) as `fake` LEFT OUTER JOIN (Select 1 AS `S` FROM t1 WHERE FALSE) AS `Ex` on 1 = 1;
+------+
| C1   |
+------+
| 0X   |
+------+
1 row in set (0.00 sec)

-- 5.7.15

mysql> SELECT
    -> CASE
    -> WHEN (NOT((`Ex`.`S` = 1) AND (`Ex`.`S` IS NOT NULL)))
    -> THEN
    -> ('0X')
    -> ELSE ('0X0X') END AS `C1`
    -> From (SELECT 1) as `fake` LEFT OUTER JOIN (Select 1 AS `S` FROM dual WHERE FALSE) AS `Ex` on 1 = 1;
+----+
| C1 |
+----+
| 0X |
+----+
1 row in set (0.00 sec)

mysql> create table t1(id int);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT  CASE  WHEN (NOT((`Ex`.`S` = 1) AND (`Ex`.`S` IS NOT NULL)))  THEN ('0X')   ELSE ('0X0X') END AS `C1`  From (SELECT 1) as `fake` LEFT OUTER JOIN (Select 1 AS `S` FROM t1 WHERE FALSE) AS `Ex` on 1 = 1;
+------+
| C1   |
+------+
| 0X0X |
+------+
1 row in set (0.00 sec)

Thanks,
Umesh

Bug #83051 marked as duplicate of this

-- 5.5.52

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.5.52: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.52-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2016, 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> use test
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT  CASE  WHEN (NOT((`Ex`.`S` = 1) AND (`Ex`.`S` IS NOT NULL)))  THEN ('0X')   ELSE ('0X0X') END AS `C1`  From (SELECT 1) as `fake` LEFT OUTER JOIN (Select 1 AS `S` FROM t1 WHERE FALSE) AS `Ex` on 1 = 1;
+------+
| C1   |
+------+
| 0X   |
+------+
1 row in set (0.00 sec)

mysql> SELECT  CASE  WHEN (NOT((`Ex`.`S` = 1) AND (`Ex`.`S` IS NOT NULL)))  THEN ('0X')   ELSE ('0X0X') END AS `C1`  From (SELECT 1) as `fake` LEFT OUTER JOIN (Select 1 AS `S` FROM dual WHERE FALSE) AS `Ex` on 1 = 1;
+------+
| C1   |
+------+
| 0X   |
+------+
1 row in set (0.00 sec)

mysql>

Posted by developer:
 
Fixed in 5.7.29