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

Ejecute la reproducción SQL proporcionada en AliSQL 8.0.44-2 y compare la consulta de origen con la reescritura materializada de CTAS, incluidas las ejecuciones con semijoin o la materialización del optimizador deshabilitados. Se considera terminado cuando tanto la forma de origen como la materializada devuelvan 8 | 8 | NULL, en lugar de que la consulta de origen devuelva 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