bert-beyondloops opened a new issue, #26260:
URL: https://github.com/apache/datafusion/issues/26260
### Is your feature request related to a problem or challenge?
With `datafusion.optimizer.prefer_hash_join = false`, the planner chooses
`SortMergeJoinExec`. Two things make it add `SortExec`s even when both inputs
are already sorted on the join keys.
**1. Sort options are hardcoded.**
`physical_planner.rs` builds the SMJ with `vec![SortOptions::default();
join_on.len()]`, which is `ASC NULLS FIRST` for every key. `SortMergeJoinExec`
then requires that ordering from both inputs. Inputs sorted `ASC NULLS
LAST` (or `DESC`, or a different option per key) don't satisfy it, so
`EnforceSorting` inserts a `SortExec` on each side. The inputs' declared
ordering is ignored.
**2. SMJ is only chosen when `target_partitions > 1`.**
The planner condition is `session_state.config().target_partitions() > 1
&& repartition_joins() && !prefer_hash_join`. With `target_partitions = 1`,
`prefer_hash_join = false` is silently ignored and a `HashJoinExec` is planned.
So users who run a single partition over sorted inputs can't get a streaming
SMJ. For `target_partitions == 1`, `repartition_joins` has no effect, because
no repartitioning happens.
### Describe the solution you'd like
1. Derive a per-key `SortOptions` from the ordering that the left and right
inputs declare through `EquivalenceProperties`. Both sides must satisfy the
same options, because SMJ compares the keys across the two sides.
- For each key, try these candidates in order: default (`ASC NULLS
FIRST`), flipped nulls, flipped direction, both flipped.
- Backtrack across keys until both inputs satisfy the whole key list
with `ordering_satisfy`.
- If no combination works, fall back to the current default. Plans that
work today don't change.
2. Plan `SortMergeJoinExec` when `prefer_hash_join = false` and either
`target_partitions == 1` or `repartition_joins` is true.
### Describe alternatives you've considered
_No response_
### Additional context
_No response_
### Willingness to contribute
I plan to contribute to this feature independently
--
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]
