Bug #121082 `x NOT IN (<set-operation subquery>)` drops rows

• Submitted: 12 Aug 20:31
• Modified: 13 Aug 6:31
• Reporter: Junwen An
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.46, 8.4.11, 9.7.2
• OS: Linux
• CPU Architecture: ARM

[12 Aug 20:31] Junwen An

Description:
`x NOT IN (<set-operation subquery>)` drops rows when the set operation's result is empty but its input contains NULL. Please see the repro.

How to repeat:
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}

[13 Aug 6:31] Chaithra Marsur Gopala Reddy

HI Junwen An,

Thanks for the test case. Verified as described.