@@ -3140,6 +3140,12 @@ set datafusion.optimizer.prefer_hash_join = false;
31403140statement ok
31413141CREATE TABLE nia_left(x INT) AS VALUES (1), (2), (3), (4);
31423142
3143+ statement ok
3144+ CREATE TABLE nia_left_with_null(x INT) AS VALUES (NULL), (1), (2), (3), (3), (4);
3145+
3146+ statement ok
3147+ CREATE TABLE nia_right_with_empty(y INT);
3148+
31433149statement ok
31443150CREATE TABLE nia_right_with_null(y INT) AS VALUES (2), (NULL);
31453151
@@ -3151,6 +3157,46 @@ query I
31513157SELECT x FROM nia_left WHERE x NOT IN (SELECT y FROM nia_right_with_null) ORDER BY x;
31523158----
31533159
3160+ # Subquery is empty -> NOT IN keeps every left row.
3161+ query I
3162+ SELECT x FROM nia_left WHERE x NOT IN (SELECT y FROM nia_right_with_empty) ORDER BY x;
3163+ ----
3164+ 1
3165+ 2
3166+ 3
3167+ 4
3168+
3169+ # Subquery has no NULL -> NOT IN behaves like a normal anti join.
3170+ query I
3171+ SELECT x FROM nia_left WHERE x NOT IN (SELECT y FROM nia_right_no_null) ORDER BY x;
3172+ ----
3173+ 1
3174+ 3
3175+
3176+ # A NULL in the RHS also suppresses a NULL left key.
3177+ query I
3178+ SELECT x FROM nia_left_with_null WHERE x NOT IN (SELECT y FROM nia_right_with_null) ORDER BY x;
3179+ ----
3180+
3181+ # An empty RHS preserves NULL left keys and duplicate rows.
3182+ query I
3183+ SELECT x FROM nia_left_with_null WHERE x NOT IN (SELECT y FROM nia_right_with_empty) ORDER BY x NULLS FIRST;
3184+ ----
3185+ NULL
3186+ 1
3187+ 2
3188+ 3
3189+ 3
3190+ 4
3191+
3192+ # A nonempty RHS without NULL excludes NULL left keys but preserves duplicates.
3193+ query I
3194+ SELECT x FROM nia_left_with_null WHERE x NOT IN (SELECT y FROM nia_right_no_null) ORDER BY x;
3195+ ----
3196+ 1
3197+ 3
3198+ 3
3199+
31543200# With prefer_hash_join = false, this single-key, unfiltered null-aware LeftAnti
31553201# join uses SortMergeJoinExec with sorted, single-partition inputs.
31563202query TT
@@ -3195,16 +3241,15 @@ physical_plan
3195324120)│ rows: 4 ││ rows: 2 │
3196324221)└───────────────────────────┘└───────────────────────────┘
31973243
3198- # Subquery has no NULL -> NOT IN behaves like a normal anti join.
3199- query I
3200- SELECT x FROM nia_left WHERE x NOT IN (SELECT y FROM nia_right_no_null) ORDER BY x;
3201- ----
3202- 1
3203- 3
3204-
32053244statement ok
32063245DROP TABLE nia_left;
32073246
3247+ statement ok
3248+ DROP TABLE nia_left_with_null;
3249+
3250+ statement ok
3251+ DROP TABLE nia_right_with_empty;
3252+
32083253statement ok
32093254DROP TABLE nia_right_with_null;
32103255
@@ -3214,6 +3259,141 @@ DROP TABLE nia_right_no_null;
32143259statement ok
32153260reset datafusion.optimizer.prefer_hash_join;
32163261
3262+ # A NULL in one RHS partition must affect every outer row.
3263+
3264+ statement ok
3265+ SET datafusion.optimizer.prefer_hash_join = false;
3266+
3267+ statement ok
3268+ SET datafusion.execution.target_partitions = 4;
3269+
3270+ statement ok
3271+ SET datafusion.optimizer.repartition_joins = true;
3272+
3273+ statement ok
3274+ CREATE TABLE nia_mp_left(x INT) AS
3275+ VALUES (NULL), (1), (2), (3), (3);
3276+
3277+ # Each UNION ALL branch produces one partition.
3278+ # CTAS preserves the partitioned result in the memory table.
3279+ statement ok
3280+ CREATE TABLE nia_mp_right AS
3281+ SELECT CAST(10 AS INT) AS y
3282+ UNION ALL
3283+ SELECT CAST(20 AS INT) AS y
3284+ UNION ALL
3285+ SELECT CAST(30 AS INT) AS y
3286+ UNION ALL
3287+ SELECT CAST(NULL AS INT) AS y;
3288+
3289+ # Verify that the RHS really has four nonempty partitions.
3290+ # partition_sizes counts batches, not rows.
3291+ query TT
3292+ EXPLAIN SELECT y FROM nia_mp_right;
3293+ ----
3294+ logical_plan TableScan: nia_mp_right projection=[y]
3295+ physical_plan DataSourceExec: partitions=4, partition_sizes=[1, 1, 1, 1]
3296+
3297+ # No left key matches 10, 20, or 30.
3298+ # The NULL in the remaining RHS partition must still suppress every row.
3299+ query I
3300+ SELECT x
3301+ FROM nia_mp_left
3302+ WHERE x NOT IN (SELECT y FROM nia_mp_right)
3303+ ORDER BY x;
3304+ ----
3305+
3306+ # The optimizer must bring all RHS partitions into the null-aware join.
3307+ query TT
3308+ EXPLAIN SELECT x FROM nia_mp_left WHERE x NOT IN (SELECT y FROM nia_mp_right);
3309+ ----
3310+ logical_plan
3311+ 01)LeftAnti Join: nia_mp_left.x = __correlated_sq_1.y null_aware
3312+ 02)--TableScan: nia_mp_left projection=[x]
3313+ 03)--SubqueryAlias: __correlated_sq_1
3314+ 04)----TableScan: nia_mp_right projection=[y]
3315+ physical_plan
3316+ 01)SortMergeJoinExec: join_type=LeftAnti, on=[(x@0, y@0)], null_aware
3317+ 02)--SortExec: expr=[x@0 ASC], preserve_partitioning=[false]
3318+ 03)----DataSourceExec: partitions=1, partition_sizes=[1]
3319+ 04)--SortPreservingMergeExec: [y@0 ASC]
3320+ 05)----SortExec: expr=[y@0 ASC], preserve_partitioning=[true]
3321+ 06)------DataSourceExec: partitions=4, partition_sizes=[1, 1, 1, 1]
3322+
3323+ # Disabling join repartitioning must preserve the same semantics.
3324+ statement ok
3325+ SET datafusion.optimizer.repartition_joins = false;
3326+
3327+ query I
3328+ SELECT x
3329+ FROM nia_mp_left
3330+ WHERE x NOT IN (SELECT y FROM nia_mp_right)
3331+ ORDER BY x;
3332+ ----
3333+
3334+ # Disabling hash repartitioning must not bypass the single-partition requirement.
3335+ query TT
3336+ EXPLAIN SELECT x FROM nia_mp_left WHERE x NOT IN (SELECT y FROM nia_mp_right);
3337+ ----
3338+ logical_plan
3339+ 01)LeftAnti Join: nia_mp_left.x = __correlated_sq_1.y null_aware
3340+ 02)--TableScan: nia_mp_left projection=[x]
3341+ 03)--SubqueryAlias: __correlated_sq_1
3342+ 04)----TableScan: nia_mp_right projection=[y]
3343+ physical_plan
3344+ 01)SortMergeJoinExec: join_type=LeftAnti, on=[(x@0, y@0)], null_aware
3345+ 02)--SortExec: expr=[x@0 ASC], preserve_partitioning=[false]
3346+ 03)----DataSourceExec: partitions=1, partition_sizes=[1]
3347+ 04)--SortPreservingMergeExec: [y@0 ASC]
3348+ 05)----SortExec: expr=[y@0 ASC], preserve_partitioning=[true]
3349+ 06)------DataSourceExec: partitions=4, partition_sizes=[1, 1, 1, 1]
3350+
3351+ # Requesting one target partition must also preserve the result.
3352+ statement ok
3353+ SET datafusion.execution.target_partitions = 1;
3354+
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+ query TT
3363+ EXPLAIN
3364+ SELECT x
3365+ FROM nia_mp_left
3366+ WHERE x NOT IN (SELECT y FROM nia_mp_right);
3367+ ----
3368+ logical_plan
3369+ 01)LeftAnti Join: nia_mp_left.x = __correlated_sq_1.y null_aware
3370+ 02)--TableScan: nia_mp_left projection=[x]
3371+ 03)--SubqueryAlias: __correlated_sq_1
3372+ 04)----TableScan: nia_mp_right projection=[y]
3373+ physical_plan
3374+ 01)SortMergeJoinExec: join_type=LeftAnti, on=[(x@0, y@0)], null_aware
3375+ 02)--SortExec: expr=[x@0 ASC], preserve_partitioning=[false]
3376+ 03)----DataSourceExec: partitions=1, partition_sizes=[1]
3377+ 04)--SortPreservingMergeExec: [y@0 ASC]
3378+ 05)----SortExec: expr=[y@0 ASC], preserve_partitioning=[true]
3379+ 06)------DataSourceExec: partitions=4, partition_sizes=[1, 1, 1, 1]
3380+
3381+ statement ok
3382+ DROP TABLE nia_mp_left;
3383+
3384+ statement ok
3385+ DROP TABLE nia_mp_right;
3386+
3387+ statement ok
3388+ RESET datafusion.optimizer.prefer_hash_join;
3389+
3390+ statement ok
3391+ RESET datafusion.optimizer.repartition_joins;
3392+
3393+ # Restore the SLT runner's target partition count.
3394+ statement ok
3395+ SET datafusion.execution.target_partitions = 4;
3396+
32173397# Regression test for #24139: CTE inside a correlated scalar subquery causing a schema error
32183398statement ok
32193399CREATE TABLE metrics (host VARCHAR, bytes_sent DOUBLE, ts BIGINT) AS VALUES
0 commit comments