You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "michaelsembwever (via GitHub)" <gi...@apache.org> on 2026/08/21 20:31:54 UTC
[PR] docs: document DELETE and UPDATE for SQL users and table provider authors [datafusion]
michaelsembwever opened a new pull request, #24567:
URL: https://github.com/apache/datafusion/pull/24567
## Which issue does this PR close?
- Documentation follow-up for #19142, which added the `TableProvider` row-level DML hooks and closed #16959. That PR shipped no documentation.
- Related to #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.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] docs: document DELETE and UPDATE for SQL users and table provider authors [datafusion]
Posted by "michaelsembwever (via GitHub)" <gi...@apache.org>.
michaelsembwever commented on PR #24567:
URL: https://github.com/apache/datafusion/pull/24567#issuecomment-5742195877
@alamb , is this good to merge now ?
for clarity sake, (with the issues and PRs that have spun out from this doc PR):
```mermaid
flowchart TD
DOC["pr#24567: documentation and bug warnings"]
DOC -. tracks .-> A["issue#24654: WHERE conditions lost"]
DOC -. tracks .-> B["issue#24656: EXPLAIN changes data"]
DOC -. tracks .-> C["issue#24998: DELETE ignores LIMIT"]
A --> AF["pr#24657: protect provider calls"]
B --> OLD["pr#24655: original execution-time fix"]
OLD -->|superseded by| NEW["pr#25040: replacement execution-time fix"]
C --> CF["pr#25005: reject DELETE LIMIT"]
```
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] docs: document DELETE and UPDATE for SQL users and table provider authors [datafusion]
Posted by "martin-g (via GitHub)" <gi...@apache.org>.
martin-g commented on code in PR #24567:
URL: https://github.com/apache/datafusion/pull/24567#discussion_r3950403746
##########
docs/source/user-guide/sql/dml.md:
##########
@@ -136,3 +136,114 @@ INSERT INTO <i><b>table_name</i></b> { VALUES ( <i><b>expression</i></b> [, ...]
| 2 |
+-------+
```
+
+## DELETE
+
+Removes rows from a table.
+
+<pre>
+DELETE FROM <i><b>table_name</i></b> [ WHERE <i><b>condition</i></b> ]
+</pre>
+
+`DELETE` returns the number of removed rows in a column named `count`.
+
+If you omit the `WHERE` clause, DataFusion removes all rows.
+
+DataFusion removes a row only if the condition is true for that row. SQL three-valued logic applies: if the condition evaluates to `NULL`, the row remains. For example, `WHERE value > 15` keeps a row with a `NULL` value, because `NULL > 15` is `NULL`.
+
+Not all tables support `DELETE`. See [Table support for DELETE and UPDATE](#table-support-for-delete-and-update).
+
+### Examples
+
+Remove the rows that match a condition:
+
+```sql
+> DELETE FROM target_table WHERE id > 1;
++-------+
+| count |
++-------+
+| 2 |
++-------+
+```
+
+Remove all rows:
+
+```sql
+> DELETE FROM target_table;
++-------+
+| count |
++-------+
+| 3 |
++-------+
+```
+
+## UPDATE
+
+Changes the values of existing rows.
+
+<pre>
+UPDATE <i><b>table_name</i></b> SET <i><b>column</i></b> = <i><b>expression</i></b> [, ...] [ WHERE <i><b>condition</i></b> ]
Review Comment:
```suggestion
UPDATE <i><b>table_name</b></i> SET <i><b>column</b></i> = <i><b>expression</b></i> [, ...] [ WHERE <i><b>condition</b></i> ]
```
##########
docs/source/user-guide/sql/dml.md:
##########
@@ -136,3 +136,114 @@ INSERT INTO <i><b>table_name</i></b> { VALUES ( <i><b>expression</i></b> [, ...]
| 2 |
+-------+
```
+
+## DELETE
+
+Removes rows from a table.
+
+<pre>
+DELETE FROM <i><b>table_name</i></b> [ WHERE <i><b>condition</i></b> ]
Review Comment:
```suggestion
DELETE FROM <i><b>table_name</b></i> [ WHERE <i><b>condition</b></i> ]
```
##########
docs/source/user-guide/sql/dml.md:
##########
@@ -136,3 +136,114 @@ INSERT INTO <i><b>table_name</i></b> { VALUES ( <i><b>expression</i></b> [, ...]
| 2 |
+-------+
```
+
+## DELETE
+
+Removes rows from a table.
+
+<pre>
+DELETE FROM <i><b>table_name</i></b> [ WHERE <i><b>condition</i></b> ]
+</pre>
+
+`DELETE` returns the number of removed rows in a column named `count`.
+
+If you omit the `WHERE` clause, DataFusion removes all rows.
+
+DataFusion removes a row only if the condition is true for that row. SQL three-valued logic applies: if the condition evaluates to `NULL`, the row remains. For example, `WHERE value > 15` keeps a row with a `NULL` value, because `NULL > 15` is `NULL`.
+
+Not all tables support `DELETE`. See [Table support for DELETE and UPDATE](#table-support-for-delete-and-update).
+
+### Examples
+
+Remove the rows that match a condition:
+
+```sql
+> DELETE FROM target_table WHERE id > 1;
++-------+
+| count |
++-------+
+| 2 |
++-------+
+```
+
+Remove all rows:
+
+```sql
+> DELETE FROM target_table;
++-------+
+| count |
++-------+
+| 3 |
++-------+
+```
+
+## UPDATE
+
+Changes the values of existing rows.
+
+<pre>
+UPDATE <i><b>table_name</i></b> SET <i><b>column</i></b> = <i><b>expression</i></b> [, ...] [ WHERE <i><b>condition</i></b> ]
+</pre>
+
+`UPDATE` returns the number of changed rows in a column named `count`.
Review Comment:
```suggestion
`UPDATE` returns the number of affected rows in a column named `count`.
```
nit: Maybe `affected` would be better here because old and new values are not compared.
UPDATE-ing to the same value is counted but it is not really `changed`.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] docs: document DELETE and UPDATE for SQL users and table provider authors [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #24567:
URL: https://github.com/apache/datafusion/pull/24567#issuecomment-5400611449
Specifically, I want to make sure the issues are tracked (ideally with a link in the docs as well) so that as we resolve them we also know to come and update the docs
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] docs: document DELETE and UPDATE for SQL users and table provider authors [datafusion]
Posted by "codecov-commenter (via GitHub)" <gi...@apache.org>.
codecov-commenter commented on PR #24567:
URL: https://github.com/apache/datafusion/pull/24567#issuecomment-5375354803
## [Codecov](https://app.codecov.io/gh/apache/datafusion/pull/24567?dropdown=coverage&src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) Report
:white_check_mark: All modified and coverable lines are covered by tests.
:white_check_mark: Project coverage is 81.36%. Comparing base ([`a6e2d3f`](https://app.codecov.io/gh/apache/datafusion/commit/a6e2d3f7a43b8007817889875b662fa0463468c7?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache)) to head ([`8dfa9c1`](https://app.codecov.io/gh/apache/datafusion/commit/8dfa9c15a199abd612c4139ee0a3035bb48a3aa4?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache)).
:warning: Report is 1 commits behind head on main.
<details><summary>Additional details and impacted files</summary>
```diff
@@ Coverage Diff @@
## main #24567 +/- ##
==========================================
- Coverage 81.36% 81.36% -0.01%
==========================================
Files 1117 1117
Lines 397872 397916 +44
Branches 397872 397916 +44
==========================================
+ Hits 323725 323751 +26
- Misses 55229 55243 +14
- Partials 18918 18922 +4
```
</details>
[:umbrella: View full report in Codecov by Harness](https://app.codecov.io/gh/apache/datafusion/pull/24567?dropdown=coverage&src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache).
:loudspeaker: Have feedback on the report? [Share it here](https://about.codecov.io/codecov-pr-comment-feedback/?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache).
<details><summary> :rocket: New features to boost your workflow: </summary>
- :snowflake: [Test Analytics](https://docs.codecov.com/docs/test-analytics): Detect flaky tests, report on failures, and find test suite problems.
- :package: [JS Bundle Analysis](https://docs.codecov.com/docs/javascript-bundle-analysis): Save yourself from yourself by tracking and limiting bundle sizes in JS merges.
</details>
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] docs: document DELETE and UPDATE for SQL users and table provider authors [datafusion]
Posted by "michaelsembwever (via GitHub)" <gi...@apache.org>.
michaelsembwever commented on code in PR #24567:
URL: https://github.com/apache/datafusion/pull/24567#discussion_r4057918814
##########
docs/source/user-guide/sql/dml.md:
##########
@@ -136,3 +136,114 @@ INSERT INTO <i><b>table_name</i></b> { VALUES ( <i><b>expression</i></b> [, ...]
| 2 |
+-------+
```
+
+## DELETE
+
+Removes rows from a table.
+
+<pre>
+DELETE FROM <i><b>table_name</b></i> [ WHERE <i><b>condition</b></i> ]
+</pre>
+
+`DELETE` returns the number of removed rows in a column named `count`.
+
+If you omit the `WHERE` clause, DataFusion removes all rows.
+
+DataFusion removes a row only if the condition is true for that row. SQL three-valued logic applies: if the condition evaluates to `NULL`, the row remains. For example, `WHERE value > 15` keeps a row with a `NULL` value, because `NULL > 15` is `NULL`.
+
+Not all tables support `DELETE`. See [Table support for DELETE and UPDATE](#table-support-for-delete-and-update).
+
+### Examples
+
+Remove the rows that match a condition:
+
+```sql
+> DELETE FROM target_table WHERE id > 1;
++-------+
+| count |
++-------+
+| 2 |
++-------+
+```
+
+Remove all rows:
+
+```sql
+> DELETE FROM target_table;
++-------+
+| count |
++-------+
+| 3 |
++-------+
+```
+
+## UPDATE
+
+Changes the values of existing rows.
+
+<pre>
+UPDATE <i><b>table_name</b></i> SET <i><b>column</b></i> = <i><b>expression</b></i> [, ...] [ WHERE <i><b>condition</b></i> ]
+</pre>
+
+`UPDATE` returns the number of affected rows in a column named `count`.
+
+If you omit the `WHERE` clause, DataFusion changes all rows. The three-valued logic of `DELETE` also applies here.
+
+Each assignment expression reads the row values from before the statement. `SET a = b, b = a` therefore exchanges the two values.
+
+Not all tables support `UPDATE`. See [Table support for DELETE and UPDATE](#table-support-for-delete-and-update).
+
+### Examples
+
+Set one column in the rows that match a condition:
+
+```sql
+> UPDATE target_table SET name = 'Baz' WHERE id = 2;
++-------+
+| count |
++-------+
+| 1 |
++-------+
+```
+
+Set two columns, one from an expression:
+
+```sql
+> UPDATE target_table SET value = value * 2, name = 'Doubled' WHERE id < 3;
++-------+
+| count |
++-------+
+| 2 |
++-------+
+```
+
+## Table support for DELETE and UPDATE
+
+The table provider does the work for `DELETE` and `UPDATE`. Support is therefore a property of each table:
+
+- `CREATE TABLE` makes an in-memory table. In-memory tables support both statements.
+- `CREATE EXTERNAL TABLE` makes a file-based table. File-based tables support neither statement.
+- Views support neither statement.
+- A custom table provider supports a statement only if it implements the matching hook. See [Custom Table Provider](../../library-user-guide/custom-table-providers.md#row-level-dml-delete-and-update).
+
+A table that gives no support returns an error:
+
+```text
+DELETE operation on table 'my_external_table'
+caused by
+This feature is not implemented: DELETE not supported for Base table
+```
+
+### Limitations
+
+:::{warning}
Review Comment:
done https://github.com/apache/datafusion/pull/24567/changes/e4f2a13d8d4e184e4248af6882aa65464bc0c71e
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] docs: document DELETE and UPDATE for SQL users and table provider authors [datafusion]
Posted by "michaelsembwever (via GitHub)" <gi...@apache.org>.
michaelsembwever commented on PR #24567:
URL: https://github.com/apache/datafusion/pull/24567#issuecomment-5572511762
> ideally with a link in the docs as well
> …
> I think this PR adds useful information about some current limitations in DataFusion.
> It would be nice if every limitation is accompanied with text similar to "This limitation is tracked at issue [XYZ (https://github.com/apache/datafusion/issues/XYZ)"
> Once the limitation is implemented it would be easier to detect that the documentation is obsolete and be updated too.
Every limitation now ends with "This limitation is tracked at issue NNNNN": #24654 for the subquery cases, #24656 for EXPLAIN, #24998 for the ignored LIMIT, and #19950 for UPDATE ... FROM. Three of the four have an open fix (#24657, #24655, #25005), so whichever of those merges after this PR should drop the matching paragraph.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] docs: document DELETE and UPDATE for SQL users and table provider authors [datafusion]
Posted by "michaelsembwever (via GitHub)" <gi...@apache.org>.
michaelsembwever commented on code in PR #24567:
URL: https://github.com/apache/datafusion/pull/24567#discussion_r4057915742
##########
docs/source/user-guide/sql/dml.md:
##########
@@ -136,3 +136,114 @@ INSERT INTO <i><b>table_name</i></b> { VALUES ( <i><b>expression</i></b> [, ...]
| 2 |
+-------+
```
+
+## DELETE
Review Comment:
done: https://github.com/apache/datafusion/pull/25553
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] docs: document DELETE and UPDATE for SQL users and table provider authors [datafusion]
Posted by "michaelsembwever (via GitHub)" <gi...@apache.org>.
michaelsembwever commented on PR #24567:
URL: https://github.com/apache/datafusion/pull/24567#issuecomment-5408127409
> Are there tickets that cover these issues?
@alamb , create them
- https://github.com/apache/datafusion/issues/24654
- https://github.com/apache/datafusion/issues/24656
with PRs attached to each.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] docs: document DELETE and UPDATE for SQL users and table provider authors [datafusion]
Posted by "michaelsembwever (via GitHub)" <gi...@apache.org>.
michaelsembwever commented on PR #24567:
URL: https://github.com/apache/datafusion/pull/24567#issuecomment-5529036056
@alamb , are we good for merging this now ?
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] docs: document DELETE and UPDATE for SQL users and table provider authors [datafusion]
Posted by "michaelsembwever (via GitHub)" <gi...@apache.org>.
michaelsembwever commented on code in PR #24567:
URL: https://github.com/apache/datafusion/pull/24567#discussion_r4057917178
##########
docs/source/library-user-guide/custom-table-providers.md:
##########
@@ -792,6 +792,141 @@ that a `FilterExec` is unnecessary for the `date` predicate, and the second
ensures that only the relevant directories are scanned. The actual file reading
happens later, in the stream produced by `execute()`.
+## Row-Level DML: DELETE and UPDATE
+
+`TableProvider` has two optional hooks for row-level Data Manipulation Language (DML) statements:
+
+- `delete_from(state, filters)` serves `DELETE FROM t [WHERE ...]`.
+- `update(state, assignments, filters)` serves `UPDATE t SET ... [WHERE ...]`.
+
+Both hooks have a default implementation that returns a "not implemented" error. A provider that does not override them keeps its previous read-only behaviour, and DataFusion reports the statement as unsupported for that table.
+
+The physical planner calls the hook instead of building a plan of its own. Your provider therefore owns the whole row change: which rows change, how to make the change durable, and how many rows the statement affected.
+
+### What the Planner Passes to the Hooks
+
+`filters` holds the `WHERE` predicates as logical `Expr` values, after three transformations:
Review Comment:
done in https://github.com/apache/datafusion/pull/24567/changes/e4f2a13d8d4e184e4248af6882aa65464bc0c71e
##########
docs/source/user-guide/sql/dml.md:
##########
@@ -136,3 +136,114 @@ INSERT INTO <i><b>table_name</i></b> { VALUES ( <i><b>expression</i></b> [, ...]
| 2 |
+-------+
```
+
+## DELETE
+
+Removes rows from a table.
+
+<pre>
+DELETE FROM <i><b>table_name</b></i> [ WHERE <i><b>condition</b></i> ]
+</pre>
+
+`DELETE` returns the number of removed rows in a column named `count`.
+
+If you omit the `WHERE` clause, DataFusion removes all rows.
+
+DataFusion removes a row only if the condition is true for that row. SQL three-valued logic applies: if the condition evaluates to `NULL`, the row remains. For example, `WHERE value > 15` keeps a row with a `NULL` value, because `NULL > 15` is `NULL`.
+
+Not all tables support `DELETE`. See [Table support for DELETE and UPDATE](#table-support-for-delete-and-update).
+
+### Examples
+
+Remove the rows that match a condition:
+
+```sql
+> DELETE FROM target_table WHERE id > 1;
++-------+
+| count |
++-------+
+| 2 |
++-------+
+```
+
+Remove all rows:
+
+```sql
+> DELETE FROM target_table;
++-------+
+| count |
++-------+
+| 3 |
++-------+
+```
+
+## UPDATE
+
+Changes the values of existing rows.
+
+<pre>
+UPDATE <i><b>table_name</b></i> SET <i><b>column</b></i> = <i><b>expression</b></i> [, ...] [ WHERE <i><b>condition</b></i> ]
+</pre>
+
+`UPDATE` returns the number of affected rows in a column named `count`.
+
+If you omit the `WHERE` clause, DataFusion changes all rows. The three-valued logic of `DELETE` also applies here.
+
+Each assignment expression reads the row values from before the statement. `SET a = b, b = a` therefore exchanges the two values.
+
+Not all tables support `UPDATE`. See [Table support for DELETE and UPDATE](#table-support-for-delete-and-update).
+
+### Examples
+
+Set one column in the rows that match a condition:
+
+```sql
+> UPDATE target_table SET name = 'Baz' WHERE id = 2;
++-------+
+| count |
++-------+
+| 1 |
++-------+
+```
+
+Set two columns, one from an expression:
+
+```sql
+> UPDATE target_table SET value = value * 2, name = 'Doubled' WHERE id < 3;
++-------+
+| count |
++-------+
+| 2 |
++-------+
+```
+
+## Table support for DELETE and UPDATE
+
+The table provider does the work for `DELETE` and `UPDATE`. Support is therefore a property of each table:
Review Comment:
done in https://github.com/apache/datafusion/pull/24567/changes/e4f2a13d8d4e184e4248af6882aa65464bc0c71e
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] docs: document DELETE and UPDATE for SQL users and table provider authors [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb merged PR #24567:
URL: https://github.com/apache/datafusion/pull/24567
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] docs: document DELETE and UPDATE for SQL users and table provider authors [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on PR #24567:
URL: https://github.com/apache/datafusion/pull/24567#issuecomment-5400583582
> Both behaviours need code fixes, which this PR does not attempt. Until then a reader needs the warning.
Are there tickets that cover these issues? I agree they sound serious
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] docs: document DELETE and UPDATE for SQL users and table provider authors [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on code in PR #24567:
URL: https://github.com/apache/datafusion/pull/24567#discussion_r4057085550
##########
docs/source/library-user-guide/custom-table-providers.md:
##########
@@ -792,6 +792,141 @@ that a `FilterExec` is unnecessary for the `date` predicate, and the second
ensures that only the relevant directories are scanned. The actual file reading
happens later, in the stream produced by `execute()`.
+## Row-Level DML: DELETE and UPDATE
+
+`TableProvider` has two optional hooks for row-level Data Manipulation Language (DML) statements:
+
+- `delete_from(state, filters)` serves `DELETE FROM t [WHERE ...]`.
+- `update(state, assignments, filters)` serves `UPDATE t SET ... [WHERE ...]`.
+
+Both hooks have a default implementation that returns a "not implemented" error. A provider that does not override them keeps its previous read-only behaviour, and DataFusion reports the statement as unsupported for that table.
+
+The physical planner calls the hook instead of building a plan of its own. Your provider therefore owns the whole row change: which rows change, how to make the change durable, and how many rows the statement affected.
+
+### What the Planner Passes to the Hooks
+
+`filters` holds the `WHERE` predicates as logical `Expr` values, after three transformations:
Review Comment:
Would you be willing to move some of this documentation on to the TableProvider::delete_from and TableProvider::update methods themselves?
Here:
https://github.com/apache/datafusion/blob/3b16a3d0eef765b81ce5aad20997b193a42ad02a/datafusion/session/src/table.rs#L360-L380
That will result in them being available in https://docs.rs/datafusion/latest/datafusion/catalog/trait.TableProvider.html as well as the source code so I think it will be more discoverable
Then the idea is that this library user guide can help provide a more user friendly overview / introduction
##########
docs/source/user-guide/sql/dml.md:
##########
@@ -136,3 +136,114 @@ INSERT INTO <i><b>table_name</i></b> { VALUES ( <i><b>expression</i></b> [, ...]
| 2 |
+-------+
```
+
+## DELETE
Review Comment:
I recommend pulling the sql/dml.sql guide updates into their own PR for faster review / merging
##########
docs/source/user-guide/sql/dml.md:
##########
@@ -136,3 +136,114 @@ INSERT INTO <i><b>table_name</i></b> { VALUES ( <i><b>expression</i></b> [, ...]
| 2 |
+-------+
```
+
+## DELETE
+
+Removes rows from a table.
+
+<pre>
+DELETE FROM <i><b>table_name</b></i> [ WHERE <i><b>condition</b></i> ]
+</pre>
+
+`DELETE` returns the number of removed rows in a column named `count`.
+
+If you omit the `WHERE` clause, DataFusion removes all rows.
+
+DataFusion removes a row only if the condition is true for that row. SQL three-valued logic applies: if the condition evaluates to `NULL`, the row remains. For example, `WHERE value > 15` keeps a row with a `NULL` value, because `NULL > 15` is `NULL`.
+
+Not all tables support `DELETE`. See [Table support for DELETE and UPDATE](#table-support-for-delete-and-update).
+
+### Examples
+
+Remove the rows that match a condition:
+
+```sql
+> DELETE FROM target_table WHERE id > 1;
++-------+
+| count |
++-------+
+| 2 |
++-------+
+```
+
+Remove all rows:
+
+```sql
+> DELETE FROM target_table;
++-------+
+| count |
++-------+
+| 3 |
++-------+
+```
+
+## UPDATE
+
+Changes the values of existing rows.
+
+<pre>
+UPDATE <i><b>table_name</b></i> SET <i><b>column</b></i> = <i><b>expression</b></i> [, ...] [ WHERE <i><b>condition</b></i> ]
+</pre>
+
+`UPDATE` returns the number of affected rows in a column named `count`.
+
+If you omit the `WHERE` clause, DataFusion changes all rows. The three-valued logic of `DELETE` also applies here.
+
+Each assignment expression reads the row values from before the statement. `SET a = b, b = a` therefore exchanges the two values.
+
+Not all tables support `UPDATE`. See [Table support for DELETE and UPDATE](#table-support-for-delete-and-update).
+
+### Examples
+
+Set one column in the rows that match a condition:
+
+```sql
+> UPDATE target_table SET name = 'Baz' WHERE id = 2;
++-------+
+| count |
++-------+
+| 1 |
++-------+
+```
+
+Set two columns, one from an expression:
+
+```sql
+> UPDATE target_table SET value = value * 2, name = 'Doubled' WHERE id < 3;
++-------+
+| count |
++-------+
+| 2 |
++-------+
+```
+
+## Table support for DELETE and UPDATE
+
+The table provider does the work for `DELETE` and `UPDATE`. Support is therefore a property of each table:
+
+- `CREATE TABLE` makes an in-memory table. In-memory tables support both statements.
+- `CREATE EXTERNAL TABLE` makes a file-based table. File-based tables support neither statement.
+- Views support neither statement.
+- A custom table provider supports a statement only if it implements the matching hook. See [Custom Table Provider](../../library-user-guide/custom-table-providers.md#row-level-dml-delete-and-update).
+
+A table that gives no support returns an error:
+
+```text
+DELETE operation on table 'my_external_table'
+caused by
+This feature is not implemented: DELETE not supported for Base table
+```
+
+### Limitations
+
+:::{warning}
Review Comment:
I a not sure we need to spell out the deatils of the active bugs in the SQL reference manual. If you think it is valuable, we could list them, but let users follow the links if they want more details
##########
docs/source/user-guide/sql/dml.md:
##########
@@ -136,3 +136,114 @@ INSERT INTO <i><b>table_name</i></b> { VALUES ( <i><b>expression</i></b> [, ...]
| 2 |
+-------+
```
+
+## DELETE
+
+Removes rows from a table.
+
+<pre>
+DELETE FROM <i><b>table_name</b></i> [ WHERE <i><b>condition</b></i> ]
+</pre>
+
+`DELETE` returns the number of removed rows in a column named `count`.
+
+If you omit the `WHERE` clause, DataFusion removes all rows.
+
+DataFusion removes a row only if the condition is true for that row. SQL three-valued logic applies: if the condition evaluates to `NULL`, the row remains. For example, `WHERE value > 15` keeps a row with a `NULL` value, because `NULL > 15` is `NULL`.
+
+Not all tables support `DELETE`. See [Table support for DELETE and UPDATE](#table-support-for-delete-and-update).
+
+### Examples
+
+Remove the rows that match a condition:
+
+```sql
+> DELETE FROM target_table WHERE id > 1;
++-------+
+| count |
++-------+
+| 2 |
++-------+
+```
+
+Remove all rows:
+
+```sql
+> DELETE FROM target_table;
++-------+
+| count |
++-------+
+| 3 |
++-------+
+```
+
+## UPDATE
+
+Changes the values of existing rows.
+
+<pre>
+UPDATE <i><b>table_name</b></i> SET <i><b>column</b></i> = <i><b>expression</b></i> [, ...] [ WHERE <i><b>condition</b></i> ]
+</pre>
+
+`UPDATE` returns the number of affected rows in a column named `count`.
+
+If you omit the `WHERE` clause, DataFusion changes all rows. The three-valued logic of `DELETE` also applies here.
+
+Each assignment expression reads the row values from before the statement. `SET a = b, b = a` therefore exchanges the two values.
+
+Not all tables support `UPDATE`. See [Table support for DELETE and UPDATE](#table-support-for-delete-and-update).
+
+### Examples
+
+Set one column in the rows that match a condition:
+
+```sql
+> UPDATE target_table SET name = 'Baz' WHERE id = 2;
++-------+
+| count |
++-------+
+| 1 |
++-------+
+```
+
+Set two columns, one from an expression:
+
+```sql
+> UPDATE target_table SET value = value * 2, name = 'Doubled' WHERE id < 3;
++-------+
+| count |
++-------+
+| 2 |
++-------+
+```
+
+## Table support for DELETE and UPDATE
+
+The table provider does the work for `DELETE` and `UPDATE`. Support is therefore a property of each table:
Review Comment:
I think this section and down has too much implementation detail and we don't have to spell out exactly how UPDATE / DELETE is implemented as part of the user guide (targeting SQL users). We could perhaps just say something like "not all table providers support UPDATE and DELETE" ?
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org