Description:
A query using a CTE returns a different aggregate result from an equivalent query using a VIEW. The VIEW result appears to be correct, while the CTE query incorrectly returns two different aggregate rows for the same GROUP BY key.
This looks like a wrong-result bug involving CTE/derived table handling, YEAR columns, scalar subqueries in the SELECT list, and GROUP BY aggregation.
How to repeat:
mysql> select version();
+-----------+
| version() |
+-----------+
| 9.6.0 |
+-----------+
DROP DATABASE IF EXISTS dd;
CREATE DATABASE dd;
USE dd;
CREATE TABLE a (
b YEAR,
e INT
);
INSERT INTO a VALUES (2001, 0);
INSERT INTO a VALUES ();
INSERT INTO a VALUES (2001, 8);
CREATE VIEW v0 AS
SELECT
b AS f,
e AS g,
(SELECT 3 WHERE e) AS x
FROM a;
SELECT f, MAX(g)
FROM v0
WHERE f = 0
UNION
SELECT f, MAX(g)
FROM v0
GROUP BY f;
WITH cte AS (
SELECT
b AS f,
e AS g,
(SELECT 3 WHERE e) AS x
FROM a
)
SELECT f, MAX(g)
FROM cte
WHERE f = 0
UNION
SELECT f, MAX(g)
FROM cte
GROUP BY f;
Actual result:
The VIEW query returns:
+------+--------+
| f | MAX(g) |
+------+--------+
| NULL | NULL |
| 2001 | 8 |
+------+--------+
The equivalent CTE query returns:
+------+--------+
| f | MAX(g) |
+------+--------+
| NULL | NULL |
| 2001 | 0 |
| 2001 | 8 |
+------+--------+
Description: A query using a CTE returns a different aggregate result from an equivalent query using a VIEW. The VIEW result appears to be correct, while the CTE query incorrectly returns two different aggregate rows for the same GROUP BY key. This looks like a wrong-result bug involving CTE/derived table handling, YEAR columns, scalar subqueries in the SELECT list, and GROUP BY aggregation. How to repeat: mysql> select version(); +-----------+ | version() | +-----------+ | 9.6.0 | +-----------+ DROP DATABASE IF EXISTS dd; CREATE DATABASE dd; USE dd; CREATE TABLE a ( b YEAR, e INT ); INSERT INTO a VALUES (2001, 0); INSERT INTO a VALUES (); INSERT INTO a VALUES (2001, 8); CREATE VIEW v0 AS SELECT b AS f, e AS g, (SELECT 3 WHERE e) AS x FROM a; SELECT f, MAX(g) FROM v0 WHERE f = 0 UNION SELECT f, MAX(g) FROM v0 GROUP BY f; WITH cte AS ( SELECT b AS f, e AS g, (SELECT 3 WHERE e) AS x FROM a ) SELECT f, MAX(g) FROM cte WHERE f = 0 UNION SELECT f, MAX(g) FROM cte GROUP BY f; Actual result: The VIEW query returns: +------+--------+ | f | MAX(g) | +------+--------+ | NULL | NULL | | 2001 | 8 | +------+--------+ The equivalent CTE query returns: +------+--------+ | f | MAX(g) | +------+--------+ | NULL | NULL | | 2001 | 0 | | 2001 | 8 | +------+--------+