SteNicholas opened a new issue, #1379:
URL: https://github.com/apache/paimon-rust/issues/1379

   ### Search before asking
   
   - [x] I searched existing issues and pull requests and found no issue 
tracking this specific SQL DELETE limitation.
   
   ### Motivation
   
   DataFusion SQL currently rejects DELETE on every primary-key table with 
`DELETE on primary-key tables is not yet supported`. This leaves a gap between 
the core primary-key writer, which supports DELETE row kinds, and the SQL 
interface.
   
   For example, with a Paimon catalog and database configured:
   
   ```sql
   CREATE TABLE paimon.test_db.pk_delete_example (
       id INT NOT NULL,
       name STRING,
       PRIMARY KEY (id)
   ) WITH ('bucket' = '1', 'merge-engine' = 'deduplicate');
   
   INSERT INTO paimon.test_db.pk_delete_example VALUES (1, 'a'), (2, 'b');
   DELETE FROM paimon.test_db.pk_delete_example WHERE id = 1;
   ```
   
   The DELETE is rejected today. For the proposed supported configuration, it 
should leave only `(2, 'b')`.
   
   Current Rust implementation: 
[`execute_delete`](https://github.com/apache/paimon-rust/blob/f91fdb8ab034887547a5ee2f26c1979e30250558/crates/integrations/datafusion/src/delete.rs#L65-L68).
 The existing `test_delete_rejects_primary_key_table` integration test also 
records this restriction.
   
   ### Solution
   
   Start with ordinary primary-key tables using `merge-engine=deduplicate`, 
reusing the existing write/commit pipeline:
   
   - Scan the logical rows matching the SQL predicate.
   - Write the matched rows as DELETE records through `TableWrite`, preserving 
the primary-key, partition and configured sequence-field values required by the 
writer.
   - Prepare commit messages and publish them through the existing commit path, 
returning the affected-row count. A no-match delete should be a no-op.
   - Keep other merge engines explicitly unsupported in this first change.
   
   Java/Spark provides a reference: 
[`performPrimaryKeyDelete`](https://github.com/apache/paimon/blob/3821db1972b87d5cd3ac1d025ec214cc3842ae9a/paimon-spark/paimon-spark-common/src/main/scala/org/apache/paimon/spark/commands/DeleteFromPaimonTableCommand.scala#L64-L68)
 filters the target rows, attaches the DELETE row kind, and passes them to the 
normal writer. 
[`PrimaryKeyTableUtils.validatePKUpsertDeletable`](https://github.com/apache/paimon/blob/3821db1972b87d5cd3ac1d025ec214cc3842ae9a/paimon-core/src/main/java/org/apache/paimon/table/PrimaryKeyTableUtils.java#L119-L163)
 defines the merge-engine eligibility.
   
   Before enabling the SQL path, define its read-snapshot, concurrent-upsert 
and retry behavior. In particular, preserve configured sequence ordering rather 
than assuming a DELETE always wins, and distinguish a commit retry from 
re-executing the DELETE query against a newer snapshot.
   
   Suggested validation:
   
   - Predicate deletes, DELETE without WHERE, no matches, and SQL NULL 
predicate semantics.
   - Partitioned tables, composite primary keys, and multiple stored versions 
of one key.
   - Configured sequence fields and deterministic 
concurrent-upsert/commit-retry cases.
   - Cleanup of uncommitted outputs on failure, existing SQL target 
restrictions, and rejection of unsupported merge engines.
   
   ### Anything else?
   
   Source baseline: paimon-rust `f91fdb8ab034887547a5ee2f26c1979e30250558`, 
compared with apache/paimon `3821db1972b87d5cd3ac1d025ec214cc3842ae9a`.
   
   This proposal does not include SQL UPDATE, extending 
partial-update/aggregation/first-row DELETE semantics, or changing the existing 
append-table and Data Evolution DELETE paths.
   
   ### Willingness to contribute
   
   - [ ] I'm willing to submit a PR!
   


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

Reply via email to