Bug #119074 Incorrect result with derived_condition_pushdown optimization in correlated subquery with LEFT JOIN ON FALSE

• Submitted: 26 Sep 4:46
• Modified: 29 Sep 13:16
• Reporter: Runyuan He
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.4.0
• OS: Linux
• CPU Architecture: x86

[26 Sep 4:46] Runyuan He

Description:
When derived_condition_pushdown=on (default), a query containing a correlated subquery within a CTE (Common Table Expression) that uses LEFT JOIN ON FALSE returns incorrect results. The query should return an empty set but instead returns a row with NULL value.

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

WITH v AS (SELECT
  (SELECT t0.c FROM t0 WHERE t0.c < t1.c) AS c0,
  t0.c AND t1.c AS c1
FROM t0 LEFT JOIN t1 ON FALSE)
SELECT v.c1 FROM v WHERE (NOT v.c1);
-- NULL, wrong, should be empty set

SET SESSION optimizer_switch='derived_condition_pushdown=off';

WITH v AS (SELECT
  (SELECT t0.c FROM t0 WHERE t0.c < t1.c) AS c0,
  t0.c AND t1.c AS c1
FROM t0 LEFT JOIN t1 ON FALSE)
SELECT v.c1 FROM v WHERE (NOT v.c1);
-- Empty set, correct

[29 Sep 13:16] MySQL Verification Team

Thank you for the report