Description:
When a derived table on the right side of a LEFT JOIN contains a column
defined by a pure constant expression (e.g. "1 + 2 AS e1"), and that
column is referenced inside a NOT EXISTS subquery, MySQL incorrectly
constant-folds the expression and ignores the fact that the LEFT JOIN
may produce NULL for that column when there is no matching row.
As a result, the NOT EXISTS predicate is evaluated incorrectly and the
query returns the wrong number of rows.
The same query returns the correct result on SQLite, PostgreSQL, and
Dameng DB, all of which correctly preserve the NULL semantics of the
LEFT JOIN.
How to repeat:
-- Setup
CREATE TABLE a(x INT);
CREATE TABLE b(y INT);
CREATE TABLE c(z INT);
INSERT INTO a VALUES (1);
INSERT INTO b VALUES (1);
INSERT INTO c VALUES (1);
-- Query
SELECT a.x
FROM a
LEFT JOIN (SELECT b.y, 1 + 2 AS e1 FROM b WHERE b.y = 999) AS t
ON t.y = 1
WHERE NOT EXISTS(SELECT 1 FROM c WHERE t.e1 <= 9);
-- Expected result: 1 row (value 1)
-- The derived table t has no matching row (b.y = 999 matches nothing),
-- so t.e1 is NULL. "NULL <= 9" is NULL (not TRUE), so EXISTS is FALSE
-- and NOT EXISTS is TRUE. The row from a is returned.
--
-- Actual result on MySQL: 0 rows
-- MySQL constant-folds "1 + 2" to 3, ignores the LEFT JOIN NULL
-- semantics, evaluates "3 <= 9" as TRUE, so EXISTS is TRUE and
-- NOT EXISTS is FALSE. No rows are returned.
