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

• Submitted: 7 Oct 2025 5:19
• Modified: 15 Dec 2025 11:22
• Reporter: Runyuan He
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.4.0
• OS: Linux
• CPU Architecture: x86

[7 Oct 2025 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

[15 Dec 2025 11:22] Øystein Grøvlen

Thank you for your report.  Verified as described.

Workaround: 
set optimizer_switch='derived_condition_pushdown=off';