qzsee opened a new pull request, #68543:
URL: https://github.com/apache/doris/pull/68543
### What problem does this PR solve?
Related PR: #53127, #67067
Problem Summary:
`OrExpansion` rewrites a nested loop join whose ON condition is a
disjunction of equal predicates into a `UNION ALL` of hash joins. It supports
`INNER` / `LEFT ANTI` / `LEFT OUTER` / `FULL OUTER JOIN`, but not `RIGHT OUTER
JOIN`, so a `RIGHT OUTER JOIN` with such a condition falls back to `NESTED LOOP
JOIN`.
`RIGHT OUTER JOIN` reaches `OrExpansion` when `SemiJoinCommute` does not
commute it to `LEFT OUTER JOIN`, e.g. `disable_join_reorder = true`, or the
join has a leading / distribute hint:
```sql
set disable_join_reorder = true;
explain select oe1.k0, oe2.k0
from oe1 right outer join oe2
on oe1.k0 = oe2.k0 or oe1.k1 + 1 = oe2.k1 * 2;
-- before: 4:VNESTED LOOP JOIN | join op: RIGHT OUTER JOIN()
-- after : VUNION over VHASH JOIN (INNER JOIN x2 + LEFT ANTI JOIN)
```
On branches where `InitJoinOrder` (#53127) still runs before `OrExpansion`
(it was removed from master's rewriter in #67067), a `LEFT OUTER JOIN` with an
OR condition can be swapped to `RIGHT OUTER JOIN` based on statistics (small
left table), so normal queries hit this and regress from hash join to nested
loop join.
This PR expands `RIGHT OUTER JOIN` as
```
inner join (split by each disjunct) UNION ALL right anti join
```
where the right anti join is built by `expandLeftAntiJoin` with swapped
producers, the same as the right-unmatched branch of `FULL OUTER JOIN`. The new
branch is placed before the generic `isOuterJoin()` branch; otherwise `RIGHT
OUTER JOIN` would be expanded as `LEFT OUTER JOIN` and return wrong results
(keeping unmatched rows of the left child instead of the right child).
### Release note
Support OR expansion for RIGHT OUTER JOIN, so that a RIGHT OUTER JOIN with
disjunctive equal conditions is planned as hash joins instead of nested loop
join.
### Check List (For Author)
- Test <!-- At least one of them must be included. -->
- [x] Regression test
- [x] Unit Test
- [x] Manual test (add detailed scripts or steps below)
- [ ] No need to test or manual test. Explain why:
- [ ] This is a refactor/code format and no logic has been changed.
- [ ] Previous test can cover this change.
- [ ] No code files have been changed.
- [ ] Other reason <!-- Add your reason? -->
- Unit test: `OrExpansionTest#testOrExpandRightOuterJoin` checks that
`OrExpansion` receives a nested loop `RIGHT OUTER JOIN`, and expands it into 2
inner joins + 1 anti join branch whose left-side columns are padded with NULL
and right-side columns are kept.
- Regression test: `query_p0/union/or_expansion` adds `RIGHT OUTER JOIN`
cases (plain / multi condition / unary condition) under `disable_join_reorder =
true`, plus an explain check (`VUNION`, no `NESTED LOOP JOIN`). Expected
results were generated with nested loop join (`OR_EXPANSION` disabled) and
cross-checked with the equivalent swapped `LEFT OUTER JOIN`.
- Manual test: on a local master build, the plan changes from `VNESTED
LOOP JOIN (RIGHT OUTER JOIN)` to `VUNION` + `VHASH JOIN`, and results match the
nested loop join results row by row.
- Behavior changed:
- [ ] No.
- [x] Yes. <!-- Explain the behavior change -->
`RIGHT OUTER JOIN` with disjunctive equal conditions is now planned as
`UNION ALL` of hash joins instead of nested loop join. Query results are
unchanged.
- Does this need documentation?
- [x] No.
- [ ] Yes. <!-- Add document PR link here. eg:
https://github.com/apache/doris-website/pull/1214 -->
### Check List (For Reviewer who merge this PR)
- [ ] Confirm the release note
- [ ] Confirm test cases
- [ ] Confirm document
- [ ] Add branch pick label <!-- Add branch pick label that this PR should
merge into -->
--
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]
