Skip to content

Commit 6c7c6d0

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 e720e6c commit 6c7c6d0

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
@@ -622,8 +622,8 @@ query I
622622
SELECT id FROM nai_outer WHERE id > 5 AND id NOT IN (SELECT id FROM nai_inner) ORDER BY id;
623623
----
624624

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

@@ -782,6 +782,22 @@ NULL
782782
# The projected value column must be the FIRST equi-join key: a null-aware hash
783783
# join reads `on[0]` as the `NOT IN` value key and `on[1..]` as correlation
784784
# scope keys, positionally. Giving the correlation that slot returns wrong rows.
785+
# Correlated null-aware joins must keep the CollectLeft HashJoin fallback even
786+
# when hash joins are not preferred: the single-key SMJ path cannot handle scopes.
787+
statement ok
788+
SET datafusion.optimizer.prefer_hash_join = false;
789+
790+
query I
791+
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;
792+
----
793+
7
794+
NULL
795+
796+
query I
797+
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;
798+
----
799+
NULL
800+
785801
query TT
786802
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);
787803
----
@@ -800,6 +816,9 @@ physical_plan
800816
04)--ProjectionExec: expr=[CAST(id@0 AS Int64) as naconst_corr_t4.id, z@1 as z]
801817
05)----DataSourceExec: partitions=1, partition_sizes=[1]
802818

819+
statement ok
820+
SET datafusion.optimizer.prefer_hash_join = true;
821+
803822
statement ok
804823
DROP TABLE naconst_corr_t3;
805824

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

2831+
statement ok
2832+
CREATE TABLE nia_left_with_null(x INT) AS VALUES (NULL), (1), (2), (3), (3), (4);
2833+
2834+
statement ok
2835+
CREATE TABLE nia_right_with_empty(y INT);
2836+
28312837
statement ok
28322838
CREATE TABLE nia_right_with_null(y INT) AS VALUES (2), (NULL);
28332839

@@ -2839,6 +2845,46 @@ query I
28392845
SELECT x FROM nia_left WHERE x NOT IN (SELECT y FROM nia_right_with_null) ORDER BY x;
28402846
----
28412847

2848+
# Subquery is empty -> NOT IN keeps every left row.
2849+
query I
2850+
SELECT x FROM nia_left WHERE x NOT IN (SELECT y FROM nia_right_with_empty) ORDER BY x;
2851+
----
2852+
1
2853+
2
2854+
3
2855+
4
2856+
2857+
# Subquery has no NULL -> NOT IN behaves like a normal anti join.
2858+
query I
2859+
SELECT x FROM nia_left WHERE x NOT IN (SELECT y FROM nia_right_no_null) ORDER BY x;
2860+
----
2861+
1
2862+
3
2863+
2864+
# A NULL in the RHS also suppresses a NULL left key.
2865+
query I
2866+
SELECT x FROM nia_left_with_null WHERE x NOT IN (SELECT y FROM nia_right_with_null) ORDER BY x;
2867+
----
2868+
2869+
# An empty RHS preserves NULL left keys and duplicate rows.
2870+
query I
2871+
SELECT x FROM nia_left_with_null WHERE x NOT IN (SELECT y FROM nia_right_with_empty) ORDER BY x NULLS FIRST;
2872+
----
2873+
NULL
2874+
1
2875+
2
2876+
3
2877+
3
2878+
4
2879+
2880+
# A nonempty RHS without NULL excludes NULL left keys but preserves duplicates.
2881+
query I
2882+
SELECT x FROM nia_left_with_null WHERE x NOT IN (SELECT y FROM nia_right_no_null) ORDER BY x;
2883+
----
2884+
1
2885+
3
2886+
3
2887+
28422888
# With prefer_hash_join = false, this single-key, unfiltered null-aware LeftAnti
28432889
# join uses SortMergeJoinExec with sorted, single-partition inputs.
28442890
query TT
@@ -2883,16 +2929,15 @@ physical_plan
28832929
20)│ rows: 4 ││ rows: 2 │
28842930
21)└───────────────────────────┘└───────────────────────────┘
28852931

