Nachiket-Roy opened a new pull request, #24982:
URL: https://github.com/apache/datafusion/pull/24982
## Which issue does this PR close?
<!--
We generally require a GitHub issue to be filed for all bug fixes and
enhancements and this helps us generate change logs for our releases. You can
link an issue to this PR using the GitHub syntax. For example `Closes #123`
indicates that this PR will close issue #123.
-->
- Closes #19617
## Rationale for this change
<!--
Why are you proposing this change? If this is already explained clearly in
the issue then this section is not needed.
Explaining clearly why changes are proposed helps reviewers understand your
changes and offer better suggestions for fixes.
Please explain the problem you are trying to solve in terms of the
user-visible
behavior, rather than the implementation.
-->
DataFusion previously rejected PostgreSQL-style `INSERT INTO ... ON CONFLICT
(col, ...) DO NOTHING` and `INSERT INTO ... ON CONFLICT (col, ...) DO UPDATE
SET ... [WHERE ...]` upsert statements during SQL planning with `"This feature
is not implemented: ON CONFLICT is not supported"`.
### Design & Architectural Approach
Rather than modifying `InsertOp` or adding new dispatch methods on
`TableProvider` (which would break external connectors such as `delta-rs`,
`iceberg-rust`, and custom catalogs across upgrades) or adding protobuf schema
churn, this change desugars `INSERT ... ON CONFLICT` into
`WriteOp::MergeInto(Box<MergeIntoOp>)` at the SQL planning stage:
1. **0% Breaking Change**: `InsertOp` remains unchanged (`Append`,
`Overwrite`, `Replace`), and `TableProvider::insert_into` is untouched.
2. **0% Protobuf Churn**: Reuses the existing `dml_node::Type::MergeInto`
protobuf representation.
3. **Instant Connector Support**: Any lakehouse connector or catalog
implementing `TableProvider::merge_into` automatically gains `ON CONFLICT`
upsert capabilities for free.
4. **PostgreSQL Semantics**:
- The incoming dataset is aliased to the standard PostgreSQL
pseudo-relation `excluded`.
- `DO NOTHING` maps to `WHEN NOT MATCHED THEN INSERT`.
- `DO UPDATE` maps to `WHEN MATCHED [AND predicate] THEN UPDATE SET ...`
followed by `WHEN NOT MATCHED THEN INSERT`.
- `NULL` conflict keys never conflict (`NULL != NULL`), taking the insert
path.
- Intra-batch hazards: duplicate conflict keys within a single statement
produce `"ON CONFLICT DO UPDATE command cannot affect row a second time"`,
while `DO NOTHING` coalesces/deduplicates them.
- Unconstrained tables (`constraints() == None` or empty): validated
against existing schema columns, and guarded against duplicate rows at runtime.
## What changes are included in this PR?
<!--
There is no need to duplicate the description in the issue here, but it is
sometimes worth providing a summary of the individual changes in this PR.
-->
### 1. SQL Planning & Desugaring (`datafusion-sql`)
- Modified
[datafusion/sql/src/statement.rs](file:///home/rosai/Downloads/datafusion/datafusion/sql/src/statement.rs):
- Removed unconditional rejection of the `on` conflict clause.
- Enforced mutual exclusivity: `ON CONFLICT` cannot be combined with
`INSERT OVERWRITE` or `REPLACE INTO`.
- Reserved alias check: Rejects target table names/aliases named
`excluded`.
- Wrapped the incoming query in `SubqueryAlias("excluded")`.
- Verified conflict columns exist in the table schema.
- **Constraint validation fallback**: When `table_source.constraints()`
contains defined constraints (`!constraints.is_empty()`), strictly validates
that target columns match a `Constraint::PrimaryKey` or `Constraint::Unique`
(failing with `"There is no unique or exclusion constraint matching the ON
CONFLICT specification"` if mismatched). When constraints are empty or `None`,
allows the operation so real connectors are not blocked.
- Constructed `MergeIntoOp` with equality join conditions between target
columns and `excluded` columns.
### 2. MemTable Reference Execution (`datafusion-catalog`)
- Implemented `TableProvider::merge_into` on `MemTable` in
[datafusion/catalog/src/memory/table.rs](file:///home/rosai/Downloads/datafusion/datafusion/catalog/src/memory/table.rs):
- **Deadlock-Free Locking**: Locks all partitions in strict ascending
index order (`0..N-1`).
- **Cross-Partition Equi-Join Index**: Builds `HashMap<RowKey,
(partition_idx, batch_idx, row_idx)>`.
- **Runtime Duplicate Key Detection**: Aborts with an explicit error if
duplicate keys are detected in unconstrained tables during index construction.
- **Intra-Batch Hazard Prevention**: Aborts on duplicate keys for `DO
UPDATE` and coalesces duplicates for `DO NOTHING`.
- **NULL Conflict Key Handling**: Rows containing `NULL` in any conflict
column bypass conflict detection and take the insert path.
- **Robust NOT MATCHED Projection**: Evaluates insert expressions against
a combined not-matched row batch, correctly filling unmentioned target columns
with typed nulls and applying type casts where required.
- **Unaliased ON Expression Parser**: Added `extract_column` to unwrap
nested `Expr::Alias` and `Expr::Cast` when extracting equi-join keys.
- **Batch Updates & Deletions Rebuild**: Updates modified columns, removes
deleted rows, appends new rows, and emits affected row counts via
`DmlResultExec`.
## What is the testing strategy for this PR?
<!--
Briefly describe how this PR is tested, and point to the specific tests you
added.
-->
1. **End-to-End Sqllogictests**:
- Added
[datafusion/sqllogictest/test_files/insert_on_conflict.slt](file:///home/rosai/Downloads/datafusion/datafusion/sqllogictest/test_files/insert_on_conflict.slt)
covering:
- Basic insert and `DO NOTHING` row skipping.
- `DO UPDATE` modifications with and without `WHERE` predicates (`where
excluded.score > users.score`).
- `NULL` conflict keys bypassing conflict detection (`NULL != NULL`).
- Intra-batch duplicate keys failing on `DO UPDATE` and coalescing on
`DO NOTHING`.
- Multi-column composite conflict keys `(a, b)`.
- Runtime duplicate key detection on unconstrained tables.
- Planning errors (mutual exclusivity with overwrite, reserved alias
`excluded`, non-existent columns, duplicate assignments).
- Updated
[datafusion/sqllogictest/test_files/merge_into.slt](file:///home/rosai/Downloads/datafusion/datafusion/sqllogictest/test_files/merge_into.slt)
to verify physical plan execution on `MemTable`.
2. **Unit & Integration Tests**:
- `datafusion/sql/tests/sql_integration.rs`: Added integration tests
verifying planned `MergeIntoOp` structures, predicates, and aliases for `DO
NOTHING` and `DO UPDATE`.
- `datafusion/sql/tests/sql_integration.rs` &
`datafusion/sql/tests/common/mod.rs`: Added 6 negative test cases in
`test_insert_schema_errors` checking mutual exclusivity, non-existent columns,
duplicate assignments, and constraint mismatches.
3. **Format & Lint**:
- Passed `cargo fmt --all -- --check`.
- Passed `cargo clippy -p datafusion-sql -p datafusion-catalog
--all-targets --all-features -- -D warnings` with zero warnings.
## Are there any user-facing changes?
<!--
If there are user-facing changes then we may require documentation to be
updated before approving the PR.
If there are any breaking changes to public APIs, please add the `api
change` label.
-->
- Users can now execute PostgreSQL-style `INSERT INTO ... ON CONFLICT (...)
DO NOTHING` and `INSERT INTO ... ON CONFLICT (...) DO UPDATE SET ...`
statements in SQL.
- `MemTable` now supports executing `MERGE INTO` queries in-memory.
- **No breaking API changes**: `InsertOp` is completely unchanged, and
existing connector implementations remain 100% compatible.
--
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]
