MySQL Bugs: #69471: UNION of derived tables returns wrong results with "1=0/false"-clauses

Bug #69471	UNION of derived tables returns wrong results with "1=0/false"-clauses
Submitted:	14 Jun 2013 13:26	Modified:	17 Jul 2013 16:46
Reporter:	Christian Rijke	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.6.10	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	1=0, query, regression, UNION

Description:
When selecting a union of an empty result set (created with "where 1=0" or "where false"), with a derived table, wrong filtering is applied to the derived table.

How to repeat:
CREATE TABLE table1 ( a INT, b INT);

CREATE TABLE table2 ( a INT, b INT);

INSERT INTO table1 VALUES (1, 4);
INSERT INTO table1 VALUES (2, 4);
INSERT INTO table2 VALUES (1, 5);
INSERT INTO table2 VALUES (2, 5);

SELECT *
FROM  ( 
	(SELECT *
           FROM table1
          WHERE 1=0)       
UNION
       	 (SELECT *
     	    FROM (SELECT * from table2) t2
           WHERE t2.b = 5)
        ) a3
;

This should return:

1	5
2	5

but returns:

1      5

Experienced this in 5.6.10 but not in 5.5-versions.

Thank you for the bug report.

[miguel@tikal 5.6]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A    

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1                            
Server version: 5.6.13 Source distribution               

Copyright (c) 2000, 2013, 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> CREATE TABLE table1 ( a INT, b INT);
Query OK, 0 rows affected (0.44 sec)       

mysql> 
mysql> CREATE TABLE table2 ( a INT, b INT);
Query OK, 0 rows affected (0.43 sec)       

mysql> 
mysql> INSERT INTO table1 VALUES (1, 4);
Query OK, 1 row affected (0.05 sec)     

mysql> INSERT INTO table1 VALUES (2, 4);
Query OK, 1 row affected (0.04 sec)     

mysql> INSERT INTO table2 VALUES (1, 5);
Query OK, 1 row affected (0.23 sec)     

mysql> INSERT INTO table2 VALUES (2, 5);
Query OK, 1 row affected (0.04 sec)     

mysql> 
mysql> SELECT *
    -> FROM  ( 
    -> (SELECT *
    ->            FROM table1
    ->           WHERE 1=0)       
    -> UNION                      
    ->         (SELECT *          
    ->          FROM (SELECT * from table2) t2
    ->            WHERE t2.b = 5)             
    ->         ) a3                           
    -> ;                                      
+------+------+                               
| a    | b    |                               
+------+------+                               
|    2 |    5 |                               
+------+------+                               
1 row in set (0.00 sec)                       

mysql> exit
Bye        
[miguel@tikal 5.6]$ bin/mysqladmin -uroot shutdown
[miguel@tikal 5.6]$ cd ..                         
[miguel@tikal bzr]$ cd 5.5                        
[miguel@tikal 5.5]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A    

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1                            
Server version: 5.5.33-debug Source distribution         

Copyright (c) 2000, 2013, 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> CREATE TABLE table1 ( a INT, b INT);
Query OK, 0 rows affected (0.13 sec)       

mysql>
mysql> CREATE TABLE table2 ( a INT, b INT);
Query OK, 0 rows affected (0.11 sec)

mysql>
mysql> INSERT INTO table1 VALUES (1, 4);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO table1 VALUES (2, 4);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO table2 VALUES (1, 5);
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO table2 VALUES (2, 5);
Query OK, 1 row affected (0.06 sec)

mysql>
mysql> SELECT *
    -> FROM  (
    -> (SELECT *
    ->            FROM table1
    ->           WHERE 1=0)
    -> UNION
    ->         (SELECT *
    ->          FROM (SELECT * from table2) t2
    ->            WHERE t2.b = 5)
    ->         ) a3
    -> ;
+------+------+
| a    | b    |
+------+------+
|    1 |    5 |
|    2 |    5 |
+------+------+
2 rows in set (0.01 sec)

mysql>

This works correctly in 5.1 as well. I might take this up and provide a patch/fix.

Noted in 5.6.13, 5.7.2 changelogs.

When selecting a union of an empty result set (created with WHERE 1=0
or WHERE FALSE) with a derived table, incorrect filtering was applied
to the derived table.