Skip to content

Commit 416696d

Browse files
committed
test(smj): Cover null-aware NOT IN semantics
Add SQL regression coverage for null-aware sort-merge joins when prefer_hash_join is false. Verify that an empty RHS preserves NULL outer keys and duplicate rows, an RHS containing NULL suppresses all rows, and a nonempty RHS without NULL excludes NULL and matched outer keys. Use a four-partition RHS to verify that NULL detection covers the entire input. Assert null-aware SMJ plans with target_partitions set to 1 or 4 and with join repartitioning enabled or disabled. Verify that unsupported correlated anti joins retain the CollectLeft HashJoin fallback and that null-aware mark joins preserve UNKNOWN results when hash joins are not preferred. Update the related test comments. Refs apache#25546
1 parent bf5690d commit 416696d

3 files changed

Lines changed: 223 additions & 11 deletions

File tree

‎datafusion/sqllogictest/test_files/null_aware_anti_join.slt‎

Lines changed: 21 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -624,8 +624,8 @@ query I
624624
SELECT id FROM nai_outer WHERE id > 5 AND id NOT IN (SELECT id FROM nai_inner) ORDER BY id;
625625
----
626626

627-
# Same query under SortMergeJoin + multiple partitions: null-aware joins must
628-
# be planned as a CollectLeft HashJoin, not a plain anti SortMergeJoin.
627+
# When hash joins are not preferred, the supported uncorrelated case uses a null-aware
628+
# SortMergeJoin, which must still preserve the subquery NULL.
629629
statement ok
630630
SET datafusion.optimizer.prefer_hash_join = false;
631631

@@ -784,6 +784,22 @@ NULL
784784
# The projected value column must be the FIRST equi-join key: a null-aware hash
785785
# join reads `on[0]` as the `NOT IN` value key and `on[1..]` as correlation
786786
# scope keys, positionally. Giving the correlation that slot returns wrong rows.
787+
# Correlated null-aware joins must keep the CollectLeft HashJoin fallback even
788+
# when hash joins are not preferred: the single-key SMJ path cannot handle scopes.
789+
statement ok
790+
SET datafusion.optimizer.prefer_hash_join = false;
791+
792+
query I
793+
SELECT z FROM naconst_corr_t3 WHERE 1 NOT IN (SELECT naconst_corr_t4.id FROM naconst_corr_t4 WHERE naconst_corr_t4.z = naconst_corr_t3.z) ORDER BY z;
794+
----
795+
7
796+
NULL
797+
798+
query I
799+
SELECT z FROM naconst_corr_t3 WHERE 2 NOT IN (SELECT naconst_corr_t4.id FROM naconst_corr_t4 WHERE naconst_corr_t4.z = naconst_corr_t3.z) ORDER BY z;
800+
----
801+
NULL
802+
787803
query TT
788804
EXPLAIN SELECT z FROM naconst_corr_t3 WHERE 1 NOT IN (SELECT naconst_corr_t4.id FROM naconst_corr_t4 WHERE naconst_corr_t4.z = naconst_corr_t3.z);
789805
----
@@ -802,6 +818,9 @@ physical_plan
802818
04)--ProjectionExec: expr=[CAST(id@0 AS Int64) as naconst_corr_t4.id, z@1 as z]
803819
05)----DataSourceExec: partitions=1, partition_sizes=[1]
804820

821+
statement ok
822+
SET datafusion.optimizer.prefer_hash_join = true;
823+
805824
statement ok
806825
DROP TABLE naconst_corr_t3;
807826

‎datafusion/sqllogictest/test_files/null_aware_mark_join.slt‎

Lines changed: 15 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -400,8 +400,9 @@ c
400400
d
401401
e
402402

403-
# Null-aware mark joins are pinned to CollectLeft hash joins (mark joins have no
404-
# sort-merge path); flipping prefer_hash_join off must not drop null awareness.
403+
# Null-aware mark joins are pinned to CollectLeft hash joins because their
404+
# null-aware semantics have no sort-merge path. Flipping prefer_hash_join off
405+
# must not drop null awareness.
405406

406407
statement ok
407408
set datafusion.optimizer.prefer_hash_join = false;
@@ -425,6 +426,18 @@ physical_plan
425426
04)------DataSourceExec: partitions=1, partition_sizes=[1]
426427
05)------DataSourceExec: partitions=1, partition_sizes=[1]
427428

429+
# Execute under the same setting: matching keys are FALSE for NOT IN, while
430+
# unmatched keys and the NULL outer key are UNKNOWN because the RHS contains NULL.
431+
query IT rowsort
432+
SELECT id, value
433+
FROM outer_table
434+
WHERE (id NOT IN (SELECT id FROM inner_table_with_null)) IS NULL;
435+
----
436+
1 a
437+
3 c
438+
4 d
439+
NULL e
440+
428441
####################################
429442
## Nested loop mark join with NULLs
430443
####################################

‎datafusion/sqllogictest/test_files/subquery.slt‎

Lines changed: 187 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -3140,6 +3140,12 @@ set datafusion.optimizer.prefer_hash_join = false;
31403140
statement ok
31413141
CREATE TABLE nia_left(x INT) AS VALUES (1), (2), (3), (4);
31423142

