mohammadnaqvi04 opened a new issue, #24960:
URL: https://github.com/apache/datafusion/issues/24960
### 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.
--
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]
