Bug #121017 subquery_to_derived drops LIMIT 1 from non-equality scalar subqueries

• Submitted: 28 Jul 5:06
• Modified: 28 Jul 5:42
• Reporter: Jacob Ding
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 9.7.1
• OS: MacOS
• CPU Architecture: ARM
• Tags: logical-bug

[28 Jul 5:06] Jacob Ding

Description:
```sql
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, NULL);
INSERT INTO t1 VALUES (1, NULL, 100), (2, NULL, 200), (3, 10, 300);

SET SESSION optimizer_switch = 'default';
SELECT t0.c0, (SELECT t1.c1 FROM t1 WHERE t1.c0 <=> t0.c0 LIMIT 1) AS s
FROM t0 GROUP BY t0.c0 WITH ROLLUP;
-- (NULL,100), (10,300), (20,NULL), (NULL,100)
-- Correct: LIMIT 1 is honored, no error

SET SESSION optimizer_switch = 'subquery_to_derived=on';
SELECT t0.c0, (SELECT t1.c1 FROM t1 WHERE t1.c0 <=> t0.c0 LIMIT 1) AS s
FROM t0 GROUP BY t0.c0 WITH ROLLUP;
-- ERROR 1242 (21000): Subquery returns more than 1 row
```

When `subquery_to_derived=on`, the correlated scalar subquery using the
`<=>` (NULL-safe equal) operator with `LIMIT 1` is rewritten as a LATERAL
derived table join. During this rewrite, the `LIMIT 1` clause is dropped
from the LATERAL derived table. Since `<=>` treats `NULL <=> NULL` as TRUE
(unlike `=` which treats `NULL = NULL` as NULL/UNKNOWN), the LATERAL join
produces multiple rows per outer row, triggering error 1242.

The bug does NOT require ROLLUP or NULL values. A minimal reproduction
without ROLLUP:
```sql
SET SESSION optimizer_switch = 'subquery_to_derived=on';
SELECT t0.c0, (SELECT t1.c1 FROM t1 WHERE t1.c0 <=> t0.c0 LIMIT 1) AS s
FROM t0;
-- ERROR 1242: Subquery returns more than 1 row
```
(This triggers whenever t1 has multiple rows matching the outer value
via `<=>`, which is common when NULL values are present.)

Control experiments confirming the precise trigger conditions:
- Using `=` instead of `<=>`: **no bug** (`=` does not match NULL rows)
- Using `LIMIT 2` instead of `LIMIT 1`: **no bug** (only `LIMIT 1` is dropped)
- Using `ORDER BY ... LIMIT 1`: **no bug** (ORDER BY changes the rewrite path)
- Using `IS NOT DISTINCT FROM` (SQL standard equivalent of `<=>`): **no bug**
  (MySQL handles this via a different code path)
- Only `subquery_to_derived=on` triggers the bug; `semijoin=off`,
  `materialization=off`, `derived_condition_pushdown=on` do NOT trigger it.

The bug manifests in multiple query contexts (SELECT list, WHERE, HAVING,
ORDER BY, CASE, COALESCE, DISTINCT, derived tables, multi-table JOINs),
indicating a systemic issue in the LATERAL rewrite pass rather than a
context-specific edge case.

How to repeat:
Tested on MySQL 9.7.1 (Homebrew, macOS arm64). Execute the above test
case; the first SELECT returns 4 rows correctly, the second raises
error 1242. An LLM-driven expansion test (25 generated queries using
the `<=>` + `LIMIT 1` pattern) found 12 distinct bug instances (48%
hit rate), confirming the bug's broad impact.

[28 Jul 5:11] Jacob Ding

When `subquery_to_derived=on`, a correlated scalar subquery with `LIMIT 1`
in its WHERE clause is rewritten as a LATERAL derived table join. During
this rewrite, the `LIMIT 1` clause is **dropped** from the LATERAL derived
table — but **only when the WHERE condition uses a non-equality operator**.

The `=` (equality) operator is the **sole exception**: for `=` conditions,
the LATERAL rewrite correctly preserves `LIMIT 1`. All other comparison
operators lose the `LIMIT 1` constraint, causing the LATERAL join to
produce multiple rows per outer row when more than one row matches,
triggering error 1242 "Subquery returns more than 1 row".

### Operator-specific test results

All tested with identical data (t0.c0=20 matching 3 rows in t1 via the
operator), `LIMIT 1`, and `subquery_to_derived=on`:

| Operator | Multiple match? | LIMIT 1 preserved? | Bug? |
|---|---|---|---|
| `=` | Yes (3 rows) | **Yes** | No |
| `<=>` (NULL-safe eq) | Yes | No | **Yes (1242)** |
| `!=` / `<>` | Yes | No | **Yes (1242)** |
| `<` | Yes | No | **Yes (1242)** |
| `<=` | Yes | No | **Yes (1242)** |
| `>=` | Yes | No | **Yes (1242)** |
| `LIKE` | Yes | No | **Yes (1242)** |
| `BETWEEN` | Yes | No | **Yes (1242)** |
| `IN` | Yes | Yes | No (different path) |
| `>` | Yes | No | **Yes (1242)** |

**Conclusion**: `=` is special-cased in the LATERAL rewrite to preserve
`LIMIT 1`. All other operators that can produce multiple matching rows
trigger the bug. This is likely because `=` is the most common correlated
subquery join condition and has a dedicated fast-path in the rewrite code,
while other operators fall through to a generic path that fails to
propagate the `LIMIT` clause.

### Additional trigger conditions

- The bug does NOT require ROLLUP, NULL values, or window functions
- The bug does NOT require NULL-safe equal (`<=>`) specifically — any
  non-`=` operator with multiple matches triggers it
- Using `LIMIT 2` instead of `LIMIT 1`: **no bug** (only `LIMIT 1` is dropped)
- Using `ORDER BY ... LIMIT 1`: **no bug** (ORDER BY changes the rewrite path)
- Using `IS NOT DISTINCT FROM` (SQL standard equivalent of `<=>`): **no bug**
  (MySQL handles this via a different code path — syntactically rejected on
  some versions)
- Only `subquery_to_derived=on` triggers the bug; `semijoin=off`,
  `materialization=off`, `derived_condition_pushdown=on` do NOT trigger it

### Query context generality

The bug manifests in all tested query contexts: SELECT list, WHERE, HAVING,
ORDER BY, CASE expressions, COALESCE, DISTINCT, derived tables, multi-table
JOINs — indicating a systemic issue in the LATERAL rewrite pass rather than
a context-specific edge case.

[28 Jul 5:19] Jacob Ding

Change the synopsis..

[28 Jul 5:42] Jacob Ding

change the category to optimizer...