Skip to content

Commit 145bee4

Browse files
committed
docs: note the row_number requirement for INTERSECT ALL and EXCEPT ALL
Document that `LogicalPlanBuilder::intersect` and `except` with `is_all = true` do not preserve duplicate counts and point callers at `intersect_all` and `except_all`. Name the operation in the planning error raised when `row_number` is not registered, and add an upgrade guide entry for custom context providers and session states. The Substrait `intersect_rel` helper is now only used for distinct intersections, so drop its `is_all` parameter. Signed-off-by: Stefan Wang <[email protected]>
1 parent a03258c commit 145bee4

6 files changed

Lines changed: 75 additions & 10 deletions

File tree

‎datafusion/core/src/dataframe/mod.rs‎

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1964,7 +1964,9 @@ impl DataFrame {
19641964
let right_plan = dataframe.plan;
19651965
let Some(row_number) = self.session_state.window_functions().get("row_number")
19661966
else {
1967-
return plan_err!("row_number window function is not registered");
1967+
return plan_err!(
1968+
"DataFrame::intersect requires the row_number window function, which is not registered"
1969+
);
19681970
};
19691971
let plan = LogicalPlanBuilder::intersect_all(left_plan, right_plan, row_number)?;
19701972
Ok(DataFrame {
@@ -2046,7 +2048,9 @@ impl DataFrame {
20462048
let right_plan = dataframe.plan;
20472049
let Some(row_number) = self.session_state.window_functions().get("row_number")
20482050
else {
2049-
return plan_err!("row_number window function is not registered");
2051+
return plan_err!(
2052+
"DataFrame::except requires the row_number window function, which is not registered"
2053+
);
20502054
};
20512055
let plan = LogicalPlanBuilder::except_all(left_plan, right_plan, row_number)?;
20522056
Ok(DataFrame {

‎datafusion/core/tests/sql/set_operations.rs‎

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -169,3 +169,32 @@ async fn set_operation_all_with_same_name_from_two_relations() -> Result<()> {
169169
assert!(result.iter().all(|batch| batch.num_rows() == 0));
170170
Ok(())
171171
}
172+
173+
#[tokio::test]
174+
async fn set_operation_all_without_row_number_is_a_plan_error() -> Result<()> {
175+
let state = datafusion::execution::SessionStateBuilder::new().build();
176+
let ctx = SessionContext::new_with_state(state);
177+
for operation in ["INTERSECT ALL", "EXCEPT ALL"] {
178+
let err = ctx
179+
.sql(&format!("SELECT 1 {operation} SELECT 1"))
180+
.await
181+
.unwrap_err();
182+
assert_contains!(
183+
err.strip_backtrace(),
184+
"Error during planning: INTERSECT ALL and EXCEPT ALL require the row_number window function, which is not registered"
185+
);
186+
}
187+
188+
let df = ctx.sql("SELECT 1").await?;
189+
let err = df.clone().intersect(df.clone()).unwrap_err();
190+
assert_contains!(
191+
err.strip_backtrace(),
192+
"DataFrame::intersect requires the row_number window function"
193+
);
194+
let err = df.clone().except(df).unwrap_err();
195+
assert_contains!(
196+
err.strip_backtrace(),
197+
"DataFrame::except requires the row_number window function"
198+
);
199+
Ok(())
200+
}

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

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1472,6 +1472,11 @@ impl LogicalPlanBuilder {
14721472
}
14731473

14741474
/// Process intersect set operator
1475+
///
1476+
/// With `is_all = true` this builds a left semi join, which keeps every
1477+
/// matching left row and so does not preserve row multiplicities: a row
1478+
/// appearing twice on the left and once on the right is returned twice.
1479+
/// Use [`Self::intersect_all`] for `INTERSECT ALL` semantics.
14751480
pub fn intersect(
14761481
left_plan: LogicalPlan,
14771482
right_plan: LogicalPlan,
@@ -1486,6 +1491,11 @@ impl LogicalPlanBuilder {
14861491
}
14871492

14881493
/// Process except set operator
1494+
///
1495+
/// With `is_all = true` this builds a left anti join, which removes every
1496+
/// left row that has any match and so does not preserve row
1497+
/// multiplicities: a row appearing twice on the left and once on the right
1498+
/// is removed entirely. Use [`Self::except_all`] for `EXCEPT ALL` semantics.
14891499
pub fn except(
14901500
left_plan: LogicalPlan,
14911501
right_plan: LogicalPlan,
@@ -1500,6 +1510,10 @@ impl LogicalPlanBuilder {
15001510
}
15011511

15021512
/// Build an `INTERSECT ALL` plan, preserving the multiplicity of each row.
1513+
///
1514+
/// `row_number` must be the `row_number` window function. It numbers the
1515+
/// copies of each distinct row on both sides so that they can be matched
1516+
/// one to one.
15031517
pub fn intersect_all(
15041518
left_plan: LogicalPlan,
15051519
right_plan: LogicalPlan,
@@ -1509,6 +1523,9 @@ impl LogicalPlanBuilder {
15091523
}
15101524

15111525
/// Build an `EXCEPT ALL` plan, subtracting matching row multiplicities.
1526+
///
1527+
/// `row_number` must be the `row_number` window function, as for
1528+
/// [`Self::intersect_all`].
15121529
pub fn except_all(
15131530
left_plan: LogicalPlan,
15141531
right_plan: LogicalPlan,

‎datafusion/sql/src/set_expr.rs‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -187,7 +187,9 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
187187

188188
fn row_number_for_set_operation(&self) -> Result<Arc<datafusion_expr::WindowUDF>> {
189189
let Some(row_number) = self.context_provider.get_window_meta("row_number") else {
190-
return plan_err!("row_number window function is not registered");
190+
return plan_err!(
191+
"INTERSECT ALL and EXCEPT ALL require the row_number window function, which is not registered"
192+
);
191193
};
192194
Ok(row_number)
193195
}

‎datafusion/substrait/src/logical_plan/consumer/rel/set_rel.rs‎

Lines changed: 5 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -39,7 +39,6 @@ pub async fn from_set_rel(
3939
SetOp::IntersectionPrimary => intersect_rel(
4040
consumer.consume_rel(&set.inputs[0]).await?,
4141
union_rels(consumer, &set.inputs[1..], true).await?,
42-
false,
4342
),
4443
SetOp::IntersectionMultiset => {
4544
intersect_rels(consumer, &set.inputs, false).await
@@ -90,15 +89,15 @@ async fn intersect_rels(
9089
&consumer.get_function_registry().udwf("row_number")?,
9190
)?
9291
} else {
93-
intersect_rel(rel, right, false)?
92+
intersect_rel(rel, right)?
9493
};
9594
}
9695

9796
Ok(rel)
9897
}
9998

100-
/// Intersects two relations, giving the result the nullability the Substrait
101-
/// [Set Operation rules] prescribe.
99+
/// Intersects two relations with distinct (set) semantics, giving the result
100+
/// the nullability the Substrait [Set Operation rules] prescribe.
102101
///
103102
/// [`LogicalPlanBuilder::intersect`] compiles an intersection into a left semi
104103
/// join, so on its own the result keeps the left input's nullability. The join
@@ -143,7 +142,6 @@ async fn intersect_rels(
143142
fn intersect_rel(
144143
left: LogicalPlan,
145144
right: LogicalPlan,
146-
is_all: bool,
147145
) -> datafusion::common::Result<LogicalPlan> {
148146
let left_fields = left.schema().fields();
149147
let right_fields = right.schema().fields();
@@ -162,14 +160,14 @@ fn intersect_rel(
162160

163161
// `intersect` also reports inputs of different widths.
164162
if left_fields.len() != right_fields.len() || !from_right.contains(&true) {
165-
return LogicalPlanBuilder::intersect(left, right, is_all);
163+
return LogicalPlanBuilder::intersect(left, right, false);
166164
}
167165

168166
let (left, right, _) = requalify_sides_if_needed(
169167
LogicalPlanBuilder::from(left),
170168
LogicalPlanBuilder::from(right),
171169
)?;
172-
let left = if is_all { left } else { left.distinct()? };
170+
let left = left.distinct()?;
173171
let right = right.distinct()?.build()?;
174172

175173
let left_columns = left.schema().columns();

‎docs/source/library-user-guide/upgrading/56.0.0.md‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -597,3 +597,18 @@ Wire compatibility is directional:
597597
- A 55.0 reader must not consume an alias-preserving 56.0 MERGE payload. It
598598
ignores the unknown field but cannot preserve the qualifier required by the
599599
expressions, which can cause resolution failure or incorrect rebinding.
600+
601+
### `INTERSECT ALL` and `EXCEPT ALL` require the `row_number` window function
602+
603+
SQL `INTERSECT ALL` and `EXCEPT ALL`, `DataFrame::intersect`, `DataFrame::except`,
604+
and Substrait multiset `INTERSECTION_MULTISET_ALL` and `MINUS_PRIMARY_ALL` plans
605+
now use `row_number()` to keep the correct number of duplicate rows. A custom
606+
`ContextProvider` or `SessionState` that does not register `row_number` (for
607+
example one built with `SessionStateBuilder::new()` without
608+
`with_default_features()`) now returns a planning error for these queries.
609+
Register `datafusion_functions_window::row_number::row_number_udwf()` to plan them.
610+
611+
`LogicalPlanBuilder::intersect` and `LogicalPlanBuilder::except` with
612+
`is_all = true` still build the old plan, which does not preserve duplicate
613+
counts. Use `LogicalPlanBuilder::intersect_all` and
614+
`LogicalPlanBuilder::except_all` instead.

0 commit comments

Comments
 (0)