Bug #119594 INTERSECT (SELECT NULL) incorrectly returns non-NULL rows when column is PRIMARY KEY

• Submitted: 24 Dec 14:03
• Modified: 26 Dec 6:17
• Reporter: Seren Zhou
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 9.5.0
• OS: Any
• CPU Architecture: Any

[24 Dec 14:03] Seren Zhou

Description:
When performing an INTERSECT operation between a table query and SELECT NULL, the server incorrectly returns rows from the left-hand side table instead of an empty set.

This issue is strictly reproducible only when the column involved is part of a PRIMARY KEY.

How to repeat:
CREATE TABLE t2(c1 BOOL, PRIMARY KEY(c1));
INSERT INTO t2(c1) VALUES(-62);

SELECT c1 FROM t2 INTERSECT SELECT NULL;

/*
ACTUAL RESULT (Incorrect):
+-----+
| c1  |
+-----+
| -62 |
+-----+
1 row in set

EXPECTED RESULT (Correct):
Empty set (0 rows)
*/

[26 Dec 6:17] Chaithra Marsur Gopala Reddy

Hi Seren Zhou,

Thank you for the test case. Verified as described.