Bug #119780 UNION ALL + AND + WHERE IS UNKNOWN query results are incorrect
Submitted: 26 Jan 14:40 Modified: 26 Jan 15:15
Reporter: mu mu Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.44 OS:Ubuntu (22.04)
Assigned to: CPU Architecture:Any

[26 Jan 14:40] mu mu 
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.
[26 Jan 15:15] Roy Lyseng 
Thank you for the bug report.
Verified as described.
[3 Apr 10:50] Chaithra Marsur Gopala Reddy 
Bug#118918 is marked as duplicate of this bug
[3 Apr 10:54] Chaithra Marsur Gopala Reddy 
Bug#119074, Bug#120010, Bug#119100, Bug#119187, Bug#119110, Bug#118019 are marked as duplicates of this bug.