Description:
A query using a CTE returns a different result from an equivalent query using a VIEW. The VIEW result appears to be correct, while the CTE query incorrectly misses the aggregate row (0, 1.5).
The issue seems related to CTE handling, nested aggregation with STDDEV_POP(), GROUP BY, and an outer UNION.
How to repeat:
mysql> select version();
+-----------+
| version() |
+-----------+
| 9.6.0 |
+-----------+
DROP DATABASE IF EXISTS dd;
CREATE DATABASE dd;
USE dd;
CREATE TABLE a (
d INT,
e INT
);
INSERT INTO a VALUES (0, 0);
INSERT INTO a VALUES ();
INSERT INTO a VALUES (3, 0);
CREATE VIEW v0 AS
SELECT
d AS f,
STDDEV_POP(e) AS g
FROM a
GROUP BY f;
SELECT g, f
FROM v0
WHERE g = 0
UNION
SELECT g, STDDEV_POP(f)
FROM v0
GROUP BY g;
WITH cte AS (
SELECT
d AS f,
STDDEV_POP(e) AS g
FROM a
GROUP BY f
)
SELECT g, f
FROM cte
WHERE g = 0
UNION
SELECT g, STDDEV_POP(f)
FROM cte
GROUP BY g;
Actual result:
The VIEW query returns:
+------+------+
| g | f |
+------+------+
| 0 | 0 |
| 0 | 3 |
| 0 | 1.5 |
| NULL | NULL |
+------+------+
The equivalent CTE query returns:
+------+------+
| g | f |
+------+------+
| 0 | 0 |
| 0 | 3 |
| NULL | NULL |
+------+------+
Description: A query using a CTE returns a different result from an equivalent query using a VIEW. The VIEW result appears to be correct, while the CTE query incorrectly misses the aggregate row (0, 1.5). The issue seems related to CTE handling, nested aggregation with STDDEV_POP(), GROUP BY, and an outer UNION. How to repeat: mysql> select version(); +-----------+ | version() | +-----------+ | 9.6.0 | +-----------+ DROP DATABASE IF EXISTS dd; CREATE DATABASE dd; USE dd; CREATE TABLE a ( d INT, e INT ); INSERT INTO a VALUES (0, 0); INSERT INTO a VALUES (); INSERT INTO a VALUES (3, 0); CREATE VIEW v0 AS SELECT d AS f, STDDEV_POP(e) AS g FROM a GROUP BY f; SELECT g, f FROM v0 WHERE g = 0 UNION SELECT g, STDDEV_POP(f) FROM v0 GROUP BY g; WITH cte AS ( SELECT d AS f, STDDEV_POP(e) AS g FROM a GROUP BY f ) SELECT g, f FROM cte WHERE g = 0 UNION SELECT g, STDDEV_POP(f) FROM cte GROUP BY g; Actual result: The VIEW query returns: +------+------+ | g | f | +------+------+ | 0 | 0 | | 0 | 3 | | 0 | 1.5 | | NULL | NULL | +------+------+ The equivalent CTE query returns: +------+------+ | g | f | +------+------+ | 0 | 0 | | 0 | 3 | | NULL | NULL | +------+------+