Bug #120923 Wrong result with CTE, UNION ALL, GROUP BY, and COUNT()

• Submitted: 14 Jul 13:53
• Reporter: Aaditya Dubey
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.4.10
• OS: Any
• CPU Architecture: Any

[14 Jul 13:53] Aaditya Dubey

Description:
A query using a VIEW returns a different result from an equivalent query using a CTE.

The query involves:

UNION ALL

NULL values inserted via default column values

GROUP BY

COUNT(c)

an outer UNION

VIEW vs CTE execution

The VIEW query returns {0, 2}, while the equivalent CTE query returns {0, 1}.

This appears to be a wrong-result bug, possibly related to CTE evaluation, UNION ALL materialization, grouping, or aggregate evaluation.

How to repeat:
DROP DATABASE IF EXISTS dd;
CREATE DATABASE dd;
USE dd;
CREATE TABLE a (
  b INT
);
INSERT INTO a VALUES (1);
INSERT INTO a VALUES ();
CREATE OR REPLACE VIEW v0 AS
SELECT b AS c FROM a
UNION ALL
SELECT b FROM a;
-- Query using VIEW
SELECT c
FROM v0
WHERE c = 0
UNION
SELECT COUNT(c)
FROM v0
GROUP BY c;
-- Equivalent query using CTE
WITH CTE AS (
  SELECT b AS c FROM a
  UNION ALL
  SELECT b FROM a
)
SELECT c
FROM CTE
WHERE c = 0
UNION
SELECT COUNT(c)
FROM CTE
GROUP BY c;
Actual result
The VIEW query returns:

+------+
| c    |
+------+
|    2 |
|    0 |
+------+
2 rows in set
The equivalent CTE query returns:

+------+
| c    |
+------+
|    1 |
|    0 |
+------+
2 rows in set
Expected result
Both queries should return the same result.

The expected result appears to be:

+------+
| c    |
+------+
|    2 |
|    0 |
+------+

Suggested fix:
Reasoning
The base table a contains two rows:

b
----
1
NULL
The VIEW/CTE body is:

SELECT b AS c FROM a
UNION ALL
SELECT b FROM a
Because this uses UNION ALL, duplicates should be preserved. Therefore the VIEW/CTE result should contain four rows:

c
----
1
NULL
1
NULL
The first branch of the outer query is:

SELECT c
FROM v0
WHERE c = 0
This should return no rows, because neither 1 nor NULL satisfies c = 0.

The second branch is:

SELECT COUNT(c)
FROM v0
GROUP BY c
Grouping the four rows by c should produce:

Group c = 1:    COUNT(c) = 2
Group c = NULL: COUNT(c) = 0
Therefore the final result of the outer UNION should be {2, 0}.

The VIEW query returns {2, 0}, which matches this reasoning. The CTE query returns {1, 0}, which appears incorrect because it seems to count only one non-NULL row from the UNION ALL result.