Correlated `EXISTS`/`IN` subqueries with groupless aggregates hit the count 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
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