@@ -3196,6 +3196,12 @@ set datafusion.optimizer.prefer_hash_join = false;
31963196statement ok
31973197CREATE 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+
31993205statement ok
32003206CREATE TABLE nia_right_with_null(y INT) AS VALUES (2), (NULL);
32013207
@@ -3207,6 +3213,46 @@ query I
32073213SELECT 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.
32123258query TT
@@ -3251,16 +3297,15 @@ physical_plan
3251329720)│ rows: 4 ││ rows: 2 │
3252329821)└───────────────────────────┘└───────────────────────────┘
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-
32613300statement ok
32623301DROP 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+
32643309statement ok
32653310DROP TABLE nia_right_with_null;
32663311
@@ -3270,6 +3315,141 @@ DROP TABLE nia_right_no_null;
32703315statement ok
32713316reset 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
32743454statement ok
32753455CREATE TABLE metrics (host VARCHAR, bytes_sent DOUBLE, ts BIGINT) AS VALUES
0 commit comments