Skip to content

Commit 94a1d85

Browse files
adriangbclaude
authored andcommitted
fix: request a null-aware mark join wherever a NULL mark is observable (apache#25560)
## Which issue does this PR close? - Completes apache#25336 for the mark-join shapes. > [!NOTE] > The executor fix landed in apache#25339 and the tests in apache#25558. This PR is the last part of that split. > > This PR fixes the review threads from apache#25339 on `decorrelate_predicate_subquery.rs` ([plain `IN`](apache#25339 (comment)), [`InSubquery` value](apache#25339 (comment))), the [`on[0]` thread](apache#25339 (comment)) and the [constant-projection comment](apache#25339 (comment)). ## Rationale for this change Decorrelation asked for null-aware semantics only for some `NOT IN` mark joins. A non-equality correlation leaves a residual join filter rather than an equi-join key, and that shape was planned without it, so a NULL on the subquery side read as FALSE instead of UNKNOWN: ```sql CREATE TABLE oc(id INT, g INT, h INT) AS VALUES (2,2,0),(1,2,0),(9,2,5),(3,1,0); CREATE TABLE ic(id INT, k INT) AS VALUES (1,1),(NULL,1),(2,2); SELECT id FROM oc WHERE oc.h > 1 OR oc.id NOT IN (SELECT i.id FROM ic i WHERE i.k < oc.g) ORDER BY id; ``` returns `2, 3, 9`; DuckDB returns `3, 9`. Row `id = 2` is UNKNOWN, not TRUE. apache#25339 gives the executor the machinery. This asks for it in the remaining place. ## What changes are included in this PR? A mark join needs null-aware semantics only where a NULL mark can behave differently from a FALSE mark. `AND`/`OR` give TRUE only from TRUE, and a `Filter` keeps a row only when the predicate is TRUE, so a non-negated `IN` reached through nothing but `AND`/`OR` is identical with a FALSE mark and must stay on the plain join. Requesting it there is expensive: **1–2 ms → 7.4 s at 100k × 100k**. That test is made **per subquery occurrence**, in a recursion that only knows `AND`/`OR`. The permissive outcome lives in one arm whose pattern is its own proof — a non-negated `IN` whose value holds no subquery, reached through nothing but `AND`/`OR` frames — and everything else, including future `Expr` variants, takes the null-aware branch. Two further changes in `build_join`: - a constant `IN` value is projected as an outer column so the equality becomes `on[0]`, the key position the executor reads as the `NOT IN` value key. Otherwise a correlation takes that slot and the value-key NULL rules are applied to the wrong key; - `null_aware` is computed once instead of being re-derived for the constant projection, the mark branch and the anti branch. Net effect on the optimizer source is **−14 lines**. ## What is the testing strategy for this PR? The coverage landed in apache#25558; this PR flips the remaining expectations, including the Q08 canary, so the diff shows the behaviour change. It also removes one note that apache#25558 put on the wrong query in `subquery.slt` (`#simple_uncorrelated_scalar_subquery2`, which no fix changes). The plan pins added in apache#25558 guard the *negative* direction — that a positive `IN` stays on the plain join — which no result assertion can catch, since a needless null-aware join is correct, only slower. Mutation testing over the decision confirms it: mutants that widen null-awareness are killed only by those pins. ### Benchmarks Results against the apache#25339 merge: [`null_aware_join`](apache#25560 (comment)), [`projection_subquery`](apache#25560 (comment)), [`tpcds`](apache#25560 (comment)). - `tpcds`, `projection_subquery`, and `null_aware_join` Q01–Q07 and Q09 do not change. Q09 is the positive `IN` that must stay on the plain join. - `null_aware_join` Q08 goes from 0.99 ms to 20.6 ms. This is not a regression: on `main`, Q08 returns an incorrect result (its correctness canary is `false`), so 0.99 ms is the time to calculate a wrong answer. 20.6 ms is the cost of the correct result, and it is the same as the combined apache#25339 measured (21.2 ms). - The remaining Q08 cost comes from the scope-key path in the executor, not from this PR. The [DuckDB comparison on apache#25339](apache#25339 (comment)) explains it, and apache#25438 tracks the fix. ## Are there any user-facing changes? Correlated `NOT IN` inside a larger predicate returns correct results. 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-authored-by: Adrian Garcia Badaracco <[email protected]> Co-authored-by: Claude Opus 5 <[email protected]>
1 parent 39f2622 commit 94a1d85

5 files changed

Lines changed: 192 additions & 219 deletions

File tree

‎benchmarks/sql_benchmarks/null_aware_join/benchmarks/q08.benchmark‎

Lines changed: 2 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -23,11 +23,10 @@ WHERE o.z > 900
2323
SELECT i.id_n0 FROM small_inner i WHERE i.k = o.k AND i.z < o.z
2424
);
2525
----
26-
# Pinned to today's behaviour, which is incorrect. See
27-
# https://github.com/apache/datafusion/issues/25336 -- the fix flips this.
28-
false
26+
true
2927

3028
expect_plan HashJoinExec
29+
expect_plan null_aware
3130

3231
run
3332
-- Q8: NOT IN correlated by both an equality and a non-equality, 50% NULL on

0 commit comments

Comments
 (0)