Description:
A query using a CTE returns an extra aggregate row that is not returned by an equivalent query using a VIEW. The VIEW result appears to be correct, while the CTE query incorrectly returns an additional (NULL, NULL) row.
The issue seems related to CTE handling with ORDER BY/LIMIT, GROUP BY, SUM(), 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 t0 (
c1 INT,
b INT
);
INSERT INTO t0 VALUES (NULL, NULL);
INSERT INTO t0 VALUES (NULL, 1);
INSERT INTO t0 VALUES (1, -1);
CREATE VIEW v0 AS
SELECT b AS vc_0, c1 AS vc_3
FROM t0
ORDER BY 1
LIMIT 3;
SELECT vc_3 AS gk0, SUM(vc_0)
FROM v0
WHERE vc_3 IS NULL
GROUP BY gk0
UNION
SELECT vc_3 AS gk0, SUM(vc_0)
FROM v0
GROUP BY gk0;
WITH cte AS (
SELECT b AS vc_0, c1 AS vc_3
FROM t0
ORDER BY 1
LIMIT 3
)
SELECT vc_3 AS gk0, SUM(vc_0)
FROM cte
WHERE vc_3 IS NULL
GROUP BY gk0
UNION
SELECT vc_3 AS gk0, SUM(vc_0)
FROM cte
GROUP BY gk0;
Actual result:
The VIEW query returns:
+------+-----------+
| gk0 | SUM(vc_0) |
+------+-----------+
| NULL | 1 |
| 1 | -1 |
+------+-----------+
The equivalent CTE query returns an extra row:
+------+-----------+
| gk0 | SUM(vc_0) |
+------+-----------+
| NULL | 1 |
| NULL | NULL |
| 1 | -1 |
+------+-----------+
