hakkibc opened a new issue, #67676:
URL: https://github.com/apache/doris/issues/67676
**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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
