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 GROUP_CONCAT(vc_1) = '1' instead of GROUP_CONCAT(vc_1) = '1,1' for the group where vc_1 = 1.
This looks like a wrong-result bug involving CTE handling, DISTINCT, NULL vs empty string values, predicate evaluation, GROUP_CONCAT(), 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 t0 (
c0 CHAR(1),
c3 INT
);
INSERT INTO t0 VALUES ('', 1);
INSERT INTO t0 VALUES ('', 0);
INSERT INTO t0 VALUES (NULL, 1);
CREATE VIEW v0 AS
SELECT DISTINCT
c0 AS vc_0,
c3 AS vc_1
FROM t0;
SELECT
vc_1 AS gk0,
GROUP_CONCAT(vc_1) AS gc
FROM v0
WHERE vc_1 = 0
GROUP BY gk0
UNION
SELECT
vc_1 AS gk0,
GROUP_CONCAT(vc_1) AS gc
FROM v0
WHERE vc_1 > 0 OR vc_0 = ''
GROUP BY gk0;
WITH cte AS (
SELECT DISTINCT
c0 AS vc_0,
c3 AS vc_1
FROM t0
)
SELECT
vc_1 AS gk0,
GROUP_CONCAT(vc_1) AS gc
FROM cte
WHERE vc_1 = 0
GROUP BY gk0
UNION
SELECT
vc_1 AS gk0,
GROUP_CONCAT(vc_1) AS gc
FROM cte
WHERE vc_1 > 0 OR vc_0 = ''
GROUP BY gk0;
Actual result:
The VIEW query returns:
+------+------+
| gk0 | gc |
+------+------+
| 0 | 0 |
| 1 | 1,1 |
+------+------+
The equivalent CTE query returns:
+------+------+
| gk0 | gc |
+------+------+
| 0 | 0 |
| 1 | 1 |
+------+------+
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 GROUP_CONCAT(vc_1) = '1' instead of GROUP_CONCAT(vc_1) = '1,1' for the group where vc_1 = 1. This looks like a wrong-result bug involving CTE handling, DISTINCT, NULL vs empty string values, predicate evaluation, GROUP_CONCAT(), 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 t0 ( c0 CHAR(1), c3 INT ); INSERT INTO t0 VALUES ('', 1); INSERT INTO t0 VALUES ('', 0); INSERT INTO t0 VALUES (NULL, 1); CREATE VIEW v0 AS SELECT DISTINCT c0 AS vc_0, c3 AS vc_1 FROM t0; SELECT vc_1 AS gk0, GROUP_CONCAT(vc_1) AS gc FROM v0 WHERE vc_1 = 0 GROUP BY gk0 UNION SELECT vc_1 AS gk0, GROUP_CONCAT(vc_1) AS gc FROM v0 WHERE vc_1 > 0 OR vc_0 = '' GROUP BY gk0; WITH cte AS ( SELECT DISTINCT c0 AS vc_0, c3 AS vc_1 FROM t0 ) SELECT vc_1 AS gk0, GROUP_CONCAT(vc_1) AS gc FROM cte WHERE vc_1 = 0 GROUP BY gk0 UNION SELECT vc_1 AS gk0, GROUP_CONCAT(vc_1) AS gc FROM cte WHERE vc_1 > 0 OR vc_0 = '' GROUP BY gk0; Actual result: The VIEW query returns: +------+------+ | gk0 | gc | +------+------+ | 0 | 0 | | 1 | 1,1 | +------+------+ The equivalent CTE query returns: +------+------+ | gk0 | gc | +------+------+ | 0 | 0 | | 1 | 1 | +------+------+