Description:
Materializing a grouped input as a view changes a `SUM(DISTINCT ...)` result from `NULL` to a concrete numeric value while keeping the overall row count unchanged. Logically, the original SQL and the view-based rewritten SQL are equivalent, but they produce different results.
How to repeat:
DROP DATABASE IF EXISTS t1;
CREATE DATABASE t1;
USE t1;
CREATE TABLE t1 (
c1 INT NOT NULL,
c6 CHAR(1) NOT NULL
);
CREATE UNIQUE INDEX idx_t1_c1 ON t1 (c1);
CREATE INDEX idx_t1_c6 ON t1 (c6);
INSERT INTO t1 VALUES (1, 'x');
SELECT SUM(DISTINCT c1) AS col_2,
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,
RANK() OVER (ORDER BY c6 DESC) AS col_3
FROM V_t1_06;
Original result:
+-------+-------+
| col_2 | col_3 |
+-------+-------+
| NULL | 1 |
+-------+-------+
After create view, result:
+-------+-------+
| col_2 | col_3 |
+-------+-------+
| 1 | 1 |
+-------+-------+
Description: Materializing a grouped input as a view changes a `SUM(DISTINCT ...)` result from `NULL` to a concrete numeric value while keeping the overall row count unchanged. Logically, the original SQL and the view-based rewritten SQL are equivalent, but they produce different results. How to repeat: DROP DATABASE IF EXISTS t1; CREATE DATABASE t1; USE t1; CREATE TABLE t1 ( c1 INT NOT NULL, c6 CHAR(1) NOT NULL ); CREATE UNIQUE INDEX idx_t1_c1 ON t1 (c1); CREATE INDEX idx_t1_c6 ON t1 (c6); INSERT INTO t1 VALUES (1, 'x'); SELECT SUM(DISTINCT c1) AS col_2, 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, RANK() OVER (ORDER BY c6 DESC) AS col_3 FROM V_t1_06; Original result: +-------+-------+ | col_2 | col_3 | +-------+-------+ | NULL | 1 | +-------+-------+ After create view, result: +-------+-------+ | col_2 | col_3 | +-------+-------+ | 1 | 1 | +-------+-------+