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 returns an incorrect COUNT(*) value for the empty string group.
The problem seems to involve CTE handling or optimizer transformations around UNION ALL, GROUP BY, HAVING, and LOCATE('', column).
How to repeat:
mysql> select version();
+-----------+
| version() |
+-----------+
| 9.6.0 |
+-----------+
DROP DATABASE IF EXISTS dd;
CREATE DATABASE dd;
USE dd;
CREATE TABLE t0 (
c0 VARCHAR(9),
c1 BINARY
);
INSERT INTO t0 VALUES ('', UNHEX(''));
INSERT INTO t0 VALUES (';', UNHEX(''));
INSERT INTO t0 VALUES ('', UNHEX(''));
CREATE VIEW v0 AS
SELECT CONCAT_WS('', c0, '') AS vc_0
FROM t0
WHERE 0 AND 0
UNION ALL
SELECT CONCAT_WS('', c0, '') AS vc_0
FROM t0
WHERE 0 OR BIT_LENGTH(c1) >= 0;
SELECT vc_0 AS gk0, COUNT(*)
FROM v0
WHERE vc_0 = ''
GROUP BY gk0
HAVING COUNT(*) >= 0
AND COUNT(*) >= 0
AND COUNT(*) BETWEEN 0 AND 0
UNION
SELECT vc_0 AS gk0, COUNT(*)
FROM v0
WHERE LOCATE('', vc_0)
GROUP BY gk0;
WITH CTE AS (
SELECT CONCAT_WS('', c0, '') AS vc_0
FROM t0
WHERE 0 AND 0
UNION ALL
SELECT CONCAT_WS('', c0, '') AS vc_0
FROM t0
WHERE 0 OR BIT_LENGTH(c1) >= 0
)
SELECT vc_0 AS gk0, COUNT(*)
FROM CTE
WHERE vc_0 = ''
GROUP BY gk0
HAVING COUNT(*) >= 0
AND COUNT(*) >= 0
AND COUNT(*) BETWEEN 0 AND 0
UNION
SELECT vc_0 AS gk0, COUNT(*)
FROM CTE
WHERE LOCATE('', vc_0)
GROUP BY gk0;
Actual result:
The VIEW query returns:
+------+----------+
| gk0 | COUNT(*) |
+------+----------+
| | 2 |
| ; | 1 |
+------+----------+
The equivalent CTE query returns:
+------+----------+
| gk0 | COUNT(*) |
+------+----------+
| | 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 returns an incorrect COUNT(*) value for the empty string group. The problem seems to involve CTE handling or optimizer transformations around UNION ALL, GROUP BY, HAVING, and LOCATE('', column). How to repeat: mysql> select version(); +-----------+ | version() | +-----------+ | 9.6.0 | +-----------+ DROP DATABASE IF EXISTS dd; CREATE DATABASE dd; USE dd; CREATE TABLE t0 ( c0 VARCHAR(9), c1 BINARY ); INSERT INTO t0 VALUES ('', UNHEX('')); INSERT INTO t0 VALUES (';', UNHEX('')); INSERT INTO t0 VALUES ('', UNHEX('')); CREATE VIEW v0 AS SELECT CONCAT_WS('', c0, '') AS vc_0 FROM t0 WHERE 0 AND 0 UNION ALL SELECT CONCAT_WS('', c0, '') AS vc_0 FROM t0 WHERE 0 OR BIT_LENGTH(c1) >= 0; SELECT vc_0 AS gk0, COUNT(*) FROM v0 WHERE vc_0 = '' GROUP BY gk0 HAVING COUNT(*) >= 0 AND COUNT(*) >= 0 AND COUNT(*) BETWEEN 0 AND 0 UNION SELECT vc_0 AS gk0, COUNT(*) FROM v0 WHERE LOCATE('', vc_0) GROUP BY gk0; WITH CTE AS ( SELECT CONCAT_WS('', c0, '') AS vc_0 FROM t0 WHERE 0 AND 0 UNION ALL SELECT CONCAT_WS('', c0, '') AS vc_0 FROM t0 WHERE 0 OR BIT_LENGTH(c1) >= 0 ) SELECT vc_0 AS gk0, COUNT(*) FROM CTE WHERE vc_0 = '' GROUP BY gk0 HAVING COUNT(*) >= 0 AND COUNT(*) >= 0 AND COUNT(*) BETWEEN 0 AND 0 UNION SELECT vc_0 AS gk0, COUNT(*) FROM CTE WHERE LOCATE('', vc_0) GROUP BY gk0; Actual result: The VIEW query returns: +------+----------+ | gk0 | COUNT(*) | +------+----------+ | | 2 | | ; | 1 | +------+----------+ The equivalent CTE query returns: +------+----------+ | gk0 | COUNT(*) | +------+----------+ | | 1 | | ; | 1 | +------+----------+