Bug #119187 CTE with correlated EXISTS subquery causes incorrect NULL handling in WHERE clause with IS NULL predicate

• Submitted: 18 Oct 2025 4:41
• Modified: 2 Apr 9:30
• Reporter: Runyuan He
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.4.0
• OS: Linux
• CPU Architecture: x86

[18 Oct 2025 4:41] Runyuan He

Description:
A Common Table Expression (CTE) containing both a correlated EXISTS subquery and a NULL expression produces inconsistent results when filtering on the NULL column. Using WHERE TRUE correctly returns NULL, but using WHERE s.c2 IS NULL incorrectly returns an empty set.

How to repeat:
CREATE  TABLE  t1(c INT);
CREATE  TABLE  t2(c INT);
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1);

WITH sub AS (SELECT
  EXISTS(SELECT 1 FROM t2 WHERE t1.c = t2.c) AS c1,
  (NULL AND NULL) AS c2
FROM t1 INNER JOIN t2 ON t1.c = t2.c) SELECT s.c2
FROM sub s
WHERE TRUE;
-- NULL, Correct

WITH sub AS (SELECT
  EXISTS(SELECT 1 FROM t2 WHERE t1.c = t2.c) AS c1,
  (NULL AND NULL) AS c2
FROM t1 INNER JOIN t2 ON t1.c = t2.c) SELECT s.c2
FROM sub s
WHERE s.c2 IS NULL;
-- Empty set, Wrong

[15 Jan 10:12] Øystein Grøvlen

Thank you for your bug report.
Verified as described.

[2 Apr 9:30] Chaithra Marsur Gopala Reddy

This is identified as duplicate of https://bugs.mysql.com/bug.php?id=119780