Repository navigation
Commit 94a1d85
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
- datafusion
- optimizer/src
- sqllogictest/test_files
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
23 | 23 | | |
24 | 24 | | |
25 | 25 | | |
26 | | - | |
27 | | - | |
28 | | - | |
| 26 | + | |
29 | 27 | | |
30 | 28 | | |
| 29 | + | |
31 | 30 | | |
32 | 31 | | |
33 | 32 | | |
| |||
0 commit comments