Details
-
Bug
-
Status: In Review (View Workflow)
-
Critical
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 12.1.2
-
None
-
Unexpected results
Description
`x NOT IN (<set-operation subquery>)` drops rows when the set operation's result is empty but its input contains NULL.
The following demonstrates the wrong result:
CREATE TABLE b (id BIGINT); |
INSERT INTO b VALUES (NULL),(1); |
|
|
SELECT id FROM b |
WHERE id NOT IN ((SELECT t5.id FROM b AS t5) EXCEPT (SELECT t6.id FROM b AS t6)); |
|
|
-- expected: {NULL, 1} -- actual: {NULL} |
When the table doesn't contain NULL, the result is correct.
CREATE TABLE b (id BIGINT); |
INSERT INTO b VALUES (1),(2); |
|
|
SELECT id FROM b |
WHERE id NOT IN ((SELECT t5.id FROM b AS t5) EXCEPT (SELECT t6.id FROM b AS t6)); |
|
|
-- expected: {1, 2} -- actual: {1, 2} |
Reproducible on MySQL as well
Attachments
Issue Links
- relates to
-
-
- Confirmed
-
