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:	29 Jul 6:16
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.