Repository navigation
fix: don't report equi-key equivalence for PiecewiseMergeJoin range joins - #24361
Merged
Merged
Conversation
…oins `PiecewiseMergeJoinExec::compute_properties` passed the join's `on` pair to `join_equivalence_properties` as an equijoin key. For an INNER join that registers `left_on == right_on` as an output equivalence — but PWMJ's `on` is a range predicate (`l < r`), not equality, so the equivalence is false. It let the optimizer treat a sort on the left key as also sorting the right key and drop a required `ORDER BY`, returning wrongly ordered rows. A range join adds no column equivalences, so pass no `on` pairs. Existing pwmj.slt plans/results updated to the now-correct (fully sorted) output; adds a regression test. Closes apache#24360. Co-authored-by: Claude Code
github-actions
Bot
added
sqllogictest
|
Thank you for opening this pull request! Reviewer note: cargo-semver-checks reported the current version number is not SemVer-compatible with the changes in this pull request (compared against the base branch). Details |
Codecov Report✅ All modified and coverable lines are covered by tests. Additional details and impacted files@@ Coverage Diff @@
## main #24361 +/- ##
==========================================
- Coverage 81.19% 81.18% -0.01%
==========================================
Files 1110 1110
Lines 388616 388614 -2
Branches 388616 388614 -2
==========================================
- Hits 315534 315512 -22
- Misses 54500 54515 +15
- Partials 18582 18587 +5 ☔ View full report in Codecov by Harness. 🚀 New features to boost your workflow:
|
…nce-fix # Conflicts: # datafusion/sqllogictest/test_files/pwmj.slt
comphead
approved these changes
Aug 14, 2026
comphead
left a comment
comphead
left a comment
Contributor
There was a problem hiding this comment.
Thanks @viirya I feel our testing is not enough for PWMJ if we keep coming across such issues and there is some of them during past weeks, maybe because people started to use PWMJ.
But before making it non-experimental we need to make sure correctness issues are solved and use cases covered, how I currently see it, we need to check if all existing join SLT tests passed with PWMJ on, I'll take care on it
Member
Author
|
Yea, make sense. Thanks @comphead |
imtherealnaska
pushed a commit
to imtherealnaska/datafusion
that referenced
this pull request
Aug 16, 2026
…oins (apache#24361) ## Which issue does this PR close? - Closes apache#24360. ## Rationale for this change `PiecewiseMergeJoinExec::compute_properties` passed the join's `on` pair to `join_equivalence_properties` as if it were an equijoin key. For an `INNER` join that registers `left_on == right_on` as an output equivalence — but PWMJ's `on` is a **range** predicate (`l.v < r.v`), not equality, so the equivalence is false. It let the optimizer treat a sort on the left key as also sorting the right key and drop a required `ORDER BY`, returning wrongly ordered rows. ```sql set datafusion.optimizer.enable_piecewise_merge_join = true; create table l(v int) as values (1),(2),(3),(5),(8); create table r(v int) as values (4),(6),(9),(2); select l.v, r.v from l join r on l.v < r.v where l.v = 2 order by r.v; -- PWMJ: 2,9 / 2,6 / 2,4 (wrong order) -- NLJ: 2,4 / 2,6 / 2,9 (correct) ``` The plans differ: PWMJ sorts only on `l.v` (`SortExec: expr=[v@0 ASC]`); NLJ sorts on both (`[v@0 ASC, v@1 ASC]`). ## What changes are included in this PR? - `compute_properties` no longer passes the range `on` pair to `join_equivalence_properties` (a range join adds no column equivalences). The now-unused `join_on` parameter is dropped. - Existing `pwmj.slt` plans/results updated to the corrected (fully sorted) output — they previously encoded the wrong ordering — and a regression test is added. ## Are these changes tested? Yes. - New regression test in `pwmj.slt` (INNER `l.v < r.v`, `WHERE l.v = 2`, `ORDER BY r.v`) asserting the correctly ordered result. - The existing `pwmj.slt` cases that asserted the wrong ordering / a single-column sort plan are updated to the correct values (verified against `NestedLoopJoin`). - `piecewise` unit tests and `joins.slt` still pass. Only `INNER` is affected. ## Are there any user-facing changes? `INNER` range joins via `PiecewiseMergeJoin` (behind `enable_piecewise_merge_join`, default off) no longer drop a required sort, matching `NestedLoopJoin`. No API changes.
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?
Rationale for this change
PiecewiseMergeJoinExec::compute_propertiespassed the join'sonpair tojoin_equivalence_propertiesas if it were an equijoin key. For anINNERjoin that registersleft_on == right_onas an output equivalence — but PWMJ'sonis a range predicate (l.v < r.v), not equality, so the equivalence is false. It let the optimizer treat a sort on the left key as also sorting the right key and drop a requiredORDER BY, returning wrongly ordered rows.The plans differ: PWMJ sorts only on
l.v(SortExec: expr=[v@0 ASC]); NLJ sorts on both ([v@0 ASC, v@1 ASC]).What changes are included in this PR?
compute_propertiesno longer passes the rangeonpair tojoin_equivalence_properties(a range join adds no column equivalences). The now-unusedjoin_onparameter is dropped.pwmj.sltplans/results updated to the corrected (fully sorted) output — they previously encoded the wrong ordering — and a regression test is added.Are these changes tested?
Yes.
pwmj.slt(INNERl.v < r.v,WHERE l.v = 2,ORDER BY r.v) asserting the correctly ordered result.pwmj.sltcases that asserted the wrong ordering / a single-column sort plan are updated to the correct values (verified againstNestedLoopJoin).piecewiseunit tests andjoins.sltstill pass. OnlyINNERis affected.Are there any user-facing changes?
INNERrange joins viaPiecewiseMergeJoin(behindenable_piecewise_merge_join, default off) no longer drop a required sort, matchingNestedLoopJoin. No API changes.