MySQL Bugs: #121019: subquery_to_derived + semijoin=off produces wrong WHERE-clause EXISTS results with LIMIT (dual boundary interaction, sil

Bug #121019	subquery_to_derived + semijoin=off produces wrong WHERE-clause EXISTS results with LIMIT (dual boundary interaction, sil
Submitted:	28 Jul 6:04	Modified:	10 Aug 5:53
Reporter:	Jacob Ding	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.46, 8.4.10, 9.7.1	OS:	MacOS
Assigned to:		CPU Architecture:	ARM

Description:
```sql
CREATE TABLE t0 (id INT PRIMARY KEY, c0 INT, c1 DECIMAL(20,4), c2 VARCHAR(50));
CREATE TABLE t1 (id INT PRIMARY KEY, c0 INT, c1 DECIMAL(20,4), c2 VARCHAR(50));
INSERT INTO t0 VALUES (1,NULL,'0.0000',' 1'),(2,0,'0.0001',' 1'),(3,1,'0.0000','100'),(4,-1,NULL,'1e10'),(5,0,'0.0000','0'),(6,2147483647,'0.0000','100'),(7,-2147483648,'0.0001','abc'),(8,0,'0.0000','0');
INSERT INTO t1 VALUES (1,NULL,'9999999999999999.9999',' 1'),(2,0,'0.0000','1e10'),(3,1,'0.0000','0'),(4,-1,'-0.0001','-1.5'),(5,-2147483648,NULL,'abc'),(6,0,'-9999999999999999.9999',NULL),(7,0,'0.0001','1e10'),(8,1,NULL,'1e10');

-- Plan A (default — correct): 6 rows
SET SESSION optimizer_switch = 'default';
SELECT t0.c0 FROM t0 WHERE EXISTS(SELECT 1 FROM t1 WHERE t1.c0 = t0.c0 LIMIT 1);

-- Plan B (s2d=on alone — correct): 6 rows
SET SESSION optimizer_switch = 'subquery_to_derived=on';
SELECT t0.c0 FROM t0 WHERE EXISTS(SELECT 1 FROM t1 WHERE t1.c0 = t0.c0 LIMIT 1);

-- Plan C (s2d=on + semijoin=off — BUG!): 0 rows
SET SESSION optimizer_switch = 'subquery_to_derived=on,semijoin=off';
SELECT t0.c0 FROM t0 WHERE EXISTS(SELECT 1 FROM t1 WHERE t1.c0 = t0.c0 LIMIT 1);
-- WRONG: returns 0 rows instead of 6. Silent corruption.
```

When `subquery_to_derived=on` is combined with `semijoin=off`, a correlated
`EXISTS`/`NOT EXISTS` subquery in the **WHERE clause** that contains a `LIMIT`
clause is rewritten as an `Inner hash join` against a materialized derived
table. The materialization uses `Limit table size: N unique row(s)` +
`Temporary table with deduplication`, which retains only N unique values of
the correlation column. The Inner hash join then only matches outer rows
whose correlation value is among the N retained values, dropping all other
rows.

This is a **silent corruption** — no error is raised, no warning is emitted.

How to repeat:
Tested on MySQL 9.7.1 (Homebrew, macOS arm64). Execute the test case above;
Plan A and Plan B return 6 rows correctly, Plan C returns 0 rows with no
error or warning.

Correction to the expected row counts in the original report:

With the exact INSERT data provided above, the correct row counts are:

- Plan A (default): 5 rows (not 6)
- Plan B (s2d=on alone): 5 rows (not 6)
- Plan C (s2d=on + semijoin=off): 0 rows (correct — the bug)

The LIMIT value sweep should be:

| LIMIT | default | s2d=on | s2d=on + semijoin=off | Correct |
|---|---|---|---|---|
| No LIMIT | 5 | 5 | 5 | 5 |
| LIMIT 1 | 5 | 5 | 0 | 5 |
| LIMIT 2 | 5 | 5 | 3 | 5 |
| LIMIT 3 | 5 | 5 | 4 | 5 |

NOT EXISTS + LIMIT 1: default returns 3 rows, s2d=on+semijoin=off returns 8 (should be 3).

The bug is fully reproducible with the provided test data — only the annotated 
row counts in the comments were slightly off. The core issue (0 rows instead of 
5 when s2d=on + semijoin=off) is confirmed.

Hi Jacob Ding,

Thank you for test case. Verified as described.

For the record: I believe this is a duplicate of #121019

121018 actually.

Hi Georgi,

Thank you for looking at this. I agree the two reports share the same defective materialization: in both plans the derived table is built with "Temporary table with deduplication" under "Limit table size: N unique row(s)", i.e. the LIMIT — semantically a no-op inside EXISTS — is applied to the deduplicated correlation values. If a single fix in the subquery_to_derived decorrelation step covers both paths, tracking them as one bug is fine with me.

I would however like to note that the two test cases exercise disjoint trigger surfaces, so the #121018 test case alone will not cover this report:

Position and gating: #121018 fires in the SELECT list with subquery_to_derived=on alone, and its control experiments explicitly found the WHERE clause NOT affected under that setting (re-verified today on 9.7.1: with semijoin at default, the WHERE-clause variant returns correct results). This report fires only in the WHERE clause, and only with subquery_to_derived=on AND semijoin=off.
Plan shape and symptom: #121018 produces a Left hash join (NULL-padded rows, wrong boolean in the projection); this report produces an Inner hash join (outer rows silently dropped for EXISTS; for NOT EXISTS, phantom rows are returned).
Whether this is a strict duplicate therefore depends on where the fix lands: a fix in the shared decorrelation step fixes both, but a fix scoped to the lateral/SELECT-list path would leave this scenario broken. Could you please ensure the WHERE + semijoin=off case (both EXISTS and NOT EXISTS variants) is added to the regression tests of whichever report is kept, and link the two bugs?

A minimal version on the same simple schema as #121018 also reproduces, which may be convenient for the test suite:

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);
SET SESSION optimizer_switch = 'subquery_to_derived=on,semijoin=off';
SELECT t0.c0 FROM t0 WHERE EXISTS(SELECT 1 FROM t1 WHERE t1.c0 = t0.c0 LIMIT 1);
-- returns 1 row; correct is 3 rows (10, 20, 30)
One correction to my comment of 28 Jul 6:16: re-running the exact test case from the original description on a clean 9.7.1 instance, the correct row count is 6 — as stated in the original report — not 5 (rows with c0 in {0, 1, −1, −2147483648} all have matches in t1), and NOT EXISTS + LIMIT 1 correctly returns 2 rows, not 3. The wrong results under subquery_to_derived=on,semijoin=off are unchanged: 0 / 3 / 4 rows for LIMIT 1/2/3 (should be 6), and 8 rows for NOT EXISTS + LIMIT 1 (should be 2).