Repository navigation
Commit 8559cd8
fix: apply the join filter when a null-aware hash join marks rows UNKNOWN
A correlated `NOT IN` whose correlation is not an equi-join key leaves a
residual join filter. The null-aware hash join ignored it when deciding
whether a NULL on the subquery side makes `NOT IN` UNKNOWN, so a NULL that the
filter excludes still poisoned every outer row:
CREATE TABLE oc(id INT, g INT) AS VALUES (1,5),(2,5),(3,0),(4,NULL),(NULL,5),(NULL,0);
CREATE TABLE ic(id INT) AS VALUES (1),(NULL);
SELECT id, g FROM oc WHERE oc.id NOT IN (SELECT ic.id FROM ic WHERE oc.g > 0);
returned no rows; DuckDB and PostgreSQL return three. The plan was already
correct, `LeftAnti ... Filter: oc.g > Int32(0) null_aware`, so this is purely
an execution fix.
A NULL now makes `NOT IN` UNKNOWN only for the build rows whose correlation
scope and residual filter keep that NULL, recorded per build row in a
null-indices bitmap. Candidates come from a scope-map lookup when there are
correlation keys and from a cross product otherwise, then pass the filter. The
cost is proportional to the number of NULLs and is zero when the data has
none; build rows already marked UNKNOWN are skipped.
Null-aware `LeftAnti` also accepts more than one join key now, which the
equality-correlated shape needs; `RightAnti` still requires exactly one.
Flips the affected expectations from the preceding commit, and the Q05-Q07
correctness canaries.
Closes #25336
Co-Authored-By: Claude Opus 5 <[email protected]>1 parent fc9f146 commit 8559cd8
7 files changed
Lines changed: 568 additions & 218 deletions
File tree
- benchmarks/sql_benchmarks/null_aware_join/benchmarks
- datafusion
- physical-optimizer/src
- physical-plan/src/joins/hash_join
- sqllogictest/test_files
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
15 | 15 | | |
16 | 16 | | |
17 | 17 | | |
18 | | - | |
19 | | - | |
20 | | - | |
| 18 | + | |
21 | 19 | | |
22 | 20 | | |
23 | 21 | | |
| |||
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
15 | 15 | | |
16 | 16 | | |
17 | 17 | | |
18 | | - | |
19 | | - | |
20 | | - | |
| 18 | + | |
21 | 19 | | |
22 | 20 | | |
23 | 21 | | |
| |||
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
17 | 17 | | |
18 | 18 | | |
19 | 19 | | |
20 | | - | |
21 | | - | |
22 | | - | |
| 20 | + | |
23 | 21 | | |
24 | 22 | | |
25 | 23 | | |
| |||
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
172 | 172 | | |
173 | 173 | | |
174 | 174 | | |
175 | | - | |
| 175 | + | |
| 176 | + | |
176 | 177 | | |
177 | 178 | | |
178 | 179 | | |
179 | 180 | | |
| 181 | + | |
180 | 182 | | |
181 | 183 | | |
182 | 184 | | |
| |||
0 commit comments