@@ -410,9 +410,10 @@ NULL 0
410410#############
411411
412412# Results under a batch size sweep are in null_aware_multi_column.slt. A tuple
413- # NOT IN is a null-aware join whose leading keys are the tuple elements
414- # (`value_keys`), followed by any correlation keys. Like the scalar NOT IN,
415- # JoinSelection swaps it into a null-aware RightAnti that builds on the
413+ # NOT IN is a null-aware join whose leading keys are the tuple elements that
414+ # can be NULL (`value_keys`), followed by the elements that cannot and by any
415+ # correlation keys, which only narrow the scope of a row. Like the scalar NOT
416+ # IN, JoinSelection swaps it into a null-aware RightAnti that builds on the
416417# subquery side when that side is not larger, keeping the key order.
417418
418419statement ok
@@ -604,6 +605,88 @@ physical_plan
60460504)------DataSourceExec: partitions=1, partition_sizes=[1]
60560605)------DataSourceExec: partitions=1, partition_sizes=[1]
606607
608+ # A correlated filter that rejects NULL makes `b = y` a scope key although
609+ # both columns are nullable (the `mc_scope` results in
610+ # null_aware_multi_column.slt).
611+ query TT
612+ EXPLAIN SELECT * FROM multi_col_outer o
613+ WHERE (a, b) NOT IN (SELECT x, y FROM multi_col_inner i WHERE i.y >= o.b);
614+ ----
615+ logical_plan
616+ 01)LeftAnti Join: o.a = __correlated_sq_1.x, o.b = __correlated_sq_1.y Filter: __correlated_sq_1.y >= o.b null_aware
617+ 02)--SubqueryAlias: o
618+ 03)----TableScan: multi_col_outer projection=[k, a, b]
619+ 04)--SubqueryAlias: __correlated_sq_1
620+ 05)----SubqueryAlias: i
621+ 06)------TableScan: multi_col_inner projection=[x, y]
622+ physical_plan
623+ 01)HashJoinExec: mode=CollectLeft, join_type=RightAnti, on=[(x@0, a@1), (y@1, b@2)], filter=y@1 >= b@0, null_aware
624+ 02)--DataSourceExec: partitions=1, partition_sizes=[1]
625+ 03)--DataSourceExec: partitions=1, partition_sizes=[1]
626+
627+ # A dictionary can hold a NULL without a null bit, so its NOT NULL field does
628+ # not rule out a NULL element. A cast to a common type hides the dictionary
629+ # from the key's type, so the type of each column the key reads counts too:
630+ # `b` stays a value key whichever side is cast.
631+ statement ok
632+ CREATE TABLE multi_col_outer_dict AS
633+ SELECT k, a, arrow_cast(b, 'Dictionary(Int32, Int32)') AS b FROM multi_col_outer_nn;
634+
635+ statement ok
636+ CREATE TABLE multi_col_inner_i64 AS
637+ SELECT k, x, arrow_cast(y, 'Int64') AS y FROM multi_col_inner_nn;
638+
639+ query TT
640+ EXPLAIN SELECT * FROM multi_col_outer_dict
641+ WHERE (a, b) NOT IN (SELECT x, y FROM multi_col_inner_i64);
642+ ----
643+ logical_plan
644+ 01)LeftAnti Join: multi_col_outer_dict.a = __correlated_sq_1.x, CAST(multi_col_outer_dict.b AS Int64) = __correlated_sq_1.y null_aware(value_keys=2)
645+ 02)--TableScan: multi_col_outer_dict projection=[k, a, b]
646+ 03)--SubqueryAlias: __correlated_sq_1
647+ 04)----TableScan: multi_col_inner_i64 projection=[x, y]
648+ physical_plan
649+ 01)HashJoinExec: mode=CollectLeft, join_type=RightAnti, on=[(x@0, a@1), (y@1, CAST(multi_col_outer_dict.b AS Int64)@3)], projection=[k@0, a@1, b@2], null_aware(value_keys=2)
650+ 02)--DataSourceExec: partitions=1, partition_sizes=[1]
651+ 03)--ProjectionExec: expr=[k@0 as k, a@1 as a, b@2 as b, CAST(b@2 AS Int64) as CAST(multi_col_outer_dict.b AS Int64)]
652+ 04)----DataSourceExec: partitions=1, partition_sizes=[1]
653+
654+ statement ok
655+ CREATE TABLE multi_col_outer_i64 AS
656+ SELECT k, a, arrow_cast(b, 'Int64') AS b FROM multi_col_outer_nn;
657+
658+ statement ok
659+ CREATE TABLE multi_col_inner_dict AS
660+ SELECT k, x, arrow_cast(y, 'Dictionary(Int32, Int32)') AS y FROM multi_col_inner_nn;
661+
662+ query TT
663+ EXPLAIN SELECT * FROM multi_col_outer_i64
664+ WHERE (a, b) NOT IN (SELECT x, y FROM multi_col_inner_dict);
665+ ----
666+ logical_plan
667+ 01)LeftAnti Join: multi_col_outer_i64.a = __correlated_sq_1.x, multi_col_outer_i64.b = __correlated_sq_1.multi_col_inner_dict.y null_aware(value_keys=2)
668+ 02)--TableScan: multi_col_outer_i64 projection=[k, a, b]
669+ 03)--SubqueryAlias: __correlated_sq_1
670+ 04)----Projection: multi_col_inner_dict.x, CAST(multi_col_inner_dict.y AS Int64)
671+ 05)------TableScan: multi_col_inner_dict projection=[x, y]
672+ physical_plan
673+ 01)HashJoinExec: mode=CollectLeft, join_type=RightAnti, on=[(x@0, a@1), (multi_col_inner_dict.y@1, b@2)], null_aware(value_keys=2)
674+ 02)--ProjectionExec: expr=[x@0 as x, CAST(y@1 AS Int64) as multi_col_inner_dict.y]
675+ 03)----DataSourceExec: partitions=1, partition_sizes=[1]
676+ 04)--DataSourceExec: partitions=1, partition_sizes=[1]
677+
678+ statement ok
679+ DROP TABLE multi_col_outer_dict;
680+
681+ statement ok
682+ DROP TABLE multi_col_inner_i64;
683+
684+ statement ok
685+ DROP TABLE multi_col_outer_i64;
686+
687+ statement ok
688+ DROP TABLE multi_col_inner_dict;
689+
607690statement ok
608691DROP TABLE multi_col_outer_nn;
609692
0 commit comments