Skip to content

Commit 9b469be

Browse files
michaelsembwevermartin-g
authored andcommitted
docs: document DELETE and UPDATE for SQL users and table provider authors (apache#24567)
## Which issue does this PR ~~close~~ relate to? - Documentation follow-up for apache#19142, which added the `TableProvider` row-level DML hooks and closed apache#16959. That PR shipped no documentation. - Related to apache#19950. ## Rationale for this change Since 52.0.0, DataFusion runs `DELETE` and `UPDATE` against a table whose provider implements `TableProvider::delete_from()` or `TableProvider::update()`, and the built-in in-memory table implements both. No page in the documentation says so. A SQL user therefore cannot learn which tables accept the two statements, what a statement returns, or which forms fail. A provider author cannot learn what the planner passes to each hook, or what the hook must return. Two current behaviours are surprising enough to warn about in the same pass: - A `DELETE` or an `UPDATE` whose `WHERE` clause holds an `IN` or an `EXISTS` subquery applies to **all** rows of the table. The optimizer rewrites the subquery into a `LeftSemi Join`, so `extract_dml_filters()` finds no predicate on the target table, and the provider reads the empty filter list as "no `WHERE` clause". ```sql > create table s1 as values (1), (2), (3); > create table s2 as values (2); > delete from s1 where column1 in (select column1 from s2); -- count 3; s1 is now empty ``` - `EXPLAIN DELETE` and `EXPLAIN UPDATE` execute the statement on an in-memory table. `MemTable` changes the rows inside the hook, and the physical planner calls the hook while it builds the plan. Both behaviours need code fixes, which this PR does not attempt. Until then a reader needs the warning. ## What changes are included in this PR? `docs/source/user-guide/sql/dml.md`: - A `DELETE` section and an `UPDATE` section: syntax, the `count` result, three-valued logic, and examples. - A "Table support for DELETE and UPDATE" section: which table kinds support the statements, and the exact error text for a table that does not. - A "Limitations" section: the two warnings above, the ignored `LIMIT` on `DELETE`, and `UPDATE ... FROM`. `docs/source/library-user-guide/custom-table-providers.md`: - A "Row-Level DML: DELETE and UPDATE" section: what the planner passes to each hook (split `AND` conjunctions, stripped table qualifiers, target-table predicates only), the single-row `count` return contract, the two semantic rules a provider must follow, a compiling example, the clauses a hook never receives, and when the work happens. No code changes. ## Are these changes tested? Yes. - `cargo test --doc -p datafusion library_user_guide_custom_table_providers` passes. The new example is a compiled doctest, not an `ignore` block. - `./ci/scripts/doc_prettier_check.sh` passes. - Every behavioural statement in the new text was checked against `main` with temporary sqllogictest cases, rather than read from the code alone: the ignored `LIMIT`; the pre-statement values in `SET a = b, b = a`; the error text for an external table and for a view; the scalar subquery error; the `IN` and `EXISTS` all-rows result; and the `EXPLAIN` side effect. Those cases are not part of this PR, because the last two assert behaviour that should change. ## Are there any user-facing changes? Documentation only. No change to any API. --------- Co-authored-by: Martin Grigorov <[email protected]>
1 parent 8f6e624 commit 9b469be

2 files changed

Lines changed: 82 additions & 4 deletions

File tree

‎datafusion/session/src/table.rs‎

Lines changed: 69 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -359,8 +359,38 @@ pub trait TableProvider: Any + Debug + Sync + Send {
359359

360360
/// Delete rows matching the filter predicates.
361361
///
362-
/// Returns an [`ExecutionPlan`] producing a single row with `count` (UInt64).
363-
/// Empty `filters` deletes all rows.
362+
/// Returns an [`ExecutionPlan`] producing one row in a single non-null
363+
/// `UInt64` column named `count`, containing the number of deleted rows.
364+
/// The default implementation returns a "not implemented" error.
365+
///
366+
/// # Filters
367+
///
368+
/// `filters` contains logical [`Expr`] predicates on the target table.
369+
/// The planner collects them from filters and pushed-down table scan
370+
/// predicates, splits `AND` conjunctions, removes table qualifiers, and
371+
/// deduplicates them. For example, `t.id = 1 AND t.value > 15` becomes
372+
/// separate `id = 1` and `value > 15` expressions.
373+
///
374+
/// Delete a row only when every predicate evaluates to true. SQL
375+
/// three-valued logic applies: a false or `NULL` predicate leaves the row
376+
/// unchanged. Empty `filters` deletes all rows, as for `DELETE FROM t`
377+
/// without a `WHERE` clause.
378+
///
379+
/// # Execution
380+
///
381+
/// This method is called during physical planning, including for `EXPLAIN`.
382+
/// Perform mutations when the returned plan executes, rather than while
383+
/// constructing it, so planning does not change the table.
384+
///
385+
/// # Limitations
386+
///
387+
/// The method receives no row limit ([#24998]). Subqueries in DML
388+
/// expressions are not fully supported ([#24654]); in particular, a
389+
/// subquery rewritten into a join can cause predicates to be lost before
390+
/// this method is called.
391+
///
392+
/// [#24998]: https://github.com/apache/datafusion/issues/24998
393+
/// [#24654]: https://github.com/apache/datafusion/issues/24654
364394
// Hand-written `#[async_trait]` expansion to reduce compile time. See
365395
// <https://github.com/apache/datafusion/issues/13814#issuecomment-5292709677>
366396
fn delete_from<'life0, 'life1, 'async_trait>(
@@ -381,8 +411,43 @@ pub trait TableProvider: Any + Debug + Sync + Send {
381411

382412
/// Update rows matching the filter predicates.
383413
///
384-
/// Returns an [`ExecutionPlan`] producing a single row with `count` (UInt64).
385-
/// Empty `filters` updates all rows.
414+
/// Returns an [`ExecutionPlan`] producing one row in a single non-null
415+
/// `UInt64` column named `count`, containing the number of affected rows.
416+
/// The default implementation returns a "not implemented" error.
417+
///
418+
/// # Filters
419+
///
420+
/// `filters` follows the same conventions as [`Self::delete_from`]:
421+
/// predicates are combined with `AND`, table qualifiers are removed, and
422+
/// only rows for which every predicate is true are updated. A false or
423+
/// `NULL` predicate leaves the row unchanged. Empty `filters` updates all
424+
/// rows.
425+
///
426+
/// # Assignments
427+
///
428+
/// `assignments` contains `(column_name, Expr)` pairs from the `SET`
429+
/// clause. The planner removes identity assignments and strips table
430+
/// qualifiers from the expressions. Leave columns without an assignment
431+
/// unchanged.
432+
///
433+
/// Evaluate every assignment against the row values from before the
434+
/// statement, and only for matching rows. For example, `SET a = b, b = a`
435+
/// exchanges the two values; an expression such as `100 / divisor` must
436+
/// not be evaluated on rows excluded by the filters.
437+
///
438+
/// # Execution
439+
///
440+
/// Like [`Self::delete_from`], this method is called during physical
441+
/// planning, including for `EXPLAIN`. Perform mutations when the returned
442+
/// plan executes so planning does not change the table.
443+
///
444+
/// # Limitations
445+
///
446+
/// Subqueries have the same limitations as in [`Self::delete_from`]
447+
/// ([#24654]). `UPDATE ... FROM` is not supported ([#19950]).
448+
///
449+
/// [#24654]: https://github.com/apache/datafusion/issues/24654
450+
/// [#19950]: https://github.com/apache/datafusion/issues/19950
386451
// Hand-written `#[async_trait]` expansion to reduce compile time. See
387452
// <https://github.com/apache/datafusion/issues/13814#issuecomment-5292709677>
388453
fn update<'life0, 'life1, 'async_trait>(

‎docs/source/library-user-guide/custom-table-providers.md‎

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -792,6 +792,19 @@ that a `FilterExec` is unnecessary for the `date` predicate, and the second
792792
ensures that only the relevant directories are scanned. The actual file reading
793793
happens later, in the stream produced by `execute()`.
794794

795+
## Row-Level DML: DELETE and UPDATE
796+
797+
A custom table provider can support `DELETE` and `UPDATE` by implementing the optional [`TableProvider::delete_from()`] and [`TableProvider::update()`] methods. Their default implementations return a "not implemented" error.
798+
799+
Each method builds an [ExecutionPlan] that changes the matching rows and returns the number of affected rows in a `count` column. Your provider decides how to apply and persist the changes. Perform the changes when the returned plan executes: these methods run during physical planning, including for `EXPLAIN`.
800+
801+
The method documentation describes the filters, assignments, SQL semantics, and result schema that an implementation must support. [MemTable] provides an implementation of both methods over in-memory batches.
802+
803+
For SQL syntax and examples, see the [DML section](../user-guide/sql/dml.md) of the user guide.
804+
805+
[`tableprovider::delete_from()`]: https://docs.rs/datafusion/latest/datafusion/catalog/trait.TableProvider.html#method.delete_from
806+
[`tableprovider::update()`]: https://docs.rs/datafusion/latest/datafusion/catalog/trait.TableProvider.html#method.update
807+
795808
## Putting It All Together
796809

797810
Here is a minimal but complete example of a custom table provider that generates

0 commit comments

Comments
 (0)