jayzhan211 opened a new issue, #26695: URL: https://github.com/apache/datafusion/issues/26695
### Describe the bug A correlated `EXISTS` / `NOT EXISTS` over a groupless aggregate gives wrong results when the subquery also has a predicate that references only outer columns, either in `HAVING` or in a filter above the aggregate. The predicate is ignored, so outer rows where it is false still pass `EXISTS`. ### To Reproduce ```sql CREATE TABLE t1(a INT) AS VALUES (1), (2), (-1); CREATE TABLE t2(a INT) AS VALUES (1); -- Query 1 SELECT a FROM t1 WHERE EXISTS (SELECT count(*) FROM t2 WHERE t2.a = t1.a HAVING t1.a > 0); -- Query 2 SELECT a FROM t1 WHERE EXISTS (SELECT 1 FROM (SELECT count(*) AS n FROM t2 WHERE t2.a = t1.a) WHERE t1.a > 0); -- Query 3 SELECT a FROM t1 WHERE NOT EXISTS (SELECT count(*) FROM t2 WHERE t2.a = t1.a HAVING t1.a > 0); ``` ### Expected behavior For `a = -1` the subquery returns no row, because `t1.a > 0` is false. For `a = 1` and `a = 2` it returns one row (`count(*)` = 1 and 0). | Query | DataFusion returns | Expected (DuckDB) | |---|---|---| | 1 | `-1, 1, 2` | `1, 2` | | 2 | `-1, 1, 2` | `1, 2` | | 3 | no rows | `-1` | Found on `main` at 702803d022. ### Additional context The compensation added for groupless aggregates in #25391 (`build_join_with_count_bug` in `decorrelate_predicate_subquery.rs`) has this gap. `t1.a > 0` is pulled up into the `LEFT JOIN` filter along with `t2.a = t1.a`, so for `a = -1` the outer row is unmatched. The compensation treats an unmatched row as "`count(*)` is 0 and the aggregate still produces a row", which makes `EXISTS` true. A predicate that references only outer columns removes the row whether or not the join matches, so it should not decide whether the row is matched. Before #25391 these queries dropped `a = 2` instead (the #24960 symptom). When the `HAVING` also reads the aggregate (for example `HAVING count(*) + t1.a > 1`), the rewrite falls back to the uncompensated path and still drops `a = 2`, as described in #24960. The aliased form, `... FROM (SELECT count(*) AS n ...) AS t WHERE t1.a > 0`, currently fails with a schema error (#26504). Once #26551 lands, it gives the same wrong result as Query 2. ### Willingness to contribute I cannot contribute a fix for this bug at this time -- 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]
