Description:
When subquery_to_derived=on rewrites a correlated EXISTS/NOT EXISTS subquery
in the SELECT list (projection) that contains a LIMIT clause, the rewrite
produces a LATERAL LEFT JOIN against a materialized derived table. During
materialization, the optimizer applies "Limit table size: N unique row(s)"
combined with "Temporary table with deduplication", which retains only N
unique values of the correlation column. When an outer row's correlation
value is not among the N retained values, the LEFT JOIN produces a NULL-padded
row, and EXISTS is incorrectly evaluated as FALSE (or NOT EXISTS as TRUE).
This is a SILENT CORRUPTION — no error is raised, no warning is emitted
(SHOW WARNINGS returns empty). The query returns a result set with wrong
boolean values.
This is DISTINCT from Bug #121017 (which drops LIMIT 1 from scalar subqueries
→ error 1242). In this bug:
- LIMIT N is transformed into "N unique rows" dedup (not dropped)
- The = operator ALSO triggers (no fast-path in EXISTS context, unlike #121017)
- LIMIT 2 also triggers (unlike #121017 where only LIMIT 1 is affected)
- ORDER BY + LIMIT 1 also triggers (unlike #121017 where it does not)
- The symptom is wrong boolean values, not a cardinality error
How to repeat:
CREATE TABLE t0 (id INT PRIMARY KEY, c0 INT);
CREATE TABLE t1 (id INT PRIMARY KEY, c0 INT, c1 INT);
INSERT INTO t0 VALUES (1, 10), (2, 20), (3, 30), (4, NULL);
INSERT INTO t1 VALUES (1, 10, 100), (2, 10, 200), (3, 20, 300), (4, 20, 400), (5, 30, 500), (6, NULL, 600), (7, NULL, 700);
-- Correct behavior (default optimizer):
SET SESSION optimizer_switch = 'default';
SELECT t0.c0, NOT EXISTS(SELECT 1 FROM t1 WHERE t1.c0 = t0.c0 LIMIT 1) AS nex FROM t0;
-- Result: (10, 0), (20, 0), (30, 0), (NULL, 1)
-- All non-NULL c0 values have matching rows in t1, so NOT EXISTS = 0 (FALSE).
-- Wrong behavior (subquery_to_derived=on):
SET SESSION optimizer_switch = 'subquery_to_derived=on';
SELECT t0.c0, NOT EXISTS(SELECT 1 FROM t1 WHERE t1.c0 = t0.c0 LIMIT 1) AS nex FROM t0;
-- Result: (10, 0), (20, 1), (30, 1), (NULL, 1)
-- WRONG: c0=20 and c0=30 should be 0 but get 1. No error, no warning.
-- EXPLAIN shows the root cause:
-- Plan A (default): dependent subquery with "Limit: 1 row(s)" — each outer row
-- independently evaluates the subquery, LIMIT 1 correctly finds matches.
--
-- Plan B (subquery_to_derived=on): Left hash join with materialized derived
-- table containing "Limit table size: 1 unique row(s)" + "Temporary table
-- with deduplication". Only 1 unique c0 value from t1 is retained in the
-- derived table. The LEFT JOIN only matches the single t0 row with that
-- value; all other t0 rows get NULL-padded (EXISTS wrongly = FALSE).
EXPLAIN FORMAT=TREE
SELECT t0.c0, NOT EXISTS(SELECT 1 FROM t1 WHERE t1.c0 = t0.c0 LIMIT 1) AS nex FROM t0;
-- -> Left hash join (derived_1_2.Name_exp_2 = t0.c0)
-- -> Table scan on t0
-- -> Hash
-- -> Table scan on derived_1_2
-- -> Materialize
-- -> Limit: 1 row(s)
-- -> Table scan on <temporary>
-- -> Temporary table with deduplication
-- -> Limit table size: 1 unique row(s) ← BUG
-- -> Table scan on t1
-- Operator sweep (NOT EXISTS + LIMIT 1, subquery_to_derived=on):
-- = : WRONG (c0=20,30 affected)
-- !=: WRONG (c0=10 affected)
-- > : WRONG (c0=10,20 affected)
-- >=: WRONG (c0=20,30 affected)
-- < : correct (coincidentally)
-- <=: correct (coincidentally)
-- <=>: ERROR 3973 (not applicable)
-- LIMIT value sweep (= operator, subquery_to_derived=on):
-- No LIMIT: correct
-- LIMIT 1: WRONG (c0=20,30)
-- LIMIT 2: WRONG (c0=30)
-- LIMIT 3: correct (>= max matches per c0)
-- ORDER BY + LIMIT 1: WRONG (same as LIMIT 1)
-- Control experiments:
-- 1. EXISTS (not NOT EXISTS) also affected: c0=20,30 get wrong ex=0
-- 2. WHERE-clause EXISTS/NOT EXISTS + LIMIT 1: NOT affected (uses Nested loop
-- inner join, not Left hash join with "Limit table size")
-- 3. Only SELECT-list (projection) EXISTS/NOT EXISTS triggers the bug
-- 4. Only subquery_to_derived=on triggers; other switches do not
-- 5. Silent: no error, no warning (SHOW WARNINGS empty)
-- Relationship to Bug #121017:
-- #121017: LIMIT 1 dropped from SCALAR subquery → error 1242; = has fast-path
-- This bug: LIMIT N → "N unique rows" dedup in EXISTS context → wrong boolean;
-- = has NO fast-path in EXISTS context; LIMIT 2 and ORDER BY+LIMIT 1 also trigger
Tested on MySQL 9.7.1 (Homebrew, macOS arm64). The first SELECT returns
correct results; the second returns wrong boolean values for c0=20 and c0=30
with no error or warning emitted.
