@@ -525,29 +525,15 @@ fn build_join(
525525 replace_qualified_name ( filter, & all_correlated_cols, & alias) . map ( Some )
526526 } ) ?;
527527
528- // The outer value expression of an `IN`/`NOT IN` predicate whose join filter
529- // is nothing but that predicate, recorded together with the subquery column
530- // it is compared against and a name for the column it can be projected as.
531- // Correlated subqueries are excluded on purpose: their correlation predicate
532- // is a second join key, and null-aware hash joins accept only a single key.
528+ // The outer value expression of an `IN`/`NOT IN` predicate, recorded together
529+ // with the subquery column it is compared against, a name for the column it
530+ // can be projected as, and the correlation predicates that share the join
531+ // filter with it.
533532 let mut in_value_expr = None ;
534533
535534 let mut join_filter = match ( join_filter_opt, in_predicate_opt. cloned ( ) ) {
536535 (
537- Some ( join_filter) ,
538- Some ( Expr :: BinaryExpr ( BinaryExpr {
539- left,
540- op : Operator :: Eq ,
541- right,
542- } ) ) ,
543- ) => {
544- let right_col = create_col_from_scalar_expr ( & right, alias) ?;
545- let in_predicate = Expr :: eq ( left. deref ( ) . clone ( ) , Expr :: Column ( right_col) ) ;
546- in_predicate. and ( join_filter)
547- }
548- ( Some ( join_filter) , _) => join_filter,
549- (
550- _,
536+ correlation_opt,
551537 Some ( Expr :: BinaryExpr ( BinaryExpr {
552538 left,
553539 op : Operator :: Eq ,
@@ -557,10 +543,19 @@ fn build_join(
557543 let value_name = format ! ( "{alias}_value" ) ;
558544 let right_col = create_col_from_scalar_expr ( & right, alias) ?;
559545 let value = left. deref ( ) . clone ( ) ;
560- in_value_expr = Some ( ( value. clone ( ) , right_col. clone ( ) , value_name) ) ;
546+ let in_predicate = Expr :: eq ( value. clone ( ) , Expr :: Column ( right_col. clone ( ) ) ) ;
547+ // The `IN` equality is the leading conjunct so that it becomes
548+ // `on[0]`, the key position a null-aware hash join reads as the
549+ // `NOT IN` value key (see `HashJoinExec::null_aware`).
550+ let join_filter = match & correlation_opt {
551+ Some ( correlation) => in_predicate. and ( correlation. clone ( ) ) ,
552+ None => in_predicate,
553+ } ;
554+ in_value_expr = Some ( ( value, right_col, value_name, correlation_opt) ) ;
561555
562- Expr :: eq ( value , Expr :: Column ( right_col ) )
556+ join_filter
563557 }
558+ ( Some ( join_filter) , _) => join_filter,
564559 ( None , None ) => lit ( true ) ,
565560 _ => return Ok ( None ) ,
566561 } ;
@@ -572,12 +567,21 @@ fn build_join(
572567 // right-only, so `push_down_filter` moves it into the subquery and drops the
573568 // very NULLs that make `NOT IN` UNKNOWN, and a join without equi-join keys
574569 // is planned as a nested loop join, which has no null-aware implementation.
570+ // A correlated subquery hits a third problem: its correlation predicate is a
571+ // valid equi-join key, so it takes `on[0]`, the position the null-aware hash
572+ // join reads as the `NOT IN` value key.
575573 // Projecting the constant as a column of the outer side turns the predicate
576- // into a real equi-join key so the null-aware hash join handles it.
574+ // into a real equi-join key, which fixes all three.
575+ // The projection only pays for itself on a join that ends up null-aware, so
576+ // its guard repeats the `null_aware` conditions below.
577577 let mut projected_left = None ;
578- if let Some ( ( value, right_col, mut value_name) ) = in_value_expr
578+ if let Some ( ( value, right_col, mut value_name, correlation_opt ) ) = in_value_expr
579579 && value. column_refs ( ) . is_empty ( )
580- && matches ! ( join_type, JoinType :: LeftAnti | JoinType :: LeftMark )
580+ && match join_type {
581+ JoinType :: LeftAnti => true ,
582+ JoinType :: LeftMark => needs_null_aware_mark,
583+ _ => false ,
584+ }
581585 && join_keys_may_be_null ( & join_filter, left. schema ( ) , sub_query_alias. schema ( ) ) ?
582586 {
583587 // The projected column is unqualified, so a left field that already has
@@ -598,9 +602,13 @@ fn build_join(
598602 . project ( projections) ?
599603 . build ( ) ?,
600604 ) ;
601- // `in_value_expr` is only set when the `IN` equality is the whole join
602- // filter, so it can simply be rebuilt against the projected column.
603- join_filter = Expr :: eq ( Expr :: Column ( value_col) , Expr :: Column ( right_col) ) ;
605+ // Rebuild the `IN` equality against the projected column, keeping it
606+ // ahead of the correlation predicates so that it stays `on[0]`.
607+ let in_predicate = Expr :: eq ( Expr :: Column ( value_col) , Expr :: Column ( right_col) ) ;
608+ join_filter = match correlation_opt {
609+ Some ( correlation) => in_predicate. and ( correlation) ,
610+ None => in_predicate,
611+ } ;
604612 }
605613 let left = projected_left. as_ref ( ) . unwrap_or ( left) ;
606614
@@ -1537,11 +1545,11 @@ mod tests {
15371545 )
15381546 }
15391547
1540- /// The same rewrite must not fire for a correlated subquery: the
1541- /// correlation predicate is a second equi-join key, and null-aware hash
1542- /// joins accept only one .
1548+ /// The same rewrite fires for a correlated subquery, where it also keeps the
1549+ /// `IN` equality ahead of the correlation predicate so that the equality
1550+ /// stays `on[0]`, the null-aware hash join's value key .
15431551 #[ test]
1544- fn constant_not_in_correlated_subquery_is_not_rewritten ( ) -> Result < ( ) > {
1552+ fn constant_not_in_correlated_subquery_is_rewritten ( ) -> Result < ( ) > {
15451553 let outer_scan = nullable_scalar_mark_scan ( "outer_t" ) ?;
15461554 let inner_scan = nullable_scalar_mark_scan ( "inner_t" ) ?;
15471555
@@ -1561,11 +1569,13 @@ mod tests {
15611569 assert_optimized_plan_equal ! (
15621570 plan,
15631571 @r"
1564- LeftAnti Join: Filter: Int32(3) = __correlated_sq_1.id AND outer_t.grp = __correlated_sq_1.grp null_aware [id:Int32;N, grp:Int32;N]
1565- TableScan: outer_t [id:Int32;N, grp:Int32;N]
1566- SubqueryAlias: __correlated_sq_1 [id:Int32;N, grp:Int32;N]
1567- Projection: inner_t.id, inner_t.grp [id:Int32;N, grp:Int32;N]
1568- TableScan: inner_t [id:Int32;N, grp:Int32;N]
1572+ Projection: outer_t.id, outer_t.grp [id:Int32;N, grp:Int32;N]
1573+ LeftAnti Join: Filter: __correlated_sq_1_value = __correlated_sq_1.id AND outer_t.grp = __correlated_sq_1.grp null_aware [id:Int32;N, grp:Int32;N, __correlated_sq_1_value:Int32]
1574+ Projection: outer_t.id, outer_t.grp, Int32(3) AS __correlated_sq_1_value [id:Int32;N, grp:Int32;N, __correlated_sq_1_value:Int32]
1575+ TableScan: outer_t [id:Int32;N, grp:Int32;N]
1576+ SubqueryAlias: __correlated_sq_1 [id:Int32;N, grp:Int32;N]
1577+ Projection: inner_t.id, inner_t.grp [id:Int32;N, grp:Int32;N]
1578+ TableScan: inner_t [id:Int32;N, grp:Int32;N]
15691579 "
15701580 )
15711581 }
0 commit comments