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

Start by running the supplied SQL reproduction against the AliSQL 8.0.44-2 Docker image and confirm the differing results. Trace the CTE, UNION, scalar subquery, ORDER BY, and LIMIT handling, then add or run a regression test showing that both forms return 2025-09-01 | 29.

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,
AliSQL 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

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

How to repeat

DROP DATABASE IF EXISTS alisql_union_cte_limit_repro;
CREATE DATABASE alisql_union_cte_limit_repro;
USE alisql_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;

Version

AliSQL version: 8.0.44-2-alisql-dev
Docker image:   songhuaxiong/alisql:8.0.44-2
Test date:      2026-09-02