Materializing an `EXISTS` input changes a `RIGHT JOIN` result
- Dominant language
- C++
- Stars
- 6k
- Forks
- 902
- PR merge metrics
- No merged PRs in 30d
Description
# 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
```text
source query: 16 | 0 | 36
materialized query: 8 | 8 | NULL
```
# How to repeat
```sql
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
```text
AliSQL version: 8.0.44-2-alisql-dev
Docker image: songhuaxiong/alisql:8.0.44-2
Test date: 2026-09-02
```
Contributor guide
Research direction
Run the supplied SQL reproduction on AliSQL 8.0.44-2 and compare the source query with the CTAS materialized rewrite, including runs with semijoin or optimizer materialization disabled. Done means the source and materialized forms both return 8 | 8 | NULL, rather than the source query returning 16 | 0 | 36.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- cpp, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 52/100