Skip to content

Commit 7a2a1a0

Browse files
viiryaclaude
andcommitted
feat: Add multi-column support for null-aware anti joins
This commit extends null-aware anti join functionality to support multiple columns, enabling queries like: SELECT * FROM t1 WHERE (a, b) NOT IN (SELECT x, y FROM t2); and correlated multi-column NOT IN subqueries: SELECT * FROM t1 WHERE (c2, c3) NOT IN ( SELECT c2, c3 FROM t2 WHERE t1.c1 = t2.c1 ); Changes: Physical Execution Layer: - Remove single-column validation restriction in HashJoinExec - Extend NULL detection in probe phase to check ANY column for NULLs - Extend NULL filtering in final phase to filter rows with ANY NULL column - Add comprehensive unit tests for 2-column and 3-column joins SQL Planning Layer: - Allow tuple expressions in parse_in_subquery() - Add validation for tuple field count matching Query Optimization Layer: - Update InSubquery validation to allow struct expressions - Skip type coercion for struct expressions (handled in decorrelation) - Implement struct decomposition in decorrelate_predicate_subquery - Decompose struct(a, b) into individual join conditions a = x AND b = y - Handle both correlated and non-correlated multi-column subqueries Test Coverage: - Add 7 new SQL logic test cases (Tests 19-25) - Add 3 unit test functions with 15 test variants (5 batch sizes each) - Cover 2-column, 3-column, empty subquery, and NULL patterns - Include correlated multi-column NOT IN from issue apache#10583 Test Results: - 31/31 null-aware anti join tests passing - 369/369 total hash join tests passing - All optimizer tests passing 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude Sonnet 4.5 <[email protected]>
1 parent 2e8a1d9 commit 7a2a1a0

7 files changed

Lines changed: 572 additions & 68 deletions

File tree

‎datafusion/expr/src/logical_plan/invariants.rs‎

