Description:
The incorrect statement is the original unhinted query:
SELECT SUM(DISTINCT c1) AS col_1,
ROW_NUMBER() OVER (ORDER BY g DESC) AS col_2
FROM t1
GROUP BY g;
On MySQL `8.4.8`, this statement returns the wrong result `NULL` for `SUM(DISTINCT c1)`. The `FORCE INDEX (PRIMARY)` variant is included only as a control query to show that the SQL semantics do not change when only the access path changes. The unhinted form and the `FORCE INDEX (PRIMARY)` form are logically equivalent and should return the same row, but they do not.
How to repeat:
CREATE TABLE t1 (
c1 INT NOT NULL AUTO_INCREMENT,
g INT NOT NULL,
PRIMARY KEY (c1),
KEY idx_g (g)
);
INSERT INTO t1 (c1, g) VALUES (377, 377);
SELECT SUM(DISTINCT c1) AS col_1,
ROW_NUMBER() OVER (ORDER BY g DESC) AS col_2
FROM t1
GROUP BY g;
SELECT SUM(DISTINCT c1) AS col_1,
ROW_NUMBER() OVER (ORDER BY g DESC) AS col_2
FROM t1 FORCE INDEX (PRIMARY)
GROUP BY g;
Expected result for the original statement:
+-------+-------+
| col_1 | col_2 |
+-------+-------+
| 377 | 1 |
+-------+-------+
Actual result of the original statement:
+-------+-------+
| col_1 | col_2 |
+-------+-------+
| NULL | 1 |
+-------+-------+
Description: The incorrect statement is the original unhinted query: SELECT SUM(DISTINCT c1) AS col_1, ROW_NUMBER() OVER (ORDER BY g DESC) AS col_2 FROM t1 GROUP BY g; On MySQL `8.4.8`, this statement returns the wrong result `NULL` for `SUM(DISTINCT c1)`. The `FORCE INDEX (PRIMARY)` variant is included only as a control query to show that the SQL semantics do not change when only the access path changes. The unhinted form and the `FORCE INDEX (PRIMARY)` form are logically equivalent and should return the same row, but they do not. How to repeat: CREATE TABLE t1 ( c1 INT NOT NULL AUTO_INCREMENT, g INT NOT NULL, PRIMARY KEY (c1), KEY idx_g (g) ); INSERT INTO t1 (c1, g) VALUES (377, 377); SELECT SUM(DISTINCT c1) AS col_1, ROW_NUMBER() OVER (ORDER BY g DESC) AS col_2 FROM t1 GROUP BY g; SELECT SUM(DISTINCT c1) AS col_1, ROW_NUMBER() OVER (ORDER BY g DESC) AS col_2 FROM t1 FORCE INDEX (PRIMARY) GROUP BY g; Expected result for the original statement: +-------+-------+ | col_1 | col_2 | +-------+-------+ | 377 | 1 | +-------+-------+ Actual result of the original statement: +-------+-------+ | col_1 | col_2 | +-------+-------+ | NULL | 1 | +-------+-------+