apache / apache/doris

[Bug] Nereids planner does not push down correlated predicate into CASE WHEN EXISTS subquery in SELECT list (works correctly for WHERE-clause EXISTS)

Open
#67,676 1 comment 0 reactions 0 assignees View on GitHub
Dominant language
Java
Stars
15.9k
Forks
3.9k
Avg merge
2d 23h
Merged PRs (30d)
520

Description

**Doris version:** `doris-4.1.3-rc02` (FE), Nereids planner default

**Description:**

When a correlated `EXISTS` subquery appears inside a `CASE WHEN EXISTS (...)` expression in the `SELECT` list, the Nereids planner fails to push the correlation predicate down into the subquery's scan, causing a full table scan of the inner table instead of a single-partition/tablet lookup. The same correlated `EXISTS` pattern, when used in the outer `WHERE` clause instead, is optimized correctly.

**Minimal repro:**

```sql
CREATE TABLE PARENT_T (
id VARCHAR(16) NOT NULL,
name VARCHAR(48) NOT NULL
) UNIQUE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 4
PROPERTIES ('replication_num'='1');

CREATE TABLE CHILD_T (
parent_id VARCHAR(16) NOT NULL,
child_id VARCHAR(16) NOT NULL
) UNIQUE KEY(parent_id, child_id)
DISTRIBUTED BY HASH(parent_id) BUCKETS 4
PROPERTIES ('replication_num'='1');

INSERT INTO PARENT_T VALUES ('P1','a'),('P2','b'),('P3','c');
INSERT INTO CHILD_T VALUES ('P1','C1'),('P2','C2');
```

**Case A — WHERE-clause EXISTS (correct):**
```sql
EXPLAIN SELECT p.id
FROM PARENT_T p
WHERE p.id = 'P1'
AND EXISTS (SELECT 1 FROM CHILD_T c WHERE c.parent_id = p.id);
```
→ inner scan plan shows `PREDICATES: ((parent_id = 'P1') AND ...)`, `tablets=1/4`.

**Case B — same EXISTS inside SELECT-list CASE WHEN (bug):**
```sql
EXPLAIN SELECT p.id,
CASE WHEN EXISTS (SELECT 1 FROM CHILD_T c WHERE c.parent_id = p.id) THEN 1 ELSE 0 END AS has_child
FROM PARENT_T p
WHERE p.id = 'P1';
```
→ inner scan plan shows only `PREDICATES: (__DORIS_DELETE_SIGN__ = 0)`, `tablets=4/4` — the `p.id = 'P1'` constant is never propagated through the correlation into `CHILD_T`'s scan, even though `p.id` is fully constrained by an equality predicate in the outer query.

**Workaround found:** adding a redundant literal bound to the same constant inside the correlated subquery restores correct pushdown:
```sql
CASE WHEN EXISTS (
SELECT 1 FROM CHILD_T c WHERE c.parent_id = p.id AND c.parent_id = 'P1'
) THEN 1 ELSE 0 END
```
→ `PREDICATES: ((parent_id = 'P1') AND ...)`, `tablets=1/4` — same result set, correct pushdown.

**Note:** the scalar subquery form `(SELECT COUNT(*) FROM CHILD_T c WHERE c.parent_id = p.id)` used in the `SELECT` list is *not* affected — it pushes down correctly. Only the `CASE WHEN EXISTS(...)` form in the projection list exhibits this gap.

**Impact:** in a query with N such `CASE WHEN EXISTS` expressions against N different large tables, this causes N unnecessary full table scans per query execution. In our workload (multi-table "does entity hold related record X" style queries), we measured a **38-60x latency regression** from this gap alone before applying the workaround.

Contributor guide

Open the contributing guide

Research direction

Start by reproducing the two EXPLAIN queries from the issue on Doris 4.1.3-rc02, comparing WHERE-clause EXISTS with CASE WHEN EXISTS in the SELECT list and checking predicate and tablet pushdown. Trace the Nereids planner handling for correlated EXISTS in projection expressions; done means the CASE form propagates the outer equality into the CHILD_T scan without requiring the redundant literal predicate.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
50/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.