siddubakka opened a new pull request, #26723: URL: https://github.com/apache/datafusion/pull/26723
> [!IMPORTANT] > > **Fork CI setup:** For pull requests from a fork to `main`, enable workflows in your fork's **Actions** tab before you push. > See the [fork CI setup guide](https://datafusion.apache.org/contributor-guide/#pull-requests-from-forks). ## Which issue does this PR close? - Closes #26503. ## Rationale for this change A correlated scalar subquery whose aggregate is `count(*)` returns `0` for outer rows where the subquery produced no row. When the subquery also has a `HAVING` clause (or a filter above the aggregate) that references an outer column, an unmatched row means that condition rejected the aggregate output — the subquery produced no row at all, so the scalar subquery should be `NULL`, not `0`. For example (from the issue): ```sql SELECT x.a, x.d, (SELECT count(*) FROM (VALUES (1)) AS y(c) WHERE y.c = x.d HAVING count(*) >= x.a) AS v FROM (VALUES (1, 1), (2, 1), (1, 9)) AS x(a, d) ORDER BY x.d, x.a; ``` | a | d | v | |---|---|------| | 1 | 1 | 1 | | 2 | 1 | NULL | | 1 | 9 | NULL | PostgreSQL returns `NULL` for the last two rows; DataFusion currently returns `0`, because the empty-input default is used unconditionally when the LEFT JOIN is unmatched. Note that the condition has to be evaluated on the empty-input default: `HAVING count(*) >= 0` holds even for an empty input, so for a non-matching outer row the correct answer there is still `0`, not `NULL`. This PR keeps that distinction. ## What changes are included in this PR? - `decorrelate.rs`: `PullUpCorrelatedExpr` gains a `having_like_join_filters` field. It records the outer-reference conditions of a `Filter` only when it sits directly above a count-bug `Aggregate` (i.e. a `HAVING` clause), so `WHERE` clauses below the aggregate, which only prune input rows, are not affected. - `scalar_subquery_to_join.rs`: in `build_join`, the recorded conditions are requalified, substituted with the empty-input aggregate defaults and type-coerced, then used to guard the unmatched arm of the compensation `CASE`: the default (e.g. `0`) is returned only when the condition holds on the defaults, `NULL` otherwise. - `subquery.slt`: four regression tests covering the issue queries plus both `HAVING` guard directions. ## What is the testing strategy for this PR? Four regression cases added to `datafusion/sqllogictest/test_files/subquery.slt`, covering: 1. the first query from the issue (`HAVING count(*) >= x.a` → `1, NULL, NULL`), 2. the second query from the issue (filter above the aggregate → `NULL`), 3. `HAVING count(*) >= 0` with no matching inner row (still `0`, guarding against over-applying the fix), 4. `HAVING count(*) > 0` with no matching inner row (`NULL`). Also verified against the rebuilt CLI that the plain count-bug case without `HAVING` still returns `0` (unchanged behavior), and ran `cargo test -p datafusion-optimizer` plus `cargo fmt`/`clippy`. ## Are there any user-facing changes? Query results change for this bug: scalar subqueries with an outer-column `HAVING` over `count(*)` now return `NULL` instead of `0` where PostgreSQL returns `NULL`. No API changes, so no `api change` label or documentation updates are needed. -- 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]
