@@ -3127,6 +3127,12 @@ set datafusion.optimizer.prefer_hash_join = false;
31273127statement ok
31283128CREATE 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+
31303136statement ok
31313137CREATE TABLE nia_right_with_null(y INT) AS VALUES (2), (NULL);
31323138
@@ -3138,6 +3144,46 @@ query I
31383144SELECT 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.
31433189query TT
@@ -3182,16 +3228,15 @@ physical_plan
3182322820)│ rows: 4 ││ rows: 2 │
3183322921)└───────────────────────────┘└───────────────────────────┘
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-
31923231statement ok
31933232DROP 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+
31953240statement ok
31963241DROP TABLE nia_right_with_null;
31973242
@@ -3201,6 +3246,141 @@ DROP TABLE nia_right_no_null;
32013246statement ok
32023247reset 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
32053385statement ok
32063386CREATE TABLE metrics (host VARCHAR, bytes_sent DOUBLE, ts BIGINT) AS VALUES
0 commit comments