Proper handling for RIGHT OUTER and FULL OUTER joins
- Dominant language
- Java
- Stars
- 14.1k
- Forks
- 3.8k
- Avg merge
- 2d 58m
- Merged PRs (30d)
- 233
Description
#8728 adds low-level support for building join cursors, but there is a problem with the design when it comes to RIGHT OUTER and FULL OUTER joins. The set of matching left-hand rows is currently computed per segment, but it should be computed globally. Doing it per-segment leads to two problems:
1. If left-hand-side segment A contains a row that matches right-hand side row X, but segment B doesn't, then segment B's join cursor will emit a row for X with nulls on the left-hand columns. This shouldn't happen, since row X _did_ match a left-hand row (from segment A). It means there will be "extra" rows.
2. If there are no segments on the left-hand side at all (an empty table) then there will be no rows generated at all. But we should really generate one row for every row on the right-hand side.
This behavior is still somewhat useful — the extra rows from (1) will not affect the results of many believable queries [*], and the empty-left-table scenario from (2) is an edge case — but it is not correct, so we need to fix it.
[*] Imagine a query like `SELECT countries.countryName, SUM(revenue) FROM sales FULL OUTER JOIN countries ON sales.countryCode = countries.countryCode GROUP BY countries.countryName`.
Contributor guide
Research direction
Start with issue #8728 and the low-level join-cursor implementation it introduced. Reproduce RIGHT OUTER and FULL OUTER joins with multiple left-hand segments and with an empty left table; done means matching left-hand rows are computed globally, without extra rows, and right-hand rows still appear when the left side is empty.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- java
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100