Description:
A non-recursive CTE containing UNION and EXCEPT produces inconsistent results when the same CTE is consumed by multiple query blocks in the same statement.
The CTE p contains exactly one row when evaluated independently.
However, when p is referenced both directly and through another CTE q, two consumers of the same CTE observe different cardinalities in the same statement:
the direct consumer reports COUNT(*) = 0
the consumer through q reports COUNT(*) = 1
Both consumers refer to the same CTE p, so they should observe the same relational result.
The issue is reproducible without any tables or user data and causes a silent wrong result.
Tested on MySQL 26.7.0.
How to repeat:
First, verify the set operation independently:
SELECT COUNT(*) FROM (
SELECT 'A' AS x
UNION SELECT 'A'
EXCEPT SELECT NULL WHERE FALSE
) AS p;
Result:
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set
This is correct. UNION removes the duplicate 'A', and the right-hand side of EXCEPT is empty.
Next, evaluate the same expression through a CTE:
WITH p AS (
SELECT 'A' AS x
UNION SELECT 'A'
EXCEPT SELECT NULL WHERE FALSE
)
SELECT COUNT(*) FROM p;
Result:
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set
This is also correct.
Now reference the same CTE p from two different consumers in the same statement:
WITH p AS (
SELECT 'A' AS x
UNION SELECT 'A'
EXCEPT SELECT NULL WHERE FALSE
), q AS (
SELECT COUNT(*) AS y FROM p
)
SELECT
(SELECT COUNT(*) FROM p) AS direct_count,
(SELECT y FROM q) AS count_via_q;
Actual result on MySQL 26.7.0:
+--------------+-------------+
| direct_count | count_via_q |
+--------------+-------------+
| 0 | 1 |
+--------------+-------------+
1 row in set
Expected result:
+--------------+-------------+
| direct_count | count_via_q |
+--------------+-------------+
| 1 | 1 |
+--------------+-------------+
The two scalar subqueries consume the same CTE p.
Therefore, direct_count and count_via_q should both observe the same cardinality.
The standalone control queries also demonstrate that the correct cardinality of p is 1.
For completeness:
SELECT VERSION();
returns:
+-----------+
| VERSION() |
+-----------+
| 26.7.0 |
+-----------+
1 row in set
No tables, schema objects, or user data are required to reproduce the issue.
Description: A non-recursive CTE containing UNION and EXCEPT produces inconsistent results when the same CTE is consumed by multiple query blocks in the same statement. The CTE p contains exactly one row when evaluated independently. However, when p is referenced both directly and through another CTE q, two consumers of the same CTE observe different cardinalities in the same statement: the direct consumer reports COUNT(*) = 0 the consumer through q reports COUNT(*) = 1 Both consumers refer to the same CTE p, so they should observe the same relational result. The issue is reproducible without any tables or user data and causes a silent wrong result. Tested on MySQL 26.7.0. How to repeat: First, verify the set operation independently: SELECT COUNT(*) FROM ( SELECT 'A' AS x UNION SELECT 'A' EXCEPT SELECT NULL WHERE FALSE ) AS p; Result: +----------+ | COUNT(*) | +----------+ | 1 | +----------+ 1 row in set This is correct. UNION removes the duplicate 'A', and the right-hand side of EXCEPT is empty. Next, evaluate the same expression through a CTE: WITH p AS ( SELECT 'A' AS x UNION SELECT 'A' EXCEPT SELECT NULL WHERE FALSE ) SELECT COUNT(*) FROM p; Result: +----------+ | COUNT(*) | +----------+ | 1 | +----------+ 1 row in set This is also correct. Now reference the same CTE p from two different consumers in the same statement: WITH p AS ( SELECT 'A' AS x UNION SELECT 'A' EXCEPT SELECT NULL WHERE FALSE ), q AS ( SELECT COUNT(*) AS y FROM p ) SELECT (SELECT COUNT(*) FROM p) AS direct_count, (SELECT y FROM q) AS count_via_q; Actual result on MySQL 26.7.0: +--------------+-------------+ | direct_count | count_via_q | +--------------+-------------+ | 0 | 1 | +--------------+-------------+ 1 row in set Expected result: +--------------+-------------+ | direct_count | count_via_q | +--------------+-------------+ | 1 | 1 | +--------------+-------------+ The two scalar subqueries consume the same CTE p. Therefore, direct_count and count_via_q should both observe the same cardinality. The standalone control queries also demonstrate that the correct cardinality of p is 1. For completeness: SELECT VERSION(); returns: +-----------+ | VERSION() | +-----------+ | 26.7.0 | +-----------+ 1 row in set No tables, schema objects, or user data are required to reproduce the issue.