3143+
statement ok
3144+
CREATE TABLE nia_left_with_null(x INT) AS VALUES (NULL), (1), (2), (3), (3), (4);
3145+
3146+
statement ok
3147+
CREATE TABLE nia_right_with_empty(y INT);
3148+
31433149
statement ok
31443150
CREATE TABLE nia_right_with_null(y INT) AS VALUES (2), (NULL);
31453151

@@ -3151,6 +3157,46 @@ query I
31513157
SELECT x FROM nia_left WHERE x NOT IN (SELECT y FROM nia_right_with_null) ORDER BY x;
31523158
----
31533159

3160+
# Subquery is empty -> NOT IN keeps every left row.
3161+
query I
3162+
SELECT x FROM nia_left WHERE x NOT IN (SELECT y FROM nia_right_with_empty) ORDER BY x;
3163+
----
3164+
1
3165+
2
3166+
3
3167+
4
3168+
3169+
# Subquery has no NULL -> NOT IN behaves like a normal anti join.
3170+
query I
3171+
SELECT x FROM nia_left WHERE x NOT IN (SELECT y FROM nia_right_no_null) ORDER BY x;
3172+
----
3173+
1
3174+
3
3175+
3176+
# A NULL in the RHS also suppresses a NULL left key.
3177+
query I
3178+
SELECT x FROM nia_left_with_null WHERE x NOT IN (SELECT y FROM nia_right_with_null) ORDER BY x;
3179+
----
3180+
3181+
# An empty RHS preserves NULL left keys and duplicate rows.
3182+
query I
3183+
SELECT x FROM nia_left_with_null WHERE x NOT IN (SELECT y FROM nia_right_with_empty) ORDER BY x NULLS FIRST;
3184+
----
3185+
NULL
3186+
1
3187+
2
3188+
3
3189+
3
3190+
4
3191+
3192+
# A nonempty RHS without NULL excludes NULL left keys but preserves duplicates.
3193+
query I
3194+
SELECT x FROM nia_left_with_null WHERE x NOT IN (SELECT y FROM nia_right_no_null) ORDER BY x;
3195+
----
3196+
1
3197+
3
3198+
3
3199+
31543200
# With prefer_hash_join = false, this single-key, unfiltered null-aware LeftAnti
31553201
# join uses SortMergeJoinExec with sorted, single-partition inputs.
31563202
query TT
@@ -3195,16 +3241,15 @@ physical_plan
31953241
20)│ rows: 4 ││ rows: 2 │
31963242
21)└───────────────────────────┘└───────────────────────────┘
31973243

3198-
# Subquery has no NULL -> NOT IN behaves like a normal anti join.
3199-
query I
3200-
SELECT x FROM nia_left WHERE x NOT IN (SELECT y FROM nia_right_no_null) ORDER BY x;
3201-
----
3202-
1
3203-
3
3204-
32053244
statement ok
32063245
DROP TABLE nia_left;
32073246

3247+
statement ok
3248+
DROP TABLE nia_left_with_null;
3249+
3250+
statement ok
3251+
DROP TABLE nia_right_with_empty;
3252+
32083253
statement ok
32093254
DROP TABLE nia_right_with_null;
32103255

@@ -3214,6 +3259,141 @@ DROP TABLE nia_right_no_null;
32143259
statement ok
32153260
reset datafusion.optimizer.prefer_hash_join;
32163261

