apache / apache/datafusion

Correlated `EXISTS`/`IN` subqueries with groupless aggregates hit the count bug

Open
#24,960 0 comments 0 reactions 0 assignees View on GitHub
bug
Dominant language
Rust
Stars
9.3k
Forks
2.4k
Avg merge
3d 7h
Merged PRs (30d)
344

Description

### Describe the bug

A correlated `EXISTS` or `IN` subquery containing a groupless aggregate (e.g. `count(*)`, with no `GROUP BY`) returns incorrect results when the outer row has 0 matches on the subquery.

A groupless aggregate always produces exactly one output row, even over zero input rows. The current decorrelation instead drops the outer row entirely, as if the subquery had produced no rows at all.

### To Reproduce

```sql
CREATE TABLE t1(t1_int int); INSERT INTO t1 VALUES (1),(2),(3);
CREATE TABLE t2(t2_int int); INSERT INTO t2 VALUES (2);

SELECT t1.t1_int FROM t1 WHERE EXISTS (
SELECT count(*) FROM t2 WHERE t1.t1_int = t2.t2_int
) ORDER BY 1;
```

### Expected behavior

The query above should return all three rows: `1, 2, 3`.

For `t1_int=1` and `t1_int=3`, no row in `t2` matches, so the inner `count(*)` evaluates to `0` rather than producing no row at all. A `0` is still a row, so `EXISTS` should be `true` for those two outer rows too.

Currently, DataFusion returns a single row, `2`, silently dropping the two rows whose `count(*)` evaluated to `0`.

The `NOT EXISTS` variant of the same query surfaces the same underlying bug. It should return no rows (since `EXISTS` is true for every row) but instead returns `1, 3`.

### Additional context

Same bug class referenced in #10553 and #15032/#15281.

Contributor guide

Open the contributing guide

Research direction

Start by running the correlated EXISTS and NOT EXISTS SQL examples against DataFusion and confirm the incorrect results. Trace the correlated-subquery decorrelation path for groupless aggregates, then add regression coverage showing that zero-match count(*) still preserves the outer row. Done means the EXISTS query returns 1, 2, 3 and the NOT EXISTS variant returns no rows.

Written by the indexing model from the issue text.

Assessment

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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.