Description:
There is a query result inconsistency when using a VIEW versus a CTE (Common Table Expression). Both contain the exact same logic (an inner GROUP BY), and the outer query performs a UNION between a scalar aggregation query and a grouped aggregation query.
How to repeat:
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
DROP TABLE IF EXISTS t0;
DROP VIEW IF EXISTS v0;
CREATE TABLE t0 (a INT);
INSERT INTO t0 VALUES (1), (1), (0), (5), (5);
CREATE VIEW v0 AS
SELECT a, COUNT(*) AS b FROM t0 GROUP BY a;
SELECT b, MIN(a) FROM v0 WHERE b = 0 UNION SELECT b, MIN(a) FROM v0 GROUP BY b; -- row = 3
WITH cte AS (
SELECT a, COUNT(*) AS b FROM t0 GROUP BY a
)
SELECT b, MIN(a) FROM cte WHERE b = 0 UNION SELECT b, MIN(a) FROM cte GROUP BY b; -- row = 4
VIEW RESULT:
+------+--------+
| b | MIN(a) |
+------+--------+
| NULL | NULL |
| 2 | 1 |
| 1 | 0 |
+------+--------+
CTE RESULT:
+------+--------+
| b | MIN(a) |
+------+--------+
| NULL | NULL |
| 2 | 1 |
| 1 | 0 |
| 2 | 5 |
+------+--------+
Description: There is a query result inconsistency when using a VIEW versus a CTE (Common Table Expression). Both contain the exact same logic (an inner GROUP BY), and the outer query performs a UNION between a scalar aggregation query and a grouped aggregation query. How to repeat: DROP DATABASE IF EXISTS test; CREATE DATABASE test; USE test; DROP TABLE IF EXISTS t0; DROP VIEW IF EXISTS v0; CREATE TABLE t0 (a INT); INSERT INTO t0 VALUES (1), (1), (0), (5), (5); CREATE VIEW v0 AS SELECT a, COUNT(*) AS b FROM t0 GROUP BY a; SELECT b, MIN(a) FROM v0 WHERE b = 0 UNION SELECT b, MIN(a) FROM v0 GROUP BY b; -- row = 3 WITH cte AS ( SELECT a, COUNT(*) AS b FROM t0 GROUP BY a ) SELECT b, MIN(a) FROM cte WHERE b = 0 UNION SELECT b, MIN(a) FROM cte GROUP BY b; -- row = 4 VIEW RESULT: +------+--------+ | b | MIN(a) | +------+--------+ | NULL | NULL | | 2 | 1 | | 1 | 0 | +------+--------+ CTE RESULT: +------+--------+ | b | MIN(a) | +------+--------+ | NULL | NULL | | 2 | 1 | | 1 | 0 | | 2 | 5 | +------+--------+