2886-
# Subquery has no NULL -> NOT IN behaves like a normal anti join.
2887-
query I
2888-
SELECT x FROM nia_left WHERE x NOT IN (SELECT y FROM nia_right_no_null) ORDER BY x;
2889-
----
2890-
1
2891-
3
2892-
28932932
statement ok
28942933
DROP TABLE nia_left;
28952934

2935+
statement ok
2936+
DROP TABLE nia_left_with_null;
2937+
2938+
statement ok
2939+
DROP TABLE nia_right_with_empty;
2940+
28962941
statement ok
28972942
DROP TABLE nia_right_with_null;
28982943

@@ -2902,6 +2947,141 @@ DROP TABLE nia_right_no_null;
29022947
statement ok
29032948
reset datafusion.optimizer.prefer_hash_join;
29042949

2950+
# A NULL in one RHS partition must affect every outer row.
2951+
2952+
statement ok
2953+
SET datafusion.optimizer.prefer_hash_join = false;
2954+
2955+
statement ok
2956+
SET datafusion.execution.target_partitions = 4;
2957+
2958+
statement ok
2959+
SET datafusion.optimizer.repartition_joins = true;
2960+
2961+
statement ok
2962+
CREATE TABLE nia_mp_left(x INT) AS
2963+
VALUES (NULL), (1), (2), (3), (3);
2964+
2965+
# Each UNION ALL branch produces one partition.
2966+
# CTAS preserves the partitioned result in the memory table.
2967+
statement ok
2968+
CREATE TABLE nia_mp_right AS
2969+
SELECT CAST(10 AS INT) AS y
2970+
UNION ALL
2971+
SELECT CAST(20 AS INT) AS y
2972+
UNION ALL
2973+
SELECT CAST(30 AS INT) AS y
2974+
UNION ALL
2975+
SELECT CAST(NULL AS INT) AS y;
2976+
2977+
# Verify that the RHS really has four nonempty partitions.
2978+
# partition_sizes counts batches, not rows.
2979+
query TT
2980+
EXPLAIN SELECT y FROM nia_mp_right;
2981+
----
2982+
logical_plan TableScan: nia_mp_right projection=[y]
2983+
physical_plan DataSourceExec: partitions=4, partition_sizes=[1, 1, 1, 1]
2984+
2985+
# No left key matches 10, 20, or 30.
2986+
# The NULL in the remaining RHS partition must still suppress every row.
2987+
query I
2988+
SELECT x
2989+
FROM nia_mp_left
2990+
WHERE x NOT IN (SELECT y FROM nia_mp_right)
2991+
ORDER BY x;
2992+
----
2993+
2994+
# The optimizer must bring all RHS partitions into the null-aware join.
2995+
query TT
2996+
EXPLAIN SELECT x FROM nia_mp_left WHERE x NOT IN (SELECT y FROM nia_mp_right);
2997+
----
2998+
logical_plan
2999+
01)LeftAnti Join: nia_mp_left.x = __correlated_sq_1.y null_aware
3000+
02)--TableScan: nia_mp_left projection=[x]
3001+
03)--SubqueryAlias: __correlated_sq_1
3002+
04)----TableScan: nia_mp_right projection=[y]
3003+
physical_plan
3004+
01)SortMergeJoinExec: join_type=LeftAnti, on=[(x@0, y@0)], null_aware
3005+
02)--SortExec: expr=[x@0 ASC], preserve_partitioning=[false]
3006+
03)----DataSourceExec: partitions=1, partition_sizes=[1]
3007+
04)--SortPreservingMergeExec: [y@0 ASC]
3008+
05)----SortExec: expr=[y@0 ASC], preserve_partitioning=[true]
3009+
06)------DataSourceExec: partitions=4, partition_sizes=[1, 1, 1, 1]
3010+
3011+
# Disabling join repartitioning must preserve the same semantics.
3012+
statement ok
3013+
SET datafusion.optimizer.repartition_joins = false;
3014+
3015+
query I
3016+
SELECT x
3017+
FROM nia_mp_left
3018+
WHERE x NOT IN (SELECT y FROM nia_mp_right)
3019+
ORDER BY x;
3020+
----
3021+
3022+
# Disabling hash repartitioning must not bypass the single-partition requirement.
3023+
query TT
3024+
EXPLAIN SELECT x FROM nia_mp_left WHERE x NOT IN (SELECT y FROM nia_mp_right);
3025+
----
3026+
logical_plan
3027+
01)LeftAnti Join: nia_mp_left.x = __correlated_sq_1.y null_aware
3028+
02)--TableScan: nia_mp_left projection=[x]
3029+
03)--SubqueryAlias: __correlated_sq_1
3030+
04)----TableScan: nia_mp_right projection=[y]
3031+
physical_plan
3032+
01)SortMergeJoinExec: join_type=LeftAnti, on=[(x@0, y@0)], null_aware
3033+
02)--SortExec: expr=[x@0 ASC], preserve_partitioning=[false]
3034+
03)----DataSourceExec: partitions=1, partition_sizes=[1]
3035+
04)--SortPreservingMergeExec: [y@0 ASC]
3036+
05)----SortExec: expr=[y@0 ASC], preserve_partitioning=[true]
3037+
06)------DataSourceExec: partitions=4, partition_sizes=[1, 1, 1, 1]
3038+
3039+
# Requesting one target partition must also preserve the result.
3040+
statement ok
3041+
SET datafusion.execution.target_partitions = 1;
3042+
3043+
query I
3044+
SELECT x
3045+
FROM nia_mp_left
3046+
WHERE x NOT IN (SELECT y FROM nia_mp_right)
3047+
ORDER BY x;
3048+
----
3049+
3050+
query TT
3051+
EXPLAIN
3052+
SELECT x
3053+
FROM nia_mp_left
3054+
WHERE x NOT IN (SELECT y FROM nia_mp_right);
3055+
----
3056+
logical_plan
3057+
01)LeftAnti Join: nia_mp_left.x = __correlated_sq_1.y null_aware
3058+
02)--TableScan: nia_mp_left projection=[x]
3059+
03)--SubqueryAlias: __correlated_sq_1
3060+
04)----TableScan: nia_mp_right projection=[y]
3061+
physical_plan
3062+
01)SortMergeJoinExec: join_type=LeftAnti, on=[(x@0, y@0)], null_aware
3063+
02)--SortExec: expr=[x@0 ASC], preserve_partitioning=[false]
3064+
03)----DataSourceExec: partitions=1, partition_sizes=[1]
3065+
04)--SortPreservingMergeExec: [y@0 ASC]
3066+
05)----SortExec: expr=[y@0 ASC], preserve_partitioning=[true]
3067+
06)------DataSourceExec: partitions=4, partition_sizes=[1, 1, 1, 1]
3068+
3069+
statement ok
3070+
DROP TABLE nia_mp_left;
3071+
3072+
statement ok
3073+
DROP TABLE nia_mp_right;
3074+
3075+
statement ok
3076+
RESET datafusion.optimizer.prefer_hash_join;
3077+
3078+
statement ok
3079+
RESET datafusion.optimizer.repartition_joins;
3080+
3081+
# Restore the SLT runner's target partition count.
3082+
statement ok
3083+
SET datafusion.execution.target_partitions = 4;
3084+
29053085
# Regression test for #24139: CTE inside a correlated scalar subquery causing a schema error
29063086
statement ok
29073087
CREATE TABLE metrics (host VARCHAR, bytes_sent DOUBLE, ts BIGINT) AS VALUES

0 commit comments

Comments
 (0)