`MERGE INTO ... WHEN MATCHED THEN UPDATE` duplicates rows instead of updating them.

[KabirBhutti]

pysail version: 0.7.1

Summary

I am seeing a data correctness issue with MERGE INTO on a partitioned Delta table.

The previous issue reported in [#2476] was related to target-side partition pruning during MERGE, and the corresponding solution was implemented in [#2478]

After that fix, I am seeing a separate issue where rows that genuinely match existing target rows are duplicated instead of updated.

When using WHEN MATCHED THEN UPDATE SET *, the existing target row is not updated in place. Instead, a second copy containing the new values is inserted, while the original stale row remains.

No error or warning is raised.

Reproduction

• Delta table is partitioned by transaction_date.
• The target partition contains approximately 50k rows.
• Selected 400 existing rows directly from the target table.
• Modified one column (status) in those rows.
• Re-merged the modified rows using transaction_id as the match key.
• Before the merge, independently confirmed that all 400 rows already existed in the target with the expected transaction_date.
• This rules out the source containing new or non-existent records.

The MERGE statement used was:

MERGE INTO t_by_date AS target
USING source_view AS source
ON target.transaction_date IN (<date literal>)
   AND target.transaction_date = source.transaction_date
   AND target.transaction_id = source.transaction_id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *

Result

After the MERGE:

• The total table row count increased by 400.
• All 400 transaction_ids now have two copies.
• The original rows remain with their old values.
• A second copy of each row appears with the updated values.
• No error or warning is reported.

Therefore, although all 400 source rows genuinely matched existing target rows, they appear to have been processed through the WHEN NOT MATCHED THEN INSERT * branch instead of WHEN MATCHED THEN UPDATE.

Diagnostic in progress

I am currently isolating whether this behavior is caused by the compound ON condition or by a more fundamental issue with WHEN MATCHED THEN UPDATE.

I am running the same update operation against the same partition using a simpler match condition, removing the static partition-pruning predicate and keeping only the date and transaction ID equality conditions.

The test is being performed on a disjoint set of existing rows from the same target partition.

I will update this issue with the result:

• Fails identically without the static predicate: likely a more fundamental issue with WHEN MATCHED THEN UPDATE.
• Only fails with the compound ON clause: likely related to the interaction between the partition predicate and the MERGE matching logic.

Expected behavior

Rows whose key columns match an existing target row should be updated in place by WHEN MATCHED THEN UPDATE SET *.

Since all 400 source rows already exist in the target, the total number of rows should remain unchanged.

Actual behavior

Matched rows are duplicated instead of updated.

The original target rows remain unchanged, while a second copy containing the updated values is inserted.

Effectively, the WHEN MATCHED branch appears not to fire, and the rows are silently treated as unmatched.

Impact

This is a data correctness issue rather than only a performance issue.

A MERGE operation intended to provide standard update-or-insert (upsert) semantics can silently create duplicate records instead of updating existing records.

Because no error or warning is raised, duplicate keys and stale rows can be introduced into the Delta table without being immediately detected.

This could affect workloads relying on MERGE INTO for incremental/upsert processing through Sail's Spark Connect implementation.

Question

Could you please take a look at this behavior and let me know whether this is something the Sail team plans to work on?

[shehabgamin]

Thx for reporting! @lonless9 Will follow-up here

[lonless9]

@KabirBhutti I couldn't reproduce this issue under the current description. Could you please provide a minimal reproducible example?