Description:
Materializing a grouped CTE or derived table as a view changes the outer grouped result. The outer query logic remains the same, and the outer `GROUP BY` key order is preserved across the original and transformed forms. The only structural change is that the grouped intermediate result is exposed through a view before the outer grouped query consumes it. 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 (
c2 CHAR(1) NOT NULL,
c3 VARCHAR(259) NULL,
c6 CHAR(1) NOT NULL
);
INSERT INTO t1 VALUES
('a', REPEAT('a', 259), 'x'),
('b', REPEAT('b', 62), 'x');
WITH cte_491 AS (
SELECT COUNT(DISTINCT hax36.c2) AS col_1,
CONCAT(hax36.c6, hax36.c3) AS col_2
FROM t1 AS hax36
GROUP BY CONCAT(hax36.c6, hax36.c3)
)
SELECT DISTINCT cte.col_1 AS col_1,
LENGTH(cte.col_2) AS col_1_3
FROM cte_491 AS cte
WHERE (cte.col_1 < 18)
GROUP BY cte.col_1,
LENGTH(cte.col_2)
ORDER BY cte.col_1 ASC;
CREATE VIEW V_cte_164ef008 AS
SELECT *
FROM (
SELECT COUNT(DISTINCT hax36.c2) AS col_1,
CONCAT(hax36.c6, hax36.c3) AS col_2
FROM t1 AS hax36
GROUP BY CONCAT(hax36.c6, hax36.c3)
) AS cte
WHERE (cte.col_1 < 18);
SELECT DISTINCT V_cte_164ef008.col_1 AS col_1,
LENGTH(V_cte_164ef008.col_2) AS col_1_3
FROM V_cte_164ef008
GROUP BY V_cte_164ef008.col_1,
LENGTH(V_cte_164ef008.col_2)
ORDER BY V_cte_164ef008.col_1;
Original result:
+-------+---------+
| col_1 | col_1_3 |
+-------+---------+
| 1 | 63 |
| 1 | 260 |
+-------+---------+
After create view, result:
+-------+---------+
| col_1 | col_1_3 |
+-------+---------+
| 1 | 260 |
+-------+---------+
Description: Materializing a grouped CTE or derived table as a view changes the outer grouped result. The outer query logic remains the same, and the outer `GROUP BY` key order is preserved across the original and transformed forms. The only structural change is that the grouped intermediate result is exposed through a view before the outer grouped query consumes it. 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 ( c2 CHAR(1) NOT NULL, c3 VARCHAR(259) NULL, c6 CHAR(1) NOT NULL ); INSERT INTO t1 VALUES ('a', REPEAT('a', 259), 'x'), ('b', REPEAT('b', 62), 'x'); WITH cte_491 AS ( SELECT COUNT(DISTINCT hax36.c2) AS col_1, CONCAT(hax36.c6, hax36.c3) AS col_2 FROM t1 AS hax36 GROUP BY CONCAT(hax36.c6, hax36.c3) ) SELECT DISTINCT cte.col_1 AS col_1, LENGTH(cte.col_2) AS col_1_3 FROM cte_491 AS cte WHERE (cte.col_1 < 18) GROUP BY cte.col_1, LENGTH(cte.col_2) ORDER BY cte.col_1 ASC; CREATE VIEW V_cte_164ef008 AS SELECT * FROM ( SELECT COUNT(DISTINCT hax36.c2) AS col_1, CONCAT(hax36.c6, hax36.c3) AS col_2 FROM t1 AS hax36 GROUP BY CONCAT(hax36.c6, hax36.c3) ) AS cte WHERE (cte.col_1 < 18); SELECT DISTINCT V_cte_164ef008.col_1 AS col_1, LENGTH(V_cte_164ef008.col_2) AS col_1_3 FROM V_cte_164ef008 GROUP BY V_cte_164ef008.col_1, LENGTH(V_cte_164ef008.col_2) ORDER BY V_cte_164ef008.col_1; Original result: +-------+---------+ | col_1 | col_1_3 | +-------+---------+ | 1 | 63 | | 1 | 260 | +-------+---------+ After create view, result: +-------+---------+ | col_1 | col_1_3 | +-------+---------+ | 1 | 260 | +-------+---------+