Description:
MySQL 9.6 returns different results for two logically equivalent grouped/window queries when the grouped aggregate is extracted into a view.
The issue appears in a window-function workload that combines SUM(DISTINCT ...), GROUP BY, and DENSE_RANK() OVER (...). In the reproduced case, the direct grouped/window query returns NULL for the aggregate column, while the logically equivalent view-based form returns 4.
Because the view definition is just the grouped aggregate projected with its grouping column, both queries should return the same col_2 value.
How to repeat:
DROP DATABASE IF EXISTS t1;
CREATE DATABASE t1;
USE t1;
CREATE TABLE t1 (
c1 INT NOT NULL,
c6 CHAR(2) NOT NULL
);
CREATE UNIQUE INDEX idx_t1_c1 ON t1 (c1);
CREATE INDEX idx_t1_c6 ON t1 (c6);
INSERT INTO t1 VALUES (4, 'z!');
SELECT SUM(DISTINCT c1) AS col_2,
DENSE_RANK() OVER (ORDER BY c6 DESC) AS col_3
FROM t1
GROUP BY c6;
CREATE VIEW V_t1_06 AS
SELECT SUM(DISTINCT c1) AS col_2,
c6
FROM t1
GROUP BY c6;
SELECT col_2,
DENSE_RANK() OVER (ORDER BY c6 DESC) AS col_3
FROM V_t1_06;
Expected result:
+-------+-------+
| col_2 | col_3 |
+-------+-------+
| 4 | 1 |
+-------+-------+
Actual result:
-- original query
+-------+-------+
| col_2 | col_3 |
+-------+-------+
| NULL | 1 |
+-------+-------+
-- transformed query
+-------+-------+
| col_2 | col_3 |
+-------+-------+
| 4 | 1 |
+-------+-------+
Description: MySQL 9.6 returns different results for two logically equivalent grouped/window queries when the grouped aggregate is extracted into a view. The issue appears in a window-function workload that combines SUM(DISTINCT ...), GROUP BY, and DENSE_RANK() OVER (...). In the reproduced case, the direct grouped/window query returns NULL for the aggregate column, while the logically equivalent view-based form returns 4. Because the view definition is just the grouped aggregate projected with its grouping column, both queries should return the same col_2 value. How to repeat: DROP DATABASE IF EXISTS t1; CREATE DATABASE t1; USE t1; CREATE TABLE t1 ( c1 INT NOT NULL, c6 CHAR(2) NOT NULL ); CREATE UNIQUE INDEX idx_t1_c1 ON t1 (c1); CREATE INDEX idx_t1_c6 ON t1 (c6); INSERT INTO t1 VALUES (4, 'z!'); SELECT SUM(DISTINCT c1) AS col_2, DENSE_RANK() OVER (ORDER BY c6 DESC) AS col_3 FROM t1 GROUP BY c6; CREATE VIEW V_t1_06 AS SELECT SUM(DISTINCT c1) AS col_2, c6 FROM t1 GROUP BY c6; SELECT col_2, DENSE_RANK() OVER (ORDER BY c6 DESC) AS col_3 FROM V_t1_06; Expected result: +-------+-------+ | col_2 | col_3 | +-------+-------+ | 4 | 1 | +-------+-------+ Actual result: -- original query +-------+-------+ | col_2 | col_3 | +-------+-------+ | NULL | 1 | +-------+-------+ -- transformed query +-------+-------+ | col_2 | col_3 | +-------+-------+ | 4 | 1 | +-------+-------+