Description:
When a query uses both DISTINCTROW and GROUP BY, a direct base‑table scan correctly returns all grouped rows (2 rows in this case).
However, when the query is rewritten as a derived table with a JOIN, the optimizer inserts a Sort with duplicate removal step after GROUP BY, but the duplicate‑removal key includes only a subset of the output columns (ref1 and ref2), omitting ref0, which is both a grouping key and an output column.
Because the two rows have identical ref1 (NULL) and ref2 (1) but different ref0 (0 and 1), the deduplication incorrectly merges them into one row, losing a row.
In the base‑table path, the optimizer recognizes that the grouping key already guarantees uniqueness and does not add an extra deduplication step, so the result is correct.
Actual Results and Status
Query Actual Result Status
Single‑table (FROM src) 2 rows Correct
Derived table (JOIN) 1 row Incorrect (missing (1, NULL, 1))
EXPLAIN Analysis
Single‑table query (correct):
text
-> Group aggregate
-> Sort: ref1, ref0
-> Covering index scan on src using i0
Derived‑table query (incorrect) – key part:
-> Sort: ref1, ...
-> Sort with duplicate removal: ref1, ref2
-> Stream results
-> Group aggregate
-> Nested loop inner join
Query semantics: DISTINCTROW should deduplicate on all SELECT columns (ref0, ref1, ref2). Since the GROUP BY keys are exactly ref0 and ref1, each group already has unique values for these columns, so all groups should be retained.
Base‑table path: The optimizer recognizes that the grouping keys match the SELECT columns and does not add an extra deduplication step.
Derived‑table path: Due to the JOIN and materialization, the optimizer inserts an additional Sort with duplicate removal after grouping, but the constructed deduplication key is incomplete — it uses only a subset of SELECT columns (ref1 and ref2), omitting ref0.
Incorrect merge: Since ref1 and ref2 are identical across the two rows (NULL and 1), the optimizer treats them as duplicates and merges them, losing the row with ref0=1.
How to repeat:
DROP DATABASE IF EXISTS repro18_final;
CREATE DATABASE repro18_final;
USE repro18_final;
CREATE TABLE src (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c0 BIGINT NULL,
UNIQUE KEY i0(c0)
) ENGINE=InnoDB;
INSERT INTO src(c0) VALUES (0),(32767);
CREATE TABLE l (
id BIGINT NOT NULL PRIMARY KEY,
c0 BIGINT NULL,
KEY l_i(id,c0)
) ENGINE=InnoDB;
CREATE TABLE r (
id BIGINT NOT NULL PRIMARY KEY,
c0 BIGINT NULL
) ENGINE=InnoDB;
INSERT INTO l SELECT id,c0 FROM src;
INSERT INTO r SELECT id,c0 FROM src;
-- Single‑table query (correct): returns 2 rows
SELECT DISTINCTROW
'single' AS q,
('x') OR (CAST((src.c0) XOR (src.id) AS SIGNED)) AS ref0,
(((src.id) < (src.id)) >= ((src.c0) NOT IN (3.35077561E8))) IN (NULL) AS ref1,
COUNT(DISTINCT (src.c0) IS NOT TRUE) AS ref2
FROM src
GROUP BY
(((src.id) < (src.id)) >= ((src.c0) NOT IN (3.35077561E8))) IN (NULL),
('x') OR (CAST((src.c0) XOR (src.id) AS SIGNED));
-- Derived‑table query (incorrect): returns only 1 row
SELECT DISTINCTROW
'split' AS q,
('x') OR (CAST((s.c0) XOR (s.id) AS SIGNED)) AS ref0,
(((s.id) < (s.id)) >= ((s.c0) NOT IN (3.35077561E8))) IN (NULL) AS ref1,
COUNT(DISTINCT (s.c0) IS NOT TRUE) AS ref2
FROM (
SELECT id, l.c0 AS c0
FROM l JOIN r USING(id)
) s
GROUP BY
(((s.id) < (s.id)) >= ((s.c0) NOT IN (3.35077561E8))) IN (NULL),
('x') OR (CAST((s.c0) XOR (s.id) AS SIGNED));
Description: When a query uses both DISTINCTROW and GROUP BY, a direct base‑table scan correctly returns all grouped rows (2 rows in this case). However, when the query is rewritten as a derived table with a JOIN, the optimizer inserts a Sort with duplicate removal step after GROUP BY, but the duplicate‑removal key includes only a subset of the output columns (ref1 and ref2), omitting ref0, which is both a grouping key and an output column. Because the two rows have identical ref1 (NULL) and ref2 (1) but different ref0 (0 and 1), the deduplication incorrectly merges them into one row, losing a row. In the base‑table path, the optimizer recognizes that the grouping key already guarantees uniqueness and does not add an extra deduplication step, so the result is correct. Actual Results and Status Query Actual Result Status Single‑table (FROM src) 2 rows Correct Derived table (JOIN) 1 row Incorrect (missing (1, NULL, 1)) EXPLAIN Analysis Single‑table query (correct): text -> Group aggregate -> Sort: ref1, ref0 -> Covering index scan on src using i0 Derived‑table query (incorrect) – key part: -> Sort: ref1, ... -> Sort with duplicate removal: ref1, ref2 -> Stream results -> Group aggregate -> Nested loop inner join Query semantics: DISTINCTROW should deduplicate on all SELECT columns (ref0, ref1, ref2). Since the GROUP BY keys are exactly ref0 and ref1, each group already has unique values for these columns, so all groups should be retained. Base‑table path: The optimizer recognizes that the grouping keys match the SELECT columns and does not add an extra deduplication step. Derived‑table path: Due to the JOIN and materialization, the optimizer inserts an additional Sort with duplicate removal after grouping, but the constructed deduplication key is incomplete — it uses only a subset of SELECT columns (ref1 and ref2), omitting ref0. Incorrect merge: Since ref1 and ref2 are identical across the two rows (NULL and 1), the optimizer treats them as duplicates and merges them, losing the row with ref0=1. How to repeat: DROP DATABASE IF EXISTS repro18_final; CREATE DATABASE repro18_final; USE repro18_final; CREATE TABLE src ( id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, c0 BIGINT NULL, UNIQUE KEY i0(c0) ) ENGINE=InnoDB; INSERT INTO src(c0) VALUES (0),(32767); CREATE TABLE l ( id BIGINT NOT NULL PRIMARY KEY, c0 BIGINT NULL, KEY l_i(id,c0) ) ENGINE=InnoDB; CREATE TABLE r ( id BIGINT NOT NULL PRIMARY KEY, c0 BIGINT NULL ) ENGINE=InnoDB; INSERT INTO l SELECT id,c0 FROM src; INSERT INTO r SELECT id,c0 FROM src; -- Single‑table query (correct): returns 2 rows SELECT DISTINCTROW 'single' AS q, ('x') OR (CAST((src.c0) XOR (src.id) AS SIGNED)) AS ref0, (((src.id) < (src.id)) >= ((src.c0) NOT IN (3.35077561E8))) IN (NULL) AS ref1, COUNT(DISTINCT (src.c0) IS NOT TRUE) AS ref2 FROM src GROUP BY (((src.id) < (src.id)) >= ((src.c0) NOT IN (3.35077561E8))) IN (NULL), ('x') OR (CAST((src.c0) XOR (src.id) AS SIGNED)); -- Derived‑table query (incorrect): returns only 1 row SELECT DISTINCTROW 'split' AS q, ('x') OR (CAST((s.c0) XOR (s.id) AS SIGNED)) AS ref0, (((s.id) < (s.id)) >= ((s.c0) NOT IN (3.35077561E8))) IN (NULL) AS ref1, COUNT(DISTINCT (s.c0) IS NOT TRUE) AS ref2 FROM ( SELECT id, l.c0 AS c0 FROM l JOIN r USING(id) ) s GROUP BY (((s.id) < (s.id)) >= ((s.c0) NOT IN (3.35077561E8))) IN (NULL), ('x') OR (CAST((s.c0) XOR (s.id) AS SIGNED));