Materializing an `EXISTS` input changes a `RIGHT JOIN` result

Chạy bản tái hiện SQL được cung cấp trên AliSQL 8.0.44-2 và so sánh truy vấn nguồn với phép viết lại CTAS được materialize, bao gồm cả các lần chạy khi semijoin hoặc optimizer materialization bị vô hiệu hóa. Được xem là hoàn tất khi cả dạng nguồn và dạng materialize đều trả về 8 | 8 | NULL, thay vì truy vấn nguồn trả về 16 | 0 | 36.

Description

The source query and its explicitly materialized rewrite are equivalent: the
materialized relation contains exactly the rows produced by the EXISTS
subquery. That relation is empty, so both forms must produce eight
NULL-complemented rows from the RIGHT JOIN.

AliSQL instead returns sixteen matched rows for the source query. Materializing
the filtered EXISTS input with CTAS restores the correct result. Disabling
either semijoin or optimizer materialization also fixes the source query.

Expected result

Both forms should return 8 | 8 | NULL.

Actual result

source query:       16 | 0 | 36
materialized query:  8 | 8 | NULL

How to repeat

DROP DATABASE IF EXISTS alisql_exists_semijoin_repro;
CREATE DATABASE alisql_exists_semijoin_repro;
USE alisql_exists_semijoin_repro;

CREATE TABLE a (x INT, s LONGTEXT);
CREATE TABLE b (y TINYINT);
CREATE TABLE r (z INT, v VARCHAR(10));
CREATE TABLE e (w INT PRIMARY KEY);

INSERT INTO a VALUES (1, 'a'), (2, 'b');
INSERT INTO b VALUES (36);
INSERT INTO r VALUES
    (1, 'x'), (2, 'x'), (3, 'x'), (4, 'x'),
    (5, 'x'), (6, 'x'), (7, 'x'), (8, 'x');
INSERT INTO e VALUES (1), (2), (3);

-- Source query: incorrectly returns 16, 0, 36.
SELECT COUNT(*) AS row_count,
       SUM(b.y IS NULL) AS null_b_rows,
       MIN(b.y) AS min_b
FROM a
CROSS JOIN b ON EXISTS (
    SELECT 1
    FROM e
    WHERE NOT e.w / NULLIF(e.w, 0) >= 0
)
RIGHT JOIN r ON a.s <> r.v;

-- Materialize the empty input relation.
CREATE TABLE vect_cut_e AS
SELECT w
FROM e
WHERE NOT w / NULLIF(w, 0) >= 0;

SELECT COUNT(*) AS cut_rows FROM vect_cut_e;

-- Materialized rewrite: correctly returns 8, 8, NULL.
SELECT COUNT(*) AS row_count,
       SUM(b.y IS NULL) AS null_b_rows,
       MIN(b.y) AS min_b
FROM a
CROSS JOIN b ON EXISTS (SELECT 1 FROM vect_cut_e)
RIGHT JOIN r ON a.s <> r.v;

Version

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