Description:
When a query uses `UNION ALL` with `AND` expression and `WHERE IS UNKNOWN` condition, the query returns incorrect result. The `WHERE IS UNKNOWN` condition fails to match NULL values in the derived table that uses `UNION ALL`.
How to repeat:
1. Create table and insert data
CREATE TABLE t_union_all_bug (id INT, c0 INT, c1 INT);
INSERT INTO t_union_all_bug VALUES
(1, NULL, NULL), -- (NULL AND NULL) = NULL
(2, 0, 0), -- (0 AND 0) = 0
(3, 1, 1); -- (1 AND 1) = 1
2. Execute SQL
SELECT COUNT(*) AS cnt
FROM (
SELECT (t.c0 AND t.c1) AS col0
FROM t_union_all_bug t
UNION ALL
SELECT (t.c0 AND t.c1) AS col0
FROM t_union_all_bug t
) AS sub
WHERE (sub.col0) IS UNKNOWN;
We get result:
```
+-----+
| cnt |
+-----+
| 0 |
+-----+
```
But the correct result is:
```
+-----+
| cnt |
+-----+
| 2 |
+-----+
```
The subquery contains 2 NULL values (because UNION ALL does not remove duplicates), so `WHERE IS UNKNOWN` should match these 2 NULL values and return 2.
Description: When a query uses `UNION ALL` with `AND` expression and `WHERE IS UNKNOWN` condition, the query returns incorrect result. The `WHERE IS UNKNOWN` condition fails to match NULL values in the derived table that uses `UNION ALL`. How to repeat: 1. Create table and insert data CREATE TABLE t_union_all_bug (id INT, c0 INT, c1 INT); INSERT INTO t_union_all_bug VALUES (1, NULL, NULL), -- (NULL AND NULL) = NULL (2, 0, 0), -- (0 AND 0) = 0 (3, 1, 1); -- (1 AND 1) = 1 2. Execute SQL SELECT COUNT(*) AS cnt FROM ( SELECT (t.c0 AND t.c1) AS col0 FROM t_union_all_bug t UNION ALL SELECT (t.c0 AND t.c1) AS col0 FROM t_union_all_bug t ) AS sub WHERE (sub.col0) IS UNKNOWN; We get result: ``` +-----+ | cnt | +-----+ | 0 | +-----+ ``` But the correct result is: ``` +-----+ | cnt | +-----+ | 2 | +-----+ ``` The subquery contains 2 NULL values (because UNION ALL does not remove duplicates), so `WHERE IS UNKNOWN` should match these 2 NULL values and return 2.