Bug #118937 LEFT JOIN with constant functions returns incorrect results when condition is ON FALSE

• Submitted: 4 Sep 4:36
• Modified: 9 Sep 23:42
• Reporter: Runyuan He
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.4.0
• OS: Any
• CPU Architecture: Any

[4 Sep 4:36] Runyuan He

Description:
MySQL optimizer incorrectly handles LEFT JOIN queries when the right-side subquery contains constant function expressions (like JSON_ARRAY_INSERT(), sin(), MOD(), ...) and the JOIN condition is ON FALSE.

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

SELECT * FROM t1 LEFT JOIN
(SELECT JSON_ARRAY_INSERT('[100,200]','$[1]',250) AS c0 FROM t1) AS t2 ON FALSE
WHERE t2.c0 IS NOT NULL;
-- 1 row should be empty set
SELECT * FROM t1 LEFT JOIN
(SELECT sin(radians(30)) AS c0 FROM t1) AS t2 ON FALSE
WHERE t2.c0 IS NOT NULL;
-- 1 row should be empty set
SELECT * FROM t1 LEFT JOIN
(SELECT MOD(5, 2) AS c0 FROM t1) AS t2 ON FALSE
WHERE t2.c0 IS NOT NULL;
-- 1 row should be empty set

[4 Sep 4:49] Runyuan He

Disabling the derived_merge optimization resolves this issue:
SET SESSION optimizer_switch='derived_merge=off';
So it might be the bug in derived_merge optimizer.

[9 Sep 23:42] MySQL Verification Team

Thanks for the report and test case.