3262+
# A NULL in one RHS partition must affect every outer row.
3263+
3264+
statement ok
3265+
SET datafusion.optimizer.prefer_hash_join = false;
3266+
3267+
statement ok
3268+
SET datafusion.execution.target_partitions = 4;
3269+
3270+
statement ok
3271+
SET datafusion.optimizer.repartition_joins = true;
3272+
3273+
statement ok
3274+
CREATE TABLE nia_mp_left(x INT) AS
3275+
VALUES (NULL), (1), (2), (3), (3);
3276+
3277+
# Each UNION ALL branch produces one partition.
3278+
# CTAS preserves the partitioned result in the memory table.
3279+
statement ok
3280+
CREATE TABLE nia_mp_right AS
3281+
SELECT CAST(10 AS INT) AS y
3282+
UNION ALL
3283+
SELECT CAST(20 AS INT) AS y
3284+
UNION ALL
3285+
SELECT CAST(30 AS INT) AS y
3286+
UNION ALL
3287+
SELECT CAST(NULL AS INT) AS y;
3288+
3289+
# Verify that the RHS really has four nonempty partitions.
3290+
# partition_sizes counts batches, not rows.
3291+
query TT
3292+
EXPLAIN SELECT y FROM nia_mp_right;
3293+
----
3294+
logical_plan TableScan: nia_mp_right projection=[y]
3295+
physical_plan DataSourceExec: partitions=4, partition_sizes=[1, 1, 1, 1]
3296+
3297+
# No left key matches 10, 20, or 30.
3298+
# The NULL in the remaining RHS partition must still suppress every row.
3299+
query I
3300+
SELECT x
3301+
FROM nia_mp_left
3302+
WHERE x NOT IN (SELECT y FROM nia_mp_right)
3303+
ORDER BY x;
3304+
----
3305+
3306+
# The optimizer must bring all RHS partitions into the null-aware join.
3307+
query TT
3308+
EXPLAIN SELECT x FROM nia_mp_left WHERE x NOT IN (SELECT y FROM nia_mp_right);
3309+
----
3310+
logical_plan
3311+
01)LeftAnti Join: nia_mp_left.x = __correlated_sq_1.y null_aware
3312+
02)--TableScan: nia_mp_left projection=[x]
3313+
03)--SubqueryAlias: __correlated_sq_1
3314+
04)----TableScan: nia_mp_right projection=[y]
3315+
physical_plan
3316+
01)SortMergeJoinExec: join_type=LeftAnti, on=[(x@0, y@0)], null_aware
3317+
02)--SortExec: expr=[x@0 ASC], preserve_partitioning=[false]
3318+
03)----DataSourceExec: partitions=1, partition_sizes=[1]
3319+
04)--SortPreservingMergeExec: [y@0 ASC]
3320+
05)----SortExec: expr=[y@0 ASC], preserve_partitioning=[true]
3321+
06)------DataSourceExec: partitions=4, partition_sizes=[1, 1, 1, 1]
3322+
3323+
# Disabling join repartitioning must preserve the same semantics.
3324+
statement ok
3325+
SET datafusion.optimizer.repartition_joins = false;
3326+
3327+
query I
3328+
SELECT x
3329+
FROM nia_mp_left
3330+
WHERE x NOT IN (SELECT y FROM nia_mp_right)
3331+
ORDER BY x;
3332+
----
3333+
3334+
# Disabling hash repartitioning must not bypass the single-partition requirement.
3335+
query TT
3336+
EXPLAIN SELECT x FROM nia_mp_left WHERE x NOT IN (SELECT y FROM nia_mp_right);
3337+
----
3338+
logical_plan
3339+
01)LeftAnti Join: nia_mp_left.x = __correlated_sq_1.y null_aware
3340+
02)--TableScan: nia_mp_left projection=[x]
3341+
03)--SubqueryAlias: __correlated_sq_1
3342+
04)----TableScan: nia_mp_right projection=[y]
3343+
physical_plan
3344+
01)SortMergeJoinExec: join_type=LeftAnti, on=[(x@0, y@0)], null_aware
3345+
02)--SortExec: expr=[x@0 ASC], preserve_partitioning=[false]
3346+
03)----DataSourceExec: partitions=1, partition_sizes=[1]
3347+
04)--SortPreservingMergeExec: [y@0 ASC]
3348+
05)----SortExec: expr=[y@0 ASC], preserve_partitioning=[true]
3349+
06)------DataSourceExec: partitions=4, partition_sizes=[1, 1, 1, 1]
3350+
3351+
# Requesting one target partition must also preserve the result.
3352+
statement ok
3353+
SET datafusion.execution.target_partitions = 1;
3354+
3355+
query I
3356+
SELECT x
3357+
FROM nia_mp_left
3358+
WHERE x NOT IN (SELECT y FROM nia_mp_right)
3359+
ORDER BY x;
3360+
----
3361+
3362+
query TT
3363+
EXPLAIN
3364+
SELECT x
3365+
FROM nia_mp_left
3366+
WHERE x NOT IN (SELECT y FROM nia_mp_right);
3367+
----
3368+
logical_plan
3369+
01)LeftAnti Join: nia_mp_left.x = __correlated_sq_1.y null_aware
3370+
02)--TableScan: nia_mp_left projection=[x]
3371+
03)--SubqueryAlias: __correlated_sq_1
3372+
04)----TableScan: nia_mp_right projection=[y]
3373+
physical_plan
3374+
01)SortMergeJoinExec: join_type=LeftAnti, on=[(x@0, y@0)], null_aware
3375+
02)--SortExec: expr=[x@0 ASC], preserve_partitioning=[false]
3376+
03)----DataSourceExec: partitions=1, partition_sizes=[1]
3377+
04)--SortPreservingMergeExec: [y@0 ASC]
3378+
05)----SortExec: expr=[y@0 ASC], preserve_partitioning=[true]
3379+
06)------DataSourceExec: partitions=4, partition_sizes=[1, 1, 1, 1]
3380+
3381+
statement ok
3382+
DROP TABLE nia_mp_left;
3383+
3384+
statement ok
3385+
DROP TABLE nia_mp_right;
3386+
3387+
statement ok
3388+
RESET datafusion.optimizer.prefer_hash_join;
3389+
3390+
statement ok
3391+
RESET datafusion.optimizer.repartition_joins;
3392+
3393+
# Restore the SLT runner's target partition count.
3394+
statement ok
3395+
SET datafusion.execution.target_partitions = 4;
3396+
32173397
# Regression test for #24139: CTE inside a correlated scalar subquery causing a schema error
32183398
statement ok
32193399
CREATE TABLE metrics (host VARCHAR, bytes_sent DOUBLE, ts BIGINT) AS VALUES

0 commit comments

Comments
 (0)