MySQL Bugs: #121020: subquery_to_derived transforms LIMIT 1 into MIN aggregate when subquery is inside derived table (= fast-path context fai

Bug #121020	subquery_to_derived transforms LIMIT 1 into MIN aggregate when subquery is inside derived table (= fast-path context fai
Submitted:	28 Jul 6:11	Modified:	29 Jul 9:47
Reporter:	Jacob Ding	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	9.7.1	OS:	MacOS
Assigned to:		CPU Architecture:	ARM
Tags:	logical-bug

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));
-- Key data: t1 has multiple rows per c0 value, including NULL c1 values
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,-1,0.0000,'abc'),(6,0,'-9999999999999999.9999',NULL),(7,0,'0.0001','1e10'),(8,1,NULL,'1e10');

-- Plan A (default — correct): 4 rows
SET SESSION optimizer_switch = 'default';
SELECT x.c0, x.s FROM (
  SELECT t0.c0, (SELECT t1.c1 FROM t1 WHERE t1.c0 = t0.c0 LIMIT 1) AS s FROM t0
) x WHERE x.s IS NOT NULL ORDER BY x.c0;
-- Returns 4 rows: (0, -0.0001), (0, -0.0001), (1, 0.0000), (2147483647, 9999999999999999.9999)

-- Plan B (s2d=on — BUG!): 5 rows with wrong values
SET SESSION optimizer_switch = 'subquery_to_derived=on';
SELECT x.c0, x.s FROM (
  SELECT t0.c0, (SELECT t1.c1 FROM t1 WHERE t1.c0 = t0.c0 LIMIT 1) AS s FROM t0
) x WHERE x.s IS NOT NULL ORDER BY x.c0;
-- Returns 5 rows (WRONG!):
--   (-1, 0.0000)               ← EXTRA ROW (LIMIT 1 returns NULL, MIN skips NULL)
--   (0, -0.0001)               ← correct (coincidentally MIN = first row)
--   (0, -0.0001)
--   (1, 0.0000)                ← correct (coincidentally MIN = first row)
--   (2147483647, 0.0000)       ← WRONG VALUE (should be 9999999999999999.9999)
```

When `subquery_to_derived=on` rewrites a correlated scalar subquery with
`LIMIT 1` that is wrapped inside a derived table (subquery in the SELECT
list of a derived table), the `=` operator's fast-path that normally
preserves `LIMIT 1` **does NOT fire**. Instead, the rewrite transforms
`LIMIT 1` into an `Aggregate using temporary table` (effectively `MIN`),
which:

1. **Returns a different value**: `MIN(t1.c1)` instead of the first-row
   value (`LIMIT 1`). When the data is unordered, these differ.
2. **Returns non-NULL for rows where LIMIT 1 would return NULL**: `MIN`
   skips NULL values, so a row whose first-match `c1` is NULL gets a
   non-NULL `MIN(c1)` instead, passing the `IS NOT NULL` filter
   incorrectly.

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 returns 4 rows correctly, Plan B returns 5 rows with wrong values
for c0=-1 (extra row) and c0=2147483647 (wrong value), with no error or
warning.
**Plan A (default — correct)**:
```
-> Filter: (x.s is not null)
    -> Table scan on x
        -> Materialize
            -> Table scan on t0
            -> Select #3 (subquery in projection; dependent)
                -> Limit: 1 row(s)           ← LIMIT 1 preserved
                    -> Filter: (t1.c0 = t0.c0)
                        -> Table scan on t1
```

**Plan B (s2d=on — buggy)**:
```
-> Nested loop inner join
    -> Table scan on t0
    -> Index lookup on derived_2_3 using <auto_key0> (c0 = t0.c0)
        -> Materialize
            -> Filter: (min(t1.c1) is not null)   ← MIN aggregate!
                -> Table scan on <temporary>
                    -> Aggregate using temporary table   ← LIMIT 1 → MIN
                        -> Table scan on t1
