Repository navigation
Commit 9b469be
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
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
359 | 359 | | |
360 | 360 | | |
361 | 361 | | |
362 | | - | |
363 | | - | |
| 362 | + | |
| 363 | + | |
| 364 | + | |
| 365 | + | |
| 366 | + | |
| 367 | + | |
| 368 | + | |
| 369 | + | |
| 370 | + | |
| 371 | + | |
| 372 | + | |
| 373 | + | |
| 374 | + | |
| 375 | + | |
| 376 | + | |
| 377 | + | |
| 378 | + | |
| 379 | + | |
| 380 | + | |
| 381 | + | |
| 382 | + | |
| 383 | + | |
| 384 | + | |
| 385 | + | |
| 386 | + | |
| 387 | + | |
| 388 | + | |
| 389 | + | |
| 390 | + | |
| 391 | + | |
| 392 | + | |
| 393 | + | |
364 | 394 | | |
365 | 395 | | |
366 | 396 | | |
| |||
381 | 411 | | |
382 | 412 | | |
383 | 413 | | |
384 | | - | |
385 | | - | |
| 414 | + | |
| 415 | + | |
| 416 | + | |
| 417 | + | |
| 418 | + | |
| 419 | + | |
| 420 | + | |
| 421 | + | |
| 422 | + | |
| 423 | + | |
| 424 | + | |
| 425 | + | |
| 426 | + | |
| 427 | + | |
| 428 | + | |
| 429 | + | |
| 430 | + | |
| 431 | + | |
| 432 | + | |
| 433 | + | |
| 434 | + | |
| 435 | + | |
| 436 | + | |
| 437 | + | |
| 438 | + | |
| 439 | + | |
| 440 | + | |
| 441 | + | |
| 442 | + | |
| 443 | + | |
| 444 | + | |
| 445 | + | |
| 446 | + | |
| 447 | + | |
| 448 | + | |
| 449 | + | |
| 450 | + | |
386 | 451 | | |
387 | 452 | | |
388 | 453 | | |
| |||
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
792 | 792 | | |
793 | 793 | | |
794 | 794 | | |
| 795 | + | |
| 796 | + | |
| 797 | + | |
| 798 | + | |
| 799 | + | |
| 800 | + | |
| 801 | + | |
| 802 | + | |
| 803 | + | |
| 804 | + | |
| 805 | + | |
| 806 | + | |
| 807 | + | |
795 | 808 | | |
796 | 809 | | |
797 | 810 | | |
| |||
0 commit comments