Bug #121506 Wrong result: with subquery_to_derived=on, a correlated scalar COUNT(*) over a derived table is not grouped when the cor

• Submitted: 14 Oct 13:04
• Reporter: Ann Calla
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 26.7.1
• OS: Ubuntu (24.04)
• CPU Architecture: x86 (x86_64)

[14 Oct 13:04] Ann Calla

Description:
With optimizer_switch='subquery_to_derived=on', a correlated scalar subquery that counts the rows of a derived table returns wrong counts when the column used in the correlation is an expression of the derived table (here "r.c0 + 0 AS k").

In the script, t0 has the rows 0, 1, 2 and t1 has the rows 0, 1, 5. The count of matching t1 rows is 1, 1 and 0. With the switch on, the query returns 3, 0 and 0: the first outer row gets the count of the whole table and the others get 0.

The result is correct with the switch off (the default). With the switch on it is also correct when the derived table selects the plain column ("r.c0 AS k"), and when the same expression is written without the derived table ("WHERE r.c0 + 0 = l.c0").

EXPLAIN FORMAT=TREE shows the reason. For the plain column, the new derived table is grouped ("Aggregate using temporary table") and joined by a lookup on the key. For the expression, the new derived table is "Aggregate: count(0)" over a table scan with no grouping, and the correlation "(r.c0 + 0) = l.c0" is left as an extra condition of the left hash join, outside the aggregation.

Reproduced on 26.7.1 and 9.7.2.

How to repeat:
CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 INT);
INSERT INTO t0 VALUES (0),(1),(2);
INSERT INTO t1 VALUES (0),(1),(5);

SELECT l.c0, (SELECT COUNT(*) FROM (SELECT r.c0 + 0 AS k FROM t1 r) r WHERE r.k = l.c0) AS n FROM t0 l;
-- (0,1), (1,1), (2,0)   correct (subquery_to_derived is off by default)

SET SESSION optimizer_switch = 'subquery_to_derived=on';

SELECT l.c0, (SELECT COUNT(*) FROM (SELECT r.c0 + 0 AS k FROM t1 r) r WHERE r.k = l.c0) AS n FROM t0 l;
-- expected: (0,1), (1,1), (2,0)
-- actual:   (0,3), (1,0), (2,0)

SELECT l.c0, (SELECT COUNT(*) FROM (SELECT r.c0 AS k FROM t1 r) r WHERE r.k = l.c0) AS n FROM t0 l;
-- (0,1), (1,1), (2,0)   correct: plain column instead of r.c0 + 0

SELECT l.c0, (SELECT COUNT(*) FROM t1 r WHERE r.c0 + 0 = l.c0) AS n FROM t0 l;
-- (0,1), (1,1), (2,0)   correct: same expression without the derived table

EXPLAIN FORMAT=TREE
SELECT l.c0, (SELECT COUNT(*) FROM (SELECT r.c0 + 0 AS k FROM t1 r) r WHERE r.k = l.c0) AS n FROM t0 l;
-- -> Left hash join (no condition), extra conditions: ((r.c0 + 0) = l.c0)
--     -> Table scan on l
--     -> Hash
--         -> Table scan on derived_1_2
--             -> Materialize
--                 -> Aggregate: count(0)
--                     -> Table scan on r

EXPLAIN FORMAT=TREE
SELECT l.c0, (SELECT COUNT(*) FROM (SELECT r.c0 AS k FROM t1 r) r WHERE r.k = l.c0) AS n FROM t0 l;
-- -> Nested loop left join
--     -> Table scan on l
--     -> Index lookup on derived_1_2 using <auto_key0> (k = l.c0)
--         -> Materialize
--             -> Table scan on <temporary>
--                 -> Aggregate using temporary table
--                     -> Table scan on r