princejain756 opened a new pull request, #26740: URL: https://github.com/apache/datafusion/pull/26740
## Which issue does this PR close? - Closes #26710. ## Rationale for this change Correlated scalar subqueries that pick one row per outer row with `ORDER BY ... LIMIT 1` reach the physical planner unrewritten and fail with `Physical plan does not support logical expression ScalarSubquery`. The issue reports 11 of 409 DB Olympics queries failing this way. `PullUpCorrelatedExpr` stops at the `Sort` (and at `Limit` for non-EXISTS), so `ScalarSubqueryToJoin` gives up. ## What changes are included in this PR? When `PullUpCorrelatedExpr` reports `can_pull_up = false`, `ScalarSubqueryToJoin` now tries a targeted rewrite of the `Limit 1 -> ... -> Sort -> ... -> correlated Filter` shape: - Equalities plus a single range predicate on the sort key, in the direction the sort picks, become an `AsOfJoin`. `ORDER BY k DESC` over `inner.k <= outer.v` rewrites to `MATCH_CONDITION outer.v >= inner.k`, which returns the greatest `k <= v` per outer row. `ASC` mirrors this. - Equalities only become a left join over an aggregate grouped by the equality inner columns computing `first_value(output ORDER BY sort keys)`. The UDAF is resolved through the session function registry since the optimizer crate cannot link the functions crates. Correlated predicates neither rewrite can express (`!=`, `OR`, a range not on the sort key, a sort key carrying an outer reference) return `Ok(None)`, so those subqueries keep today's behavior. The extracted `__scalar_sq_N.<name>` references resolve unchanged, so count-bug compensation and outer-side expression rewriting need no special handling. Deviation from the sketch in the issue: the equality-only shape uses `first_value` rather than `row_number() OVER (...) = 1`; same one-row-per-group semantics without a dependency on `datafusion-functions-window`. ## What is the testing strategy for this PR? - sqllogictest cases in `subquery.slt`: ASOF decorrelation (result values and the rewritten logical plan), ascending and descending sorts, output column equal to the sort key, equality-only `first_value` decorrelation in select and filter positions and inside a larger expression, NULLs on missing matches, and two cases that must still fail (range not bounding the sorted direction, non-equality non-range predicate). - unit tests in `scalar_subquery_to_join` for the ASOF rewrite, the equality-only bail-out when no function registry is available, and shapes that must not rewrite. ## Are there any user-facing changes? Queries that previously failed at physical planning now run. No API changes. -- 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]
