Description:
When querying a derived table created using UNION, applying an OR condition that combines a comparison with an IS NULL check incorrectly omits NULL values from the result set.
How to repeat:
I have the following query:
SELECT * FROM (SELECT NULL as col_1 UNION SELECT 1 as col_1) as u
It returns correctly:
+------+
| col\_1|
+------+
| NULL |
| 1 |
+------+
If I apply a filter:
-- Statement 1
SELECT * FROM (SELECT NULL as col_1 UNION SELECT 1 as col_1) as u
WHERE u.col_1 = 1;
-- Statement 2
SELECT * FROM (SELECT NULL as col_1 UNION SELECT 1 as col_1) as u
WHERE u.col_1 IS NULL;
I also get the correct result: Statment 1: Statment 2:
+------+ +------+
| col\_1| |col\_1 |
+------+ +------+
| 1 | | NULL |
+------+ +------+
But when I'm applying both filters with an OR
-- Statement 1
SELECT * FROM (SELECT NULL as col_1 UNION SELECT 1 as col_1) as u
WHERE u.col_1 = 1;
-- Statement 2
SELECT * FROM (SELECT NULL as col_1 UNION SELECT 1 as col_1) as u
WHERE u.col_1 IS NULL OR u.col_1 = 1;
It is omitting NULL:
+------+
| col\_1|
+------+
| 1 |
+------+
Suggested fix:
It should return the null row.
It won't happen if we add a second column to the compound select also this works:
it's unpredicatable
