Bug #121208 Materializing a shared `UNION` CTE changes a scalar `ORDER BY ... LIMIT` result

• Submitted: 2 Sep 9:04
• Reporter: cl hl
• Status: Open
• Category: MySQL Server
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[2 Sep 9:04] cl hl

Description:
The source query and materialized rewrite contain the same two CTE rows. Their
dates are distinct, so `ORDER BY d LIMIT 1` must select `2025-09-01`.

When the outer query and scalar subquery directly share the `UNION` CTE,
MySQL instead selects the first branch's `2025-09-02` row. Materializing the
complete CTE result with CTAS before reconstructing the query returns the
correct row.

# Expected result

Both forms should return `2025-09-01 | 29`.

# Actual result

```text
source query:       2025-09-02 | 1
materialized query: 2025-09-01 | 29
```

How to repeat:
DROP DATABASE IF EXISTS mysql_union_cte_limit_repro;
CREATE DATABASE mysql_union_cte_limit_repro;
USE mysql_union_cte_limit_repro;

CREATE TABLE t (
    d DATE,
    n INT
);

INSERT INTO t VALUES
    ('2025-09-02', 1),
    ('2025-09-01', 29);

-- Source query: incorrectly returns 2025-09-02, 1.
WITH c AS (
    SELECT d, n FROM t WHERE n = 1
    UNION
    SELECT d, n FROM t WHERE n = 29
)
SELECT a.d, a.n
FROM c AS a
WHERE (
    SELECT b.d
    FROM c AS b
    ORDER BY b.d
    LIMIT 1
) = a.d;

-- Materialize the complete UNION result.
CREATE TABLE vect_cut_cte AS
    SELECT d, n FROM t WHERE n = 1
    UNION
    SELECT d, n FROM t WHERE n = 29;

SELECT * FROM vect_cut_cte ORDER BY d;

-- Materialized rewrite: correctly returns 2025-09-01, 29.
SELECT a.d, a.n
FROM vect_cut_cte AS a
WHERE (
    SELECT b.d
    FROM vect_cut_cte AS b
    ORDER BY b.d
    LIMIT 1
) = a.d;