Bug #120098 Incorrect result when using EXISTS with INTERSECT ALL and correlated subquery

• Submitted: 18 Mar 9:58
• Modified: 18 Mar 10:58
• Reporter: Guo Yuxiao
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 8.0.45
• OS: Any
• CPU Architecture: Any

[18 Mar 9:58] Guo Yuxiao

Description:
I found a logic bug in MySQL 9.6.0.

When using INTERSECT ALL in EXCEPT, NULL values will be erroneously included in the execution result.

How to repeat:
-- create table
DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (c0 VARCHAR(10));
INSERT INTO t2 VALUES (''), (NULL);

-- query, expect:{''}, actual:{'', NULL}
SELECT
  t2.c0
FROM t2
WHERE EXISTS (
  SELECT 1 FROM t2 AS t4 WHERE t2.c0 IS NOT NULL
  INTERSECT ALL
  SELECT 1 FROM t2 AS t4 WHERE t2.c0 IS NOT NULL
);

[18 Mar 10:58] Roy Lyseng

Thank you for the bug report.
Verified as described.