martin-g commented on code in PR #24657:
URL: https://github.com/apache/datafusion/pull/24657#discussion_r3869869662
##########
datafusion/sqllogictest/test_files/dml_delete.slt:
##########
@@ -200,3 +200,62 @@ SELECT * FROM test_delete_in;
statement ok
DROP TABLE test_delete_in;
+
+# Test DELETE with an IN or an EXISTS subquery in the WHERE clause
+# The optimizer rewrites the subquery into a semi join, so the condition cannot
+# reach the table provider as a filter. DataFusion rejects the statement
instead
+# of deleting every row.
+statement ok
+CREATE TABLE test_delete_subquery AS VALUES (1), (2), (3);
+
+statement ok
+CREATE TABLE test_delete_subquery_src AS VALUES (2);
+
+statement error DataFusion error: This feature is not implemented: DELETE on
table 'test_delete_subquery' with an IN or an EXISTS subquery in its WHERE
clause is not supported
+DELETE FROM test_delete_subquery WHERE column1 IN (SELECT column1 FROM
test_delete_subquery_src);
Review Comment:
It would be good to execute the tests also with `set
datafusion.optimizer.max_passes = 0;`.
Some other tests exercise this.
##########
datafusion/core/src/physical_planner.rs:
##########
@@ -2203,6 +2220,132 @@ fn get_physical_expr_pair(
Ok((physical_expr, physical_name))
}
+/// How a DELETE or an UPDATE reaches its target table.
+///
+/// The `filters` argument of [`TableProvider::delete_from`] and
+/// [`TableProvider::update`] is the only channel that carries the `WHERE`
clause
+/// to the provider, and an empty vector means "no `WHERE` clause, so every
row".
+/// A plan whose row restriction cannot travel through that channel must
+/// therefore never reach the provider.
+///
+/// [`TableProvider::delete_from`]:
datafusion_catalog::TableProvider::delete_from
+/// [`TableProvider::update`]: datafusion_catalog::TableProvider::update
+enum DmlInput {
+ /// Every row restriction of the statement reaches the provider as a
filter.
+ Filters,
+ /// No row matches, so the statement affects no rows and the provider is
not
+ /// called at all.
+ NoRows,
+}
+
+/// Check that the input plan of a DELETE or an UPDATE can reach the table
+/// provider without losing part of its `WHERE` clause.
+///
+/// The optimizer rewrites an `IN` or an `EXISTS` subquery into a semi join,
and
+/// it folds an always-false predicate into an empty relation. In both cases
the
+/// condition leaves the `Filter` nodes that [`extract_dml_filters`] reads, and
+/// the provider would see an empty filter list and change every row.
+///
+/// # Parameters
+/// - `input`: the input plan of the DELETE or the UPDATE
+/// - `target`: the target table of the statement
+/// - `op`: `"DELETE"` or `"UPDATE"`, used in the error message
+///
+/// # Returns
+/// [`DmlInput::Filters`] when the provider may be called, [`DmlInput::NoRows`]
+/// when the statement matches no row, and a "not implemented" error when part
of
+/// the `WHERE` clause cannot reach the provider.
+fn classify_dml_input(
+ input: &Arc<LogicalPlan>,
+ target: &TableReference,
+ op: &str,
+) -> Result<DmlInput> {
+ let mut allowed_refs = vec![target.clone()];
+ input.apply(|node| {
+ if let LogicalPlan::SubqueryAlias(alias) = node
+ && let LogicalPlan::TableScan(scan) = alias.input.as_ref()
+ && scan.table_name.resolved_eq(target)
+ {
+ allowed_refs.push(TableReference::bare(alias.alias.to_string()));
+ }
+ Ok(TreeNodeRecursion::Continue)
+ })?;
Review Comment:
This duplicates the same logic at
https://github.com/thelastpickle/datafusion/blob/5f3e301801e3c27f3a3dd69974f71d97e811faad/datafusion/core/src/physical_planner.rs#L2376C5-L2388
It would be good to extract it to a helper function and reuse it.
##########
datafusion/core/src/physical_planner.rs:
##########
@@ -2203,6 +2220,132 @@ fn get_physical_expr_pair(
Ok((physical_expr, physical_name))
}
+/// How a DELETE or an UPDATE reaches its target table.
+///
+/// The `filters` argument of [`TableProvider::delete_from`] and
+/// [`TableProvider::update`] is the only channel that carries the `WHERE`
clause
+/// to the provider, and an empty vector means "no `WHERE` clause, so every
row".
+/// A plan whose row restriction cannot travel through that channel must
+/// therefore never reach the provider.
+///
+/// [`TableProvider::delete_from`]:
datafusion_catalog::TableProvider::delete_from
+/// [`TableProvider::update`]: datafusion_catalog::TableProvider::update
+enum DmlInput {
+ /// Every row restriction of the statement reaches the provider as a
filter.
+ Filters,
+ /// No row matches, so the statement affects no rows and the provider is
not
+ /// called at all.
+ NoRows,
+}
+
+/// Check that the input plan of a DELETE or an UPDATE can reach the table
+/// provider without losing part of its `WHERE` clause.
+///
+/// The optimizer rewrites an `IN` or an `EXISTS` subquery into a semi join,
and
+/// it folds an always-false predicate into an empty relation. In both cases
the
+/// condition leaves the `Filter` nodes that [`extract_dml_filters`] reads, and
+/// the provider would see an empty filter list and change every row.
+///
+/// # Parameters
+/// - `input`: the input plan of the DELETE or the UPDATE
+/// - `target`: the target table of the statement
+/// - `op`: `"DELETE"` or `"UPDATE"`, used in the error message
+///
+/// # Returns
+/// [`DmlInput::Filters`] when the provider may be called, [`DmlInput::NoRows`]
+/// when the statement matches no row, and a "not implemented" error when part
of
+/// the `WHERE` clause cannot reach the provider.
Review Comment:
as bullets
```suggestion
/// * [`DmlInput::Filters`] when the provider may be called
/// * [`DmlInput::NoRows`] when the statement matches no row
/// * a "not implemented" error when part of the `WHERE` clause cannot reach
the provider.
```
##########
datafusion/core/src/physical_planner.rs:
##########
@@ -793,17 +793,26 @@ impl DefaultPhysicalPlanner {
target,
op: WriteOp::Delete,
input,
- ..
+ output_schema,
}) => {
if let Some(provider) =
target.downcast_ref::<DefaultTableSource>() {
- let filters = extract_dml_filters(input, table_name)?;
- provider
- .table_provider
- .delete_from(session_state, filters)
- .await
- .map_err(|e| {
- e.context(format!("DELETE operation on table
'{table_name}'"))
- })?
+ match classify_dml_input(input, table_name, "DELETE")? {
+ DmlInput::NoRows => {
+
zero_rows_affected_exec(Arc::clone(output_schema.inner()))?
+ }
+ DmlInput::Filters => {
+ let filters = extract_dml_filters(input,
table_name)?;
Review Comment:
nit: both `classify_dml_input()` and `extract_dml_filters()` traverse the
tree to collect the `allowed_refs`. Is it worthy to collect them before calling
these methods ?! It looks negligible.
##########
datafusion/core/src/physical_planner.rs:
##########
@@ -2203,6 +2220,132 @@ fn get_physical_expr_pair(
Ok((physical_expr, physical_name))
}
+/// How a DELETE or an UPDATE reaches its target table.
+///
+/// The `filters` argument of [`TableProvider::delete_from`] and
+/// [`TableProvider::update`] is the only channel that carries the `WHERE`
clause
+/// to the provider, and an empty vector means "no `WHERE` clause, so every
row".
+/// A plan whose row restriction cannot travel through that channel must
+/// therefore never reach the provider.
+///
+/// [`TableProvider::delete_from`]:
datafusion_catalog::TableProvider::delete_from
+/// [`TableProvider::update`]: datafusion_catalog::TableProvider::update
+enum DmlInput {
+ /// Every row restriction of the statement reaches the provider as a
filter.
+ Filters,
+ /// No row matches, so the statement affects no rows and the provider is
not
+ /// called at all.
+ NoRows,
+}
+
+/// Check that the input plan of a DELETE or an UPDATE can reach the table
+/// provider without losing part of its `WHERE` clause.
+///
+/// The optimizer rewrites an `IN` or an `EXISTS` subquery into a semi join,
and
+/// it folds an always-false predicate into an empty relation. In both cases
the
+/// condition leaves the `Filter` nodes that [`extract_dml_filters`] reads, and
+/// the provider would see an empty filter list and change every row.
+///
+/// # Parameters
+/// - `input`: the input plan of the DELETE or the UPDATE
+/// - `target`: the target table of the statement
+/// - `op`: `"DELETE"` or `"UPDATE"`, used in the error message
+///
+/// # Returns
+/// [`DmlInput::Filters`] when the provider may be called, [`DmlInput::NoRows`]
+/// when the statement matches no row, and a "not implemented" error when part
of
+/// the `WHERE` clause cannot reach the provider.
+fn classify_dml_input(
+ input: &Arc<LogicalPlan>,
+ target: &TableReference,
+ op: &str,
+) -> Result<DmlInput> {
+ let mut allowed_refs = vec![target.clone()];
+ input.apply(|node| {
+ if let LogicalPlan::SubqueryAlias(alias) = node
+ && let LogicalPlan::TableScan(scan) = alias.input.as_ref()
+ && scan.table_name.resolved_eq(target)
+ {
+ allowed_refs.push(TableReference::bare(alias.alias.to_string()));
+ }
+ Ok(TreeNodeRecursion::Continue)
+ })?;
+
+ let mut result = DmlInput::Filters;
+ input.apply(|node| {
+ match node {
+ // An empty relation means the optimizer proved that no row
matches,
+ // so the statement affects no rows.
+ LogicalPlan::EmptyRelation(empty) if !empty.produce_one_row => {
+ result = DmlInput::NoRows;
+ return Ok(TreeNodeRecursion::Stop);
+ }
+ // A join carries the condition in its `on` clause, where
+ // `extract_dml_filters` cannot read it. The optimizer builds one
for
+ // an `IN` or an `EXISTS` subquery.
+ LogicalPlan::Join(join) => {
+ return not_impl_err!(
+ "{op} on table '{target}' with an IN or an EXISTS subquery
in its \
+ WHERE clause is not supported: the optimizer rewrites the
subquery \
+ into a {} join, and the condition does not reach the
table provider",
+ join.join_type
+ );
+ }
+ LogicalPlan::Filter(filter) => {
+ // A predicate on another table restricts the rows of the
target
+ // table, and the provider cannot evaluate it.
+ for predicate in split_conjunction(&filter.predicate) {
+ if !predicate_is_on_target_multi(predicate,
&allowed_refs)? {
+ return not_impl_err!(
+ "{op} on table '{target}' with a WHERE clause that
\
+ references another table is not supported"
+ );
+ }
+ }
+ }
+ // Plans that pass every row of the target table through, or that
+ // hold no row restriction of their own.
+ LogicalPlan::TableScan(_)
+ | LogicalPlan::Projection(_)
+ | LogicalPlan::SubqueryAlias(_)
+ | LogicalPlan::Sort(_)
+ | LogicalPlan::Repartition(_)
+ // A `Limit` reaches the provider as no filter at all, so a DELETE
+ // ignores it. That is a separate gap, kept as it is here.
Review Comment:
let's file an issue for this. I didn't find an existing one
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
