Skip to content

Commit d361e60

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 013b79b commit d361e60

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
@@ -611,8 +611,8 @@ query I
611611
SELECT id FROM nai_outer WHERE id > 5 AND id NOT IN (SELECT id FROM nai_inner) ORDER BY id;
612612
----
613613

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

@@ -771,6 +771,22 @@ NULL
771771
# The projected value column must be the FIRST equi-join key: a null-aware hash
772772
# join reads `on[0]` as the `NOT IN` value key and `on[1..]` as correlation
773773
# scope keys, positionally. Giving the correlation that slot returns wrong rows.
774+
# Correlated null-aware joins must keep the CollectLeft HashJoin fallback even
775+
# when hash joins are not preferred: the single-key SMJ path cannot handle scopes.
776+
statement ok
777+
SET datafusion.optimizer.prefer_hash_join = false;
778+
779+
query I
780+
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;
781+
----
782+
7
783+
NULL
784+
785+
query I
786+
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;
787+
----
788+
NULL
789+
774790
query TT
775791
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);
776792
----
@@ -789,6 +805,9 @@ physical_plan
789805
04)--ProjectionExec: expr=[CAST(id@0 AS Int64) as naconst_corr_t4.id, z@1 as z]
790806
05)----DataSourceExec: partitions=1, partition_sizes=[1]
791807

808+
statement ok
809+
SET datafusion.optimizer.prefer_hash_join = true;
810+
792811
statement ok
793812
DROP TABLE naconst_corr_t3;
794813

‎datafusion/sqllogictest/test_files/null_aware_mark_join.slt‎

Lines changed: 15 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -387,8 +387,9 @@ c
387387
d
388388
e
389389

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

393394
statement ok
394395
set datafusion.optimizer.prefer_hash_join = false;
@@ -412,6 +413,18 @@ physical_plan
412413
04)------DataSourceExec: partitions=1, partition_sizes=[1]
413414
05)------DataSourceExec: partitions=1, partition_sizes=[1]
414415

416+
# Execute under the same setting: matching keys are FALSE for NOT IN, while
417+
# unmatched keys and the NULL outer key are UNKNOWN because the RHS contains NULL.
418+
query IT rowsort
419+
SELECT id, value
420+
FROM outer_table
421+
WHERE (id NOT IN (SELECT id FROM inner_table_with_null)) IS NULL;
422+
----
423+
1 a
424+
3 c
425+
4 d
426+
NULL e
427+
415428
####################################
416429
## Nested loop mark join with NULLs
417430
####################################

‎datafusion/sqllogictest/test_files/subquery.slt‎

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

3130+
statement ok
3131+
CREATE TABLE nia_left_with_null(x INT) AS VALUES (NULL), (1), (2), (3), (3), (4);
3132+
3133+
statement ok
3134+
CREATE TABLE nia_right_with_empty(y INT);
3135+
31303136
statement ok
31313137
CREATE TABLE nia_right_with_null(y INT) AS VALUES (2), (NULL);
31323138

@@ -3138,6 +3144,46 @@ query I
31383144
SELECT x FROM nia_left WHERE x NOT IN (SELECT y FROM nia_right_with_null) ORDER BY x;
31393145
----
31403146

3147+
# Subquery is empty -> NOT IN keeps every left row.
3148+
query I
3149+
SELECT x FROM nia_left WHERE x NOT IN (SELECT y FROM nia_right_with_empty) ORDER BY x;
3150+
----
3151+
1
3152+
2
3153+
3
3154+
4
3155+
3156+
# Subquery has no NULL -> NOT IN behaves like a normal anti join.
3157+
query I
3158+
SELECT x FROM nia_left WHERE x NOT IN (SELECT y FROM nia_right_no_null) ORDER BY x;
3159+
----
3160+
1
3161+
3
3162+
3163+
# A NULL in the RHS also suppresses a NULL left key.
3164+
query I
3165+
SELECT x FROM nia_left_with_null WHERE x NOT IN (SELECT y FROM nia_right_with_null) ORDER BY x;
3166+
----
3167+
3168+
# An empty RHS preserves NULL left keys and duplicate rows.
3169+
query I
3170+
SELECT x FROM nia_left_with_null WHERE x NOT IN (SELECT y FROM nia_right_with_empty) ORDER BY x NULLS FIRST;
3171+
----
3172+
NULL
3173+
1
3174+
2
3175+
3
3176+
3
3177+
4
3178+
3179+
# A nonempty RHS without NULL excludes NULL left keys but preserves duplicates.
3180+
query I
3181+
SELECT x FROM nia_left_with_null WHERE x NOT IN (SELECT y FROM nia_right_no_null) ORDER BY x;
3182+
----
3183+
1
3184+
3
3185+
3
3186+
31413187
# With prefer_hash_join = false, this single-key, unfiltered null-aware LeftAnti
31423188
# join uses SortMergeJoinExec with sorted, single-partition inputs.
31433189
query TT
@@ -3182,16 +3228,15 @@ physical_plan
31823228
20)│ rows: 4 ││ rows: 2 │
31833229
21)└───────────────────────────┘└───────────────────────────┘
31843230

3185-
# Subquery has no NULL -> NOT IN behaves like a normal anti join.
3186-
query I
3187-
SELECT x FROM nia_left WHERE x NOT IN (SELECT y FROM nia_right_no_null) ORDER BY x;
3188-
----
3189-
1
3190-
3
3191-
31923231
statement ok
31933232
DROP TABLE nia_left;
31943233

3234+
statement ok
3235+
DROP TABLE nia_left_with_null;
3236+
3237+
statement ok
3238+
DROP TABLE nia_right_with_empty;
3239+
31953240
statement ok
31963241
DROP TABLE nia_right_with_null;
31973242

@@ -3201,6 +3246,141 @@ DROP TABLE nia_right_no_null;
32013246
statement ok
32023247
reset datafusion.optimizer.prefer_hash_join;
32033248

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

0 commit comments

Comments
 (0)