```

Note: `Limit: 1 row(s)` is GONE. Replaced by `Aggregate using temporary
table`. The subquery is effectively rewritten as:
`SELECT MIN(t1.c1) FROM t1 WHERE t1.c0 = t0.c0`
which is semantically different from:
`SELECT t1.c1 FROM t1 WHERE t1.c0 = t0.c0 LIMIT 1`

Correction and important update to the original report:

1. Row count correction: With the exact INSERT data provided, both Plan A and 
   Plan B return 5 rows (not 4 and 5 as stated). The bug manifests as WRONG 
   VALUES, not extra rows, with this dataset:

   Plan A (default — correct):
     (-1, -0.0001), (0, 0.0000), (0, 0.0000), (0, 0.0000), (1, 0.0000)

   Plan B (s2d=on — wrong values):
     (-1, -0.0001), (0, -9999999999999999.9999), (0, -9999999999999999.9999), 
     (0, -9999999999999999.9999), (1, 0.0000)

   The value for c0=0 changes from 0.0000 (first match by id) to 
   -9999999999999999.9999 (MIN aggregate).

2. Important: the bug also affects DIRECT scalar subqueries (not only 
   derived-table-wrapped subqueries). The original report stated that the 
   = fast-path works in direct context, but this is incorrect — it was a 
   coincidence of the test data where MIN happened to equal the first-row value.

   Minimal reproduction showing both contexts are affected:

   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, 100);
   INSERT INTO t1 VALUES (1, 100, 50), (2, 100, 10), (3, 100, 999);
   -- LIMIT 1 should return 50 (first by id); MIN returns 10

   SET SESSION optimizer_switch = 'default';
   SELECT t0.c0, (SELECT t1.c1 FROM t1 WHERE t1.c0 = t0.c0 LIMIT 1) AS s FROM t0;
   -- Returns: (100, 50) — correct

   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;
   -- Returns: (100, 10) — WRONG (MIN instead of LIMIT 1)

   EXPLAIN with s2d=on shows "Aggregate using temporary table" instead of 
   "Limit: 1 row(s)" — the LIMIT 1 is transformed into MIN aggregate in both 
   direct and derived-table contexts.

The core bug (LIMIT 1 transformed to MIN aggregate by subquery_to_derived) is 
fully confirmed. The scope is broader than originally stated: it affects = 
operator in ALL scalar subquery contexts, not just derived-table-wrapped ones.

Hi Jacob Ding,

Thank you for the test case. This is not a bug. The expectation that the subquery needs to always return "50" is not right. Since there is no ORDER BY for the LIMIT 1 query, MySQL is free to return any one row that matches the condition which could be either 50, 10 or 999. 

Thank you,
Chaithra

Re: the "Not a Bug" resolution. The non-determinism argument does not apply when ORDER BY makes LIMIT 1 deterministic. With ORDER BY ... LIMIT 1, the result is fully determined, yet subquery_to_derived=on still transforms it to MIN aggregate, producing a different result due to NULL handling.
MIN skips NULL values; LIMIT 1 does not. When the first row (per ORDER BY) has a NULL value, LIMIT 1 returns NULL, but MIN returns the smallest non-NULL value. This is a deterministic semantic difference, not non-determinism.
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, 100);
-- c1=NULL sorts first in ASC order; MIN skips NULL → returns 10
INSERT INTO t1 VALUES (1, 100, NULL), (2, 100, 10), (3, 100, 999);

SET SESSION optimizer_switch = 'default';
SELECT t0.c0, (SELECT t1.c1 FROM t1 WHERE t1.c0 = t0.c0 ORDER BY t1.c1 LIMIT 1) AS s FROM t0;
-- (100, NULL)  ← ORDER BY c1 ASC → NULL is first → LIMIT 1 returns NULL

SET SESSION optimizer_switch = 'subquery_to_derived=on';
SELECT t0.c0, (SELECT t1.c1 FROM t1 WHERE t1.c0 = t0.c0 ORDER BY t1.c1 LIMIT 1) AS s FROM t0;
-- (100, 10)   ← MIN skips NULL, returns 10
EXPLAIN with subquery_to_derived=on shows Aggregate using temporary table instead of Limit: 1 row(s) — the deterministic ORDER BY ... LIMIT 1 is replaced by MIN, which has different NULL semantics.
Tested on MySQL 9.7.1. No error, no warning.

Thank you for the clarification. With ORDER BY added, this revised query is deterministic.