apache / apache/druid

Proper handling for RIGHT OUTER and FULL OUTER joins

Open
#9,229 0 comments 4 reactions 0 assignees View on GitHub
Area - Querying
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.