Repository navigation
feat: decorrelate correlated scalar subqueries with ORDER BY ... LIMIT 1 - #26740
Draft
princejain756 wants to merge 1 commit into
Draft
princejain756 wants to merge 1 commit into
princejain756 wants to merge 1 commit into
Conversation
PullUpCorrelatedExpr cannot rewrite a scalar subquery when a Sort or Limit sits between the subquery head and the correlated filter, so queries of the "latest row per outer row" form failed at physical planning. When pull-up fails on the Limit(1) -> ... -> Sort -> Filter shape, fall back to two rewrites that preserve the at-most-one-row-per-outer-row contract: - equalities plus a range predicate on the sort key become an ASOF join on the outer side (ORDER BY k DESC over inner.k <= outer.v becomes MATCH_CONDITION outer.v >= inner.k) - equalities only become an aggregate grouped by the equality inner columns computing first_value(output ORDER BY sort keys), left-joined on the keys Correlated predicates neither rewrite can express are left in place. Fixes apache#26710.
github-actions
Bot
added
optimizer
princejain756
added a commit
to princejain756/princejain756
that referenced
this pull request
Oct 25, 2026
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
Which issue does this PR close?
ORDER BY ... LIMIT 1fail with "Physical plan does not support logical expression ScalarSubquery" #26710.Rationale for this change
Correlated scalar subqueries that pick one row per outer row with
ORDER BY ... LIMIT 1reach the physical planner unrewritten and fail withPhysical plan does not support logical expression ScalarSubquery. The issue reports 11 of 409 DB Olympics queries failing this way.PullUpCorrelatedExprstops at theSort(and atLimitfor non-EXISTS), soScalarSubqueryToJoingives up.What changes are included in this PR?
When
PullUpCorrelatedExprreportscan_pull_up = false,ScalarSubqueryToJoinnow tries a targeted rewrite of theLimit 1 -> ... -> Sort -> ... -> correlated Filtershape:AsOfJoin.ORDER BY k DESCoverinner.k <= outer.vrewrites toMATCH_CONDITION outer.v >= inner.k, which returns the greatestk <= vper outer row.ASCmirrors this.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) returnOk(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_valuerather thanrow_number() OVER (...) = 1; same one-row-per-group semantics without a dependency ondatafusion-functions-window.What is the testing strategy for this PR?
subquery.slt: ASOF decorrelation (result values and the rewritten logical plan), ascending and descending sorts, output column equal to the sort key, equality-onlyfirst_valuedecorrelation 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).scalar_subquery_to_joinfor 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.