Description:
-- Root cause: When a correlated scalar subquery contains GROUP BY with multiple
-- columns, decorrelate_derived_scalar_subquery_pre (D31) adds a windowed COUNT
-- cardinality check. But this check counts rows PER GROUP BY GROUP, not PER
-- CORRELATION VALUE. With multi-column GROUP BY, each group has 1 row (COUNT=1,
-- passes check), but each correlation value may match multiple groups → multiple
-- rows returned → scalar subquery constraint violated silently.
--
-- source: sql_resolver.cc:8086-8097 (setup_counts_over_partitions) +
-- sql_resolver.cc:8257-8261 (build_reject_if)
How to repeat:
CREATE TABLE t0 (id INT PRIMARY KEY, c0 INT, c1 INT);
CREATE TABLE t1 (id INT PRIMARY KEY, c0 INT, c1 INT);
INSERT INTO t0 VALUES (1, 10, 100), (2, 20, 200), (3, 30, 300);
INSERT INTO t1 VALUES (1, 10, 1000), (2, 10, 3000), (3, 20, 2000), (4, 20, 4000), (5, 30, 5000);
-- Plan A (default — correct): ERROR 1242
SET SESSION optimizer_switch = 'default';
SELECT t0.c0,
(SELECT SUM(t1.c1) FROM t1 WHERE t1.c0 = t0.c0 GROUP BY t1.c0, t1.c1) AS sq
FROM t0;
-- ERROR 1242: Subquery returns more than 1 row ← CORRECT: scalar subquery violated
-- Plan B (s2d=on — BUG): returns 8 rows silently, no error
SET SESSION optimizer_switch = 'subquery_to_derived=on';
SELECT t0.c0,
(SELECT SUM(t1.c1) FROM t1 WHERE t1.c0 = t0.c0 GROUP BY t1.c0, t1.c1) AS sq
FROM t0;
-- (10, 1000), (10, 3000), (20, 2000), (20, 4000), (30, 5000), (NULL, NULL), (10, 1000), (10, 3000)
-- WRONG: 8 rows returned, no error. Should error with 1242.
--
-- EXPLAIN shows reject_if is present but checks per-group, not per-correlation-value:
-- Filter: reject_if((derived_1_2.Name_exp_5 > 1))
-- The derived table groups by (t1.c0, t1.c1), so each group has COUNT=1 → passes check.
-- But c0=10 matches 2 groups → 2 rows → scalar subquery returns 2 rows → should be 1242.
Description: -- Root cause: When a correlated scalar subquery contains GROUP BY with multiple -- columns, decorrelate_derived_scalar_subquery_pre (D31) adds a windowed COUNT -- cardinality check. But this check counts rows PER GROUP BY GROUP, not PER -- CORRELATION VALUE. With multi-column GROUP BY, each group has 1 row (COUNT=1, -- passes check), but each correlation value may match multiple groups → multiple -- rows returned → scalar subquery constraint violated silently. -- -- source: sql_resolver.cc:8086-8097 (setup_counts_over_partitions) + -- sql_resolver.cc:8257-8261 (build_reject_if) How to repeat: CREATE TABLE t0 (id INT PRIMARY KEY, c0 INT, c1 INT); CREATE TABLE t1 (id INT PRIMARY KEY, c0 INT, c1 INT); INSERT INTO t0 VALUES (1, 10, 100), (2, 20, 200), (3, 30, 300); INSERT INTO t1 VALUES (1, 10, 1000), (2, 10, 3000), (3, 20, 2000), (4, 20, 4000), (5, 30, 5000); -- Plan A (default — correct): ERROR 1242 SET SESSION optimizer_switch = 'default'; SELECT t0.c0, (SELECT SUM(t1.c1) FROM t1 WHERE t1.c0 = t0.c0 GROUP BY t1.c0, t1.c1) AS sq FROM t0; -- ERROR 1242: Subquery returns more than 1 row ← CORRECT: scalar subquery violated -- Plan B (s2d=on — BUG): returns 8 rows silently, no error SET SESSION optimizer_switch = 'subquery_to_derived=on'; SELECT t0.c0, (SELECT SUM(t1.c1) FROM t1 WHERE t1.c0 = t0.c0 GROUP BY t1.c0, t1.c1) AS sq FROM t0; -- (10, 1000), (10, 3000), (20, 2000), (20, 4000), (30, 5000), (NULL, NULL), (10, 1000), (10, 3000) -- WRONG: 8 rows returned, no error. Should error with 1242. -- -- EXPLAIN shows reject_if is present but checks per-group, not per-correlation-value: -- Filter: reject_if((derived_1_2.Name_exp_5 > 1)) -- The derived table groups by (t1.c0, t1.c1), so each group has COUNT=1 → passes check. -- But c0=10 matches 2 groups → 2 rows → scalar subquery returns 2 rows → should be 1242.