Skip to content

Commit 67c2051

Browse files
nathanb9lukekim
authored andcommitted
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

File tree

‎datafusion/core/src/physical_planner.rs‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1737,6 +1737,11 @@ impl DefaultPhysicalPlanner {
17371737
} else if session_state.config().target_partitions() > 1
17381738
&& session_state.config().repartition_joins()
17391739
&& !prefer_hash_join
1740+
&& !*null_aware
1741+
// Null-aware joins (e.g. `NOT IN` with a nullable subquery) must
1742+
// use the CollectLeft HashJoin below: SortMergeJoinExec does not
1743+
// implement null-aware anti-join semantics and would return wrong
1744+
// results when the right side contains a null join key.
17401745
{
17411746
// Use SortMergeJoin if hash join is not preferred
17421747
let join_on_len = join_on.len();

‎datafusion/sqllogictest/test_files/subquery.slt‎

Lines changed: 58 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2394,3 +2394,61 @@ DROP TABLE sq_count_customer;
23942394

23952395
statement ok
23962396
DROP TABLE sq_count_orders;
2397+
2398+
# Regression test: `NOT IN` is a null-aware anti join. When the subquery yields a
2399+
# NULL the predicate is never TRUE, so the query must return zero rows. This must
2400+
# hold regardless of the chosen physical join operator. Previously, with
2401+
# prefer_hash_join = false and multiple partitions, the planner routed the
2402+
# null-aware anti join to SortMergeJoin (which is not null-aware) and returned
2403+
# wrong results; null-aware anti joins must use the CollectLeft HashJoin.
2404+
2405+
statement ok
2406+
set datafusion.optimizer.prefer_hash_join = false;
2407+
2408+
statement ok
2409+
CREATE TABLE nia_left(x INT) AS VALUES (1), (2), (3), (4);
2410+
2411+
statement ok
2412+
CREATE TABLE nia_right_with_null(y INT) AS VALUES (2), (NULL);
2413+
2414+
statement ok
2415+
CREATE TABLE nia_right_no_null(y INT) AS VALUES (2), (4);
2416+
2417+
# Subquery contains a NULL -> NOT IN must return no rows.
2418+
query I
2419+
SELECT x FROM nia_left WHERE x NOT IN (SELECT y FROM nia_right_with_null) ORDER BY x;
2420+
----
2421+
2422+
# The null-aware anti join must be planned as a CollectLeft HashJoinExec even with
2423+
# prefer_hash_join = false: SortMergeJoinExec is not null-aware and must not be used.
2424+
query TT
2425+
EXPLAIN SELECT x FROM nia_left WHERE x NOT IN (SELECT y FROM nia_right_with_null);
2426+
----
2427+
logical_plan
2428+
01)LeftAnti Join: nia_left.x = __correlated_sq_1.y null_aware
2429+
02)--TableScan: nia_left projection=[x]
2430+
03)--SubqueryAlias: __correlated_sq_1
2431+
04)----TableScan: nia_right_with_null projection=[y]
2432+
physical_plan
2433+
01)HashJoinExec: mode=CollectLeft, join_type=LeftAnti, on=[(x@0, y@0)], null_aware
2434+
02)--DataSourceExec: partitions=1, partition_sizes=[1]
2435+
03)--DataSourceExec: partitions=1, partition_sizes=[1]
2436+
2437+
# Subquery has no NULL -> NOT IN behaves like a normal anti join.
2438+
query I
2439+
SELECT x FROM nia_left WHERE x NOT IN (SELECT y FROM nia_right_no_null) ORDER BY x;
2440+
----
2441+
1
2442+
3
2443+
2444+
statement ok
2445+
DROP TABLE nia_left;
2446+
2447+
statement ok
2448+
DROP TABLE nia_right_with_null;
2449+
2450+
statement ok
2451+
DROP TABLE nia_right_no_null;
2452+
2453+
statement ok
2454+
reset datafusion.optimizer.prefer_hash_join;

0 commit comments

Comments
 (0)