[Bug] Nereids planner does not push down correlated predicate into CASE WHEN EXISTS subquery in SELECT list (works correctly for WHERE-clause EXISTS)
- 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
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