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
Triage: Needs Triage: D2 (Serious)

[14 Jun 2013 13:26] Christian Rijke
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
[14 Jun 2013 13:34] Christian Rijke
Experienced this in 5.6.10 but not in 5.5-versions.
[14 Jun 2013 14:23] Miguel Solorzano
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>
[24 Jun 2013 20:49] Rahul Gulati
This works correctly in 5.1 as well. I might take this up and provide a patch/fix.
[17 Jul 2013 16:46] Paul Dubois
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.