Bug #119110 Incorrect NULL filtering in CTE with UNION ALL over view containing LEFT JOIN on FALSE

• Submitted: 7 Oct 5:19
• Modified: 7 Oct 12:28
• Reporter: Runyuan He
• Status: Analyzing
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.4.0
• OS: Linux
• Assigned to: MySQL Verification Team
• CPU Architecture: x86

[7 Oct 5:19] Runyuan He

Description:
A query combining a CTE (Common Table Expression) with UNION ALL produces an incorrect empty result set when filtering for NULL values. The expected result should return one row with NULL.

How to repeat:
CREATE  TABLE  t0(c BOOLEAN);
CREATE  TABLE  t1(c BOOLEAN);
INSERT INTO t0 VALUES (TRUE);
INSERT INTO t1 VALUES (TRUE);

CREATE VIEW v AS SELECT
    (NULL AND t1.c) AS c
FROM t0 LEFT JOIN t1 ON FALSE;

WITH s AS (
  SELECT c FROM v
  UNION ALL
  SELECT c FROM v WHERE FALSE
)
SELECT c
FROM s
WHERE c IS NULL;
-- Empty set, should be NULL