Bug #121239 Wrong result: the same CTE returns inconsistent cardinality across consumers with UNION/EXCEPT

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.

Thank you for the bug report.
Verified as described.

Posted by developer: Bug status updated to 'Patch pending'

Contribution submitted via Github - Bug#121239: Preserve set-operation state across shared CTE references 
(*) Contribution by PR specific confirm no longer needed

Contribution: git_patch_4588022553.txt (text/plain), 8.02 KiB.

Posted by developer: Bug status updated to 'Patch approved'

Posted by developer: Bug status updated to 'Documenting'