Repository navigation
Commit 67c2051
fix: NOT IN with NULL subquery returns wrong results under SortMergeJoin (apache#22810)
## Problem
`NOT IN (subquery)` is a null-aware anti join: when the subquery yields
a NULL the predicate is never TRUE, so the query must return zero rows.
With `prefer_hash_join = false` and multiple partitions, the planner
routed the null-aware anti join to `SortMergeJoinExec`, which is not
null-aware, so it returned wrong results. HashJoin (the default) was
already correct.
## Proof
```sql
set datafusion.optimizer.prefer_hash_join = false;
create table t1(x int) as values (1);
create table t2(y int) as values (NULL);
select x from t1 where x not in (select y from t2);
```
Expected 0 rows (the subquery contains a NULL). Before this change it
returned `1`. With `prefer_hash_join = true` it correctly returned 0
rows. `EXPLAIN` showed the wrong config selecting `SortMergeJoinExec:
join_type=LeftAnti`.
## Solution
The planner already requires null-aware joins to use the CollectLeft
HashJoin, and the HashJoin branch guards on `!null_aware`. The
SortMergeJoin branch was missing the same guard, so this adds `&&
!*null_aware` to it. Null-aware anti joins now fall through to the
CollectLeft HashJoin regardless of `prefer_hash_join`.
`SortMergeJoinExec` has no `null_aware` parameter and cannot honor these
semantics.
Added a regression test in `subquery.slt` (under `prefer_hash_join =
false`) covering both a null-containing subquery (zero rows) and a
null-free subquery (normal anti join). All 61 SortMergeJoin unit tests
pass.
(cherry picked from commit 82f1b36)1 parent 30538e6 commit 67c2051
2 files changed
Lines changed: 63 additions & 0 deletions
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
1737 | 1737 | | |
1738 | 1738 | | |
1739 | 1739 | | |
| 1740 | + | |
| 1741 | + | |
| 1742 | + | |
| 1743 | + | |
| 1744 | + | |
1740 | 1745 | | |
1741 | 1746 | | |
1742 | 1747 | | |
| |||
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
2394 | 2394 | | |
2395 | 2395 | | |
2396 | 2396 | | |
| 2397 | + | |
| 2398 | + | |
| 2399 | + | |
| 2400 | + | |
| 2401 | + | |
| 2402 | + | |
| 2403 | + | |
| 2404 | + | |
| 2405 | + | |
| 2406 | + | |
| 2407 | + | |
| 2408 | + | |
| 2409 | + | |
| 2410 | + | |
| 2411 | + | |
| 2412 | + | |
| 2413 | + | |
| 2414 | + | |
| 2415 | + | |
| 2416 | + | |
| 2417 | + | |
| 2418 | + | |
| 2419 | + | |
| 2420 | + | |
| 2421 | + | |
| 2422 | + | |
| 2423 | + | |
| 2424 | + | |
| 2425 | + | |
| 2426 | + | |
| 2427 | + | |
| 2428 | + | |
| 2429 | + | |
| 2430 | + | |
| 2431 | + | |
| 2432 | + | |
| 2433 | + | |
| 2434 | + | |
| 2435 | + | |
| 2436 | + | |
| 2437 | + | |
| 2438 | + | |
| 2439 | + | |
| 2440 | + | |
| 2441 | + | |
| 2442 | + | |
| 2443 | + | |
| 2444 | + | |
| 2445 | + | |
| 2446 | + | |
| 2447 | + | |
| 2448 | + | |
| 2449 | + | |
| 2450 | + | |
| 2451 | + | |
| 2452 | + | |
| 2453 | + | |
| 2454 | + | |
0 commit comments