Skip to content

Commit 25351db

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 b179fd6 commit 25351db

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
@@ -3196,6 +3196,12 @@ set datafusion.optimizer.prefer_hash_join = false;
31963196
statement ok
31973197
CREATE TABLE nia_left(x INT) AS VALUES (1), (2), (3), (4);
31983198

3199+
statement ok
3200+
CREATE TABLE nia_left_with_null(x INT) AS VALUES (NULL), (1), (2), (3), (3), (4);
3201+
3202+
statement ok
3203+
CREATE TABLE nia_right_with_empty(y INT);
3204+
31993205
statement ok
32003206
CREATE TABLE nia_right_with_null(y INT) AS VALUES (2), (NULL);
32013207

@@ -3207,6 +3213,46 @@ query I
32073213
SELECT x FROM nia_left WHERE x NOT IN (SELECT y FROM nia_right_with_null) ORDER BY x;
32083214
----
32093215

3216+
# Subquery is empty -> NOT IN keeps every left row.
3217+
query I
3218+
SELECT x FROM nia_left WHERE x NOT IN (SELECT y FROM nia_right_with_empty) ORDER BY x;
3219+
----
3220+
1
3221+
2
3222+
3
3223+
4
3224+
3225+
# Subquery has no NULL -> NOT IN behaves like a normal anti join.
3226+
query I
3227+
SELECT x FROM nia_left WHERE x NOT IN (SELECT y FROM nia_right_no_null) ORDER BY x;
3228+
----
3229+
1
3230+
3
3231+
3232+
# A NULL in the RHS also suppresses a NULL left key.
3233+
query I
3234+
SELECT x FROM nia_left_with_null WHERE x NOT IN (SELECT y FROM nia_right_with_null) ORDER BY x;
3235+
----
3236+
3237+
# An empty RHS preserves NULL left keys and duplicate rows.
3238+
query I
3239+
SELECT x FROM nia_left_with_null WHERE x NOT IN (SELECT y FROM nia_right_with_empty) ORDER BY x NULLS FIRST;
3240+
----
3241+
NULL
3242+
1
3243+
2
3244+
3
3245+
3
3246+
4
3247+
3248+
# A nonempty RHS without NULL excludes NULL left keys but preserves duplicates.
3249+
query I
3250+
SELECT x FROM nia_left_with_null WHERE x NOT IN (SELECT y FROM nia_right_no_null) ORDER BY x;
3251+
----
3252+
1
3253+
3
3254+
3
3255+
32103256
# With prefer_hash_join = false, this single-key, unfiltered null-aware LeftAnti
32113257
# join uses SortMergeJoinExec with sorted, single-partition inputs.
32123258
query TT
@@ -3251,16 +3297,15 @@ physical_plan
32513297
20)│ rows: 4 ││ rows: 2 │
32523298
21)└───────────────────────────┘└───────────────────────────┘
32533299

3254-
# Subquery has no NULL -> NOT IN behaves like a normal anti join.
3255-
query I
3256-
SELECT x FROM nia_left WHERE x NOT IN (SELECT y FROM nia_right_no_null) ORDER BY x;
3257-
----
3258-
1
3259-
3
3260-
32613300
statement ok
32623301
DROP TABLE nia_left;
32633302

3303+
statement ok
3304+
DROP TABLE nia_left_with_null;
3305+
3306+
statement ok
3307+
DROP TABLE nia_right_with_empty;
3308+
32643309
statement ok
32653310
DROP TABLE nia_right_with_null;
32663311

@@ -3270,6 +3315,141 @@ DROP TABLE nia_right_no_null;
32703315
statement ok
32713316
reset datafusion.optimizer.prefer_hash_join;
32723317

3318+
# A NULL in one RHS partition must affect every outer row.
3319+
3320+
statement ok
3321+
SET datafusion.optimizer.prefer_hash_join = false;
3322+
3323+
statement ok
3324+
SET datafusion.execution.target_partitions = 4;
3325+
3326+
statement ok
3327+
SET datafusion.optimizer.repartition_joins = true;
3328+
3329+
statement ok
3330+
CREATE TABLE nia_mp_left(x INT) AS
3331+
VALUES (NULL), (1), (2), (3), (3);
3332+
3333+
# Each UNION ALL branch produces one partition.
3334+
# CTAS preserves the partitioned result in the memory table.
3335+
statement ok
3336+
CREATE TABLE nia_mp_right AS
3337+
SELECT CAST(10 AS INT) AS y
3338+
UNION ALL
3339+
SELECT CAST(20 AS INT) AS y
3340+
UNION ALL
3341+
SELECT CAST(30 AS INT) AS y
3342+
UNION ALL
3343+
SELECT CAST(NULL AS INT) AS y;
3344+
3345+
# Verify that the RHS really has four nonempty partitions.
3346+
# partition_sizes counts batches, not rows.
3347+
query TT
3348+
EXPLAIN SELECT y FROM nia_mp_right;
3349+
----
3350+
logical_plan TableScan: nia_mp_right projection=[y]
3351+
physical_plan DataSourceExec: partitions=4, partition_sizes=[1, 1, 1, 1]
3352+
3353+
# No left key matches 10, 20, or 30.
3354+
# The NULL in the remaining RHS partition must still suppress every row.
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+
# The optimizer must bring all RHS partitions into the null-aware join.
3363+
query TT
3364+
EXPLAIN SELECT x FROM nia_mp_left WHERE x NOT IN (SELECT y FROM nia_mp_right);
3365+
----
3366+
logical_plan
3367+
01)LeftAnti Join: nia_mp_left.x = __correlated_sq_1.y null_aware
3368+
02)--TableScan: nia_mp_left projection=[x]
3369+
03)--SubqueryAlias: __correlated_sq_1
3370+
04)----TableScan: nia_mp_right projection=[y]
3371+
physical_plan
3372+
01)SortMergeJoinExec: join_type=LeftAnti, on=[(x@0, y@0)], null_aware
3373+
02)--SortExec: expr=[x@0 ASC], preserve_partitioning=[false]
3374+
03)----DataSourceExec: partitions=1, partition_sizes=[1]
3375+
04)--SortPreservingMergeExec: [y@0 ASC]
3376+
05)----SortExec: expr=[y@0 ASC], preserve_partitioning=[true]
3377+
06)------DataSourceExec: partitions=4, partition_sizes=[1, 1, 1, 1]
3378+
3379+
# Disabling join repartitioning must preserve the same semantics.
3380+
statement ok
3381+
SET datafusion.optimizer.repartition_joins = false;
3382+
3383+
query I
3384+
SELECT x
3385+
FROM nia_mp_left
3386+
WHERE x NOT IN (SELECT y FROM nia_mp_right)
3387+
ORDER BY x;
3388+
----
3389+
3390+
# Disabling hash repartitioning must not bypass the single-partition requirement.
3391+
query TT
3392+
EXPLAIN SELECT x FROM nia_mp_left WHERE x NOT IN (SELECT y FROM nia_mp_right);
3393+
----
3394+
logical_plan
3395+
01)LeftAnti Join: nia_mp_left.x = __correlated_sq_1.y null_aware
3396+
02)--TableScan: nia_mp_left projection=[x]
3397+
03)--SubqueryAlias: __correlated_sq_1
3398+
04)----TableScan: nia_mp_right projection=[y]
3399+
physical_plan
3400+
01)SortMergeJoinExec: join_type=LeftAnti, on=[(x@0, y@0)], null_aware
3401+
02)--SortExec: expr=[x@0 ASC], preserve_partitioning=[false]
3402+
03)----DataSourceExec: partitions=1, partition_sizes=[1]
3403+
04)--SortPreservingMergeExec: [y@0 ASC]
3404+
05)----SortExec: expr=[y@0 ASC], preserve_partitioning=[true]
3405+
06)------DataSourceExec: partitions=4, partition_sizes=[1, 1, 1, 1]
3406+
3407+
# Requesting one target partition must also preserve the result.
3408+
statement ok
3409+
SET datafusion.execution.target_partitions = 1;
3410+
3411+
query I
3412+
SELECT x
3413+
FROM nia_mp_left
3414+
WHERE x NOT IN (SELECT y FROM nia_mp_right)
3415+
ORDER BY x;
3416+
----
3417+
3418+
query TT
3419+
EXPLAIN
3420+
SELECT x
3421+
FROM nia_mp_left
3422+
WHERE x NOT IN (SELECT y FROM nia_mp_right);
3423+
----
3424+
logical_plan
3425+
01)LeftAnti Join: nia_mp_left.x = __correlated_sq_1.y null_aware
3426+
02)--TableScan: nia_mp_left projection=[x]
3427+
03)--SubqueryAlias: __correlated_sq_1
3428+
04)----TableScan: nia_mp_right projection=[y]
3429+
physical_plan
3430+
01)SortMergeJoinExec: join_type=LeftAnti, on=[(x@0, y@0)], null_aware
3431+
02)--SortExec: expr=[x@0 ASC], preserve_partitioning=[false]
3432+
03)----DataSourceExec: partitions=1, partition_sizes=[1]
3433+
04)--SortPreservingMergeExec: [y@0 ASC]
3434+
05)----SortExec: expr=[y@0 ASC], preserve_partitioning=[true]
3435+
06)------DataSourceExec: partitions=4, partition_sizes=[1, 1, 1, 1]
3436+
3437+
statement ok
3438+
DROP TABLE nia_mp_left;
3439+
3440+
statement ok
3441+
DROP TABLE nia_mp_right;
3442+
3443+
statement ok
3444+
RESET datafusion.optimizer.prefer_hash_join;
3445+
3446+
statement ok
3447+
RESET datafusion.optimizer.repartition_joins;
3448+
3449+
# Restore the SLT runner's target partition count.
3450+
statement ok
3451+
SET datafusion.execution.target_partitions = 4;
3452+
32733453
# Regression test for #24139: CTE inside a correlated scalar subquery causing a schema error
32743454
statement ok
32753455
CREATE TABLE metrics (host VARCHAR, bytes_sent DOUBLE, ts BIGINT) AS VALUES

0 commit comments

Comments
 (0)