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: [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]
