@@ -2828,6 +2828,12 @@ set datafusion.optimizer.prefer_hash_join = false;
28282828statement ok
28292829CREATE 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+
28312837statement ok
28322838CREATE TABLE nia_right_with_null(y INT) AS VALUES (2), (NULL);
28332839
@@ -2839,6 +2845,46 @@ query I
28392845SELECT 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.
28442890query TT
@@ -2883,16 +2929,15 @@ physical_plan
2883292920)│ rows: 4 ││ rows: 2 │
2884293021)└───────────────────────────┘└───────────────────────────┘
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-
28932932statement ok
28942933DROP 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+
28962941statement ok
28972942DROP TABLE nia_right_with_null;
28982943
@@ -2902,6 +2947,141 @@ DROP TABLE nia_right_no_null;
29022947statement ok
29032948reset 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
29063086statement ok
29073087CREATE TABLE metrics (host VARCHAR, bytes_sent DOUBLE, ts BIGINT) AS VALUES
0 commit comments