alibaba / alibaba/AliSQL

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

Open
#186 0 comments 0 reactions 0 assignees View on GitHub
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.