Bug #118918 Inconsistent NULL handling in CTE LEFT JOIN results

• Submitted: 1 Sep 7:30
• Modified: 3 Sep 8:31
• Reporter: Runyuan He
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.4.0
• OS: Any
• CPU Architecture: Any

[1 Sep 7:30] Runyuan He

Description:
There is an inconsistency in how MySQL handles NULL values from LEFT JOIN operations within Common Table Expressions (CTEs). When a CTE produces a NULL value through a LEFT JOIN with an empty result set, the behavior differs depending on the WHERE clause condition:

WHERE TRUE returns the NULL value
WHERE cte1.c1 IS NULL returns an empty set

This behavior appears inconsistent because if cte1.c1 is NULL (as shown by the first query), then cte1.c1 IS NULL should evaluate to TRUE and return that row.

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

WITH cte0 AS (
    SELECT
        (t.c AND t.c) AS c0
    FROM t WHERE FALSE
), cte1 AS (
    SELECT
        (SELECT SUM(val) FROM (SELECT t.c AS val) AS sub) AS c0,
        cte0.c0 AS c1
    FROM t LEFT JOIN cte0 ON TRUE
)
SELECT
  cte1.c1
FROM cte1
WHERE TRUE;
-- NULL

WITH cte0 AS (
    SELECT
        (t.c AND t.c) AS c0
    FROM t WHERE FALSE
), cte1 AS (
    SELECT
        (SELECT SUM(val) FROM (SELECT t.c AS val) AS sub) AS c0,
        cte0.c0 AS c1
    FROM t LEFT JOIN cte0 ON TRUE
)
SELECT
  cte1.c1
FROM cte1
WHERE cte1.c1 IS NULL;
-- empty set, should be NULL

[3 Sep 8:31] MySQL Verification Team

Thank you for the report.

mysql> WITH cte0 AS (
    ->     SELECT
    ->         (t.c AND t.c) AS c0
    ->     FROM t WHERE FALSE
    -> ), cte1 AS (
    ->     SELECT
    ->         (SELECT SUM(val) FROM (SELECT t.c AS val) AS sub) AS c0,
    ->         cte0.c0 AS c1
    ->     FROM t LEFT JOIN cte0 ON TRUE
    -> )
    -> SELECT
    ->   cte1.c1
    -> FROM cte1
    -> WHERE TRUE;
+------+
| c1   |
+------+
| NULL |
+------+
1 row in set (0.002 sec)

mysql> -- NULL
Query OK, 0 rows affected (0.000 sec)

mysql> 
mysql> WITH cte0 AS (
    ->     SELECT
    ->         (t.c AND t.c) AS c0
    ->     FROM t WHERE FALSE
    -> ), cte1 AS (
    ->     SELECT
    ->         (SELECT SUM(val) FROM (SELECT t.c AS val) AS sub) AS c0,
    ->         cte0.c0 AS c1
    ->     FROM t LEFT JOIN cte0 ON TRUE
    -> )
    -> SELECT
    ->   cte1.c1
    -> FROM cte1
    -> WHERE cte1.c1 IS NULL;
Empty set (0.001 sec)

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 9.4.0     |
+-----------+
1 row in set (0.000 sec)

mysql>