[Feature] Support SQL DELETE on deduplicate primary-key tables in DataFusion #1379

Search before asking

• 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:

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. 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 filters the target rows, attaches the DELETE row kind, and passes them to the normal writer. PrimaryKeyTableUtils.validatePKUpsertDeletable 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!