Lines changed: 22 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -224,14 +224,33 @@ pub fn check_subquery_expr(
224224
check_correlations_in_subquery(inner_plan)
225225
} else {
226226
if let Expr::InSubquery(subquery) = expr {
227-
// InSubquery should only return one column
228-
if subquery.subquery.subquery.schema().fields().len() > 1 {
227+
// InSubquery should only return one column UNLESS the left expression is a struct
228+
// (multi-column IN like: (a, b) NOT IN (SELECT x, y FROM ...))
229+
let is_struct = matches!(*subquery.expr, Expr::ScalarFunction(ref func) if func.func.name() == "struct");
230+
231+
let num_subquery_cols = subquery.subquery.subquery.schema().fields().len();
232+
233+
if !is_struct && num_subquery_cols > 1 {
229234
return plan_err!(
230235
"InSubquery should only return one column, but found {}: {}",
231-
subquery.subquery.subquery.schema().fields().len(),
236+
num_subquery_cols,
232237
subquery.subquery.subquery.schema().field_names().join(", ")
233238
);
234239
}
240+
241+
// For struct expressions, validate that the number of fields matches
242+
if is_struct {
243+
if let Expr::ScalarFunction(ref func) = *subquery.expr {
244+
let num_tuple_cols = func.args.len();
245+
if num_tuple_cols != num_subquery_cols {
246+
return plan_err!(
247+
"The number of columns in the tuple ({}) must match the number of columns in the subquery ({})",
248+
num_tuple_cols,
249+
num_subquery_cols
250+
);
251+
}
252+
}
253+
}
235254
}
236255
if let Expr::SetComparison(set_comparison) = expr
237256
&& set_comparison.subquery.subquery.schema().fields().len() > 1

‎datafusion/optimizer/src/analyzer/type_coercion.rs‎

Lines changed: 37 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -490,23 +490,43 @@ impl TreeNodeRewriter for TypeCoercionRewriter<'_> {
490490
Arc::unwrap_or_clone(subquery.subquery),
491491
)?
492492
.data;
493-
let expr_type = expr.get_type(self.schema)?;
494-
let subquery_type = new_plan.schema().field(0).data_type();
495-
let common_type = comparison_coercion(&expr_type, subquery_type).ok_or(
496-
plan_datafusion_err!(
497-
"expr type {expr_type} can't cast to {subquery_type} in InSubquery"
498-
),
499-
)?;
500-
let new_subquery = Subquery {
501-
subquery: Arc::new(new_plan),
502-
outer_ref_columns: subquery.outer_ref_columns,
503-
spans: subquery.spans,
504-
};
505-
Ok(Transformed::yes(Expr::InSubquery(InSubquery::new(
506-
Box::new(expr.cast_to(&common_type, self.schema)?),
507-
cast_subquery(new_subquery, &common_type)?,
508-
negated,
509-
))))
493+
494+
// Check if this is a multi-column IN (struct expression)
495+
let is_struct = matches!(*expr, Expr::ScalarFunction(ref func) if func.func.name() == "struct");
496+
497+
if is_struct {
498+
// For multi-column IN, we don't need type coercion at this level
499+
// The decorrelation phase will handle this by creating join conditions
500+
let new_subquery = Subquery {
501+
subquery: Arc::new(new_plan),
502+
outer_ref_columns: subquery.outer_ref_columns,
503+
spans: subquery.spans,
504+
};
505+
Ok(Transformed::yes(Expr::InSubquery(InSubquery::new(
506+
expr,
507+
new_subquery,
508+
negated,
509+
))))
510+
} else {
511+
// Single-column IN: apply type coercion as before
512+
let expr_type = expr.get_type(self.schema)?;
513+
let subquery_type = new_plan.schema().field(0).data_type();
514+
let common_type = comparison_coercion(&expr_type, subquery_type).ok_or(
515+
plan_datafusion_err!(
516+
"expr type {expr_type} can't cast to {subquery_type} in InSubquery"
517+
),
518+
)?;
519+
let new_subquery = Subquery {
520+
subquery: Arc::new(new_plan),
521+
outer_ref_columns: subquery.outer_ref_columns,
522+
spans: subquery.spans,
523+
};
524+
Ok(Transformed::yes(Expr::InSubquery(InSubquery::new(
525+
Box::new(expr.cast_to(&common_type, self.schema)?),
526+
cast_subquery(new_subquery, &common_type)?,
527+
negated,
528+
))))
529+
}
510530
}
511531
Expr::SetComparison(SetComparison {
512532
expr,

‎datafusion/optimizer/src/decorrelate_predicate_subquery.rs‎

Lines changed: 95 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -394,9 +394,57 @@ fn build_join(
394394
right,
395395
})),
396396
) => {
397-
let right_col = create_col_from_scalar_expr(right.deref(), alias)?;
398-
let in_predicate = Expr::eq(left.deref().clone(), Expr::Column(right_col));
399-
in_predicate.and(join_filter)
397+
// Check if this is a multi-column IN (struct expression)
398+
if let Expr::ScalarFunction(func) = left.deref() {
399+
if func.func.name() == "struct" {
400+
// Decompose struct into individual field comparisons
401+
let struct_args = &func.args;
402+
403+
// The right side should be the subquery result
404+
// Note: After pull-up, the subquery may have additional correlated columns
405+
// We only care about the first N columns that match our struct fields
406+
let subquery_fields = sub_query_alias.schema().fields();
407+
408+
if struct_args.len() > subquery_fields.len() {
409+
return plan_err!(
410+
"Struct field count ({}) exceeds subquery column count ({})",
411+
struct_args.len(),
412+
subquery_fields.len()
413+
);
414+
}
415+
416+
// Create equality conditions for each field
417+
let mut conditions = Vec::new();
418+
for (i, arg) in struct_args.iter().enumerate() {
419+
let field = &subquery_fields[i];
420+
let right_col = Expr::Column(Column::new(
421+
Some(alias.clone()),
422+
field.name().to_string(),
423+
));
424+
conditions.push(Expr::eq(arg.clone(), right_col));
425+
}
426+
427+
// Combine all conditions with AND
428+
let in_predicate = conditions
429+
.into_iter()
430+
.reduce(|acc, cond| acc.and(cond))
431+
.unwrap_or(lit(true));
432+
433+
in_predicate.and(join_filter)
434+
} else {
435+
// Regular scalar function, handle as before
436+
let right_col = create_col_from_scalar_expr(right.deref(), alias)?;
437+
let in_predicate =
438+
Expr::eq(left.deref().clone(), Expr::Column(right_col));
439+
in_predicate.and(join_filter)
440+
}
441+
} else {
442+
// Not a struct, handle as before
443+
let right_col = create_col_from_scalar_expr(right.deref(), alias)?;
444+
let in_predicate =
445+
Expr::eq(left.deref().clone(), Expr::Column(right_col));
446+
in_predicate.and(join_filter)
447+
}
400448
}
401449
(Some(join_filter), _) => join_filter,
402450
(
@@ -407,9 +455,51 @@ fn build_join(
407455
right,
408456
})),
409457
) => {
410-
let right_col = create_col_from_scalar_expr(right.deref(), alias)?;
458+
// Check if this is a multi-column IN (struct expression)
459+
if let Expr::ScalarFunction(func) = left.deref() {
460+
if func.func.name() == "struct" {
461+
// Decompose struct into individual field comparisons
462+
let struct_args = &func.args;
463+
464+
// The right side should be the subquery result
465+
// Note: After pull-up, the subquery may have additional correlated columns
466+
// We only care about the first N columns that match our struct fields
467+
let subquery_fields = sub_query_alias.schema().fields();
468+
469+
if struct_args.len() > subquery_fields.len() {
470+
return plan_err!(
471+
"Struct field count ({}) exceeds subquery column count ({})",
472+
struct_args.len(),
473+
subquery_fields.len()
474+
);
475+
}
476+
477+
// Create equality conditions for each field
478+
let mut conditions = Vec::new();
479+
for (i, arg) in struct_args.iter().enumerate() {
480+
let field = &subquery_fields[i];
481+
let right_col = Expr::Column(Column::new(
482+
Some(alias.clone()),
483+
field.name().to_string(),
484+
));
485+
conditions.push(Expr::eq(arg.clone(), right_col));
486+
}
411487

412-
Expr::eq(left.deref().clone(), Expr::Column(right_col))
488+
// Combine all conditions with AND
489+
conditions
490+
.into_iter()
491+
.reduce(|acc, cond| acc.and(cond))
492+
.unwrap_or(lit(true))
493+
} else {
494+
// Regular scalar function, handle as before
495+
let right_col = create_col_from_scalar_expr(right.deref(), alias)?;
496+
Expr::eq(left.deref().clone(), Expr::Column(right_col))
497+
}
498+
} else {
499+
// Not a struct, handle as before
500+
let right_col = create_col_from_scalar_expr(right.deref(), alias)?;
501+
Expr::eq(left.deref().clone(), Expr::Column(right_col))
502+
}
413503
}
414504
(None, None) => lit(true),
415505
_ => return Ok(None),

0 commit comments

Comments
 (0)