feat: support partition-scoped INSERT OVERWRITE semantics via SQL

[puchengy]

Problem

Teams migrating partitioned tables from Hive / Iceberg rely heavily on INSERT OVERWRITE scoped to a partition: "replace exactly the rows for dt = X with this new data, atomically." Lance has no partition concept (see Partitioning (or not) for Lance · Discussion #4125, where partitioning was deprioritized), and today the Spark connector offers no equivalent:

• DELETE + INSERT — two separate commits, so a reader can observe the region deleted-but-not-yet-reinserted, and a failure between them leaves the region missing. Not atomic.
• whole-table INSERT OVERWRITE — atomic, but replaces the entire table, not a partition.
• MERGE INTO — atomic, but it cannot cleanly express a partition replacement, and it forces a shuffle.

Why MERGE INTO is not a substitute

Consider a table logically partitioned by a date column:

1, 2, 2026-01-01
3, 4, 2026-01-01
5, 6, 2026-01-02

and source data intended to overwrite the 2026-01-01 partition:

a, b, 2026-01-01

There is no correct MERGE INTO ... ON <date> that yields the desired result:

• Keying on the date column is not a row key, so old rows match new rows many-to-many. WHEN MATCHED THEN UPDATE raises a cardinality violation.
• Switching to WHEN MATCHED THEN DELETE + WHEN NOT MATCHED THEN INSERT never inserts the new rows, because they do match existing rows on the date and so are not "not matched."

MERGE is a row-to-row upsert, not a set-replace, and additionally pays a join/shuffle cost. So it does not cover the partition-overwrite pattern.

Proposal

Expose a predicate-scoped, atomic overwrite in the Spark connector, implemented on top of the format's Update transaction (which removes some fragments and adds others in a single commit — an atomic delete + append). Rows to replace are chosen by a predicate rather than a declared partition spec, since Lance has none:

REPLACE lance.db.events
  WHERE dt = '2026-08-01'
  AS SELECT id, dt, value FROM staging_events WHERE dt = '2026-08-01';

Semantics:

• Delete of matching rows + append of new rows commit as one table version (readers never see a half-applied state; a crash cannot leave the region half-written).
• Fragments fully covered by the predicate are dropped by id; fragments that only partially match keep their non-matching rows via a deletion vector.
• A predicate matching no existing rows degrades to a plain append.

Status

A WIP implementation is up: #756 (REPLACE ... WHERE ... AS <query>, reusing the existing write pipeline and committing a single Operation.Update). Filing this issue to track the feature and gather design feedback — in particular whether a predicate-scoped REPLACE is the preferred surface, versus a dynamic-partition-style INSERT OVERWRITE or the upcoming liquid-clustering work.

[puchengy]

fyi @jackye1995

[puchengy]

Follow-up optimization: metadata-only fragment removal via zonemap

Verified how the current REPLACE ... WHERE (in #756) interacts with zonemap scalar indexes, and there's a worthwhile performance follow-up.

What already works (no change needed): the delete-planning scan (ScanOptions.filter(predicate).withRowAddress(true)) engages scalar-index pushdown automatically — use_scalar_index defaults to true, and zonemaps prune zones via the standard ScalarIndex::search path. A conjunctive predicate like dt = X AND hr = Y uses two independent single-column zonemaps and intersects their surviving row-address ranges (ScalarIndexExpr::And), so no composite index is required. Net: on a partition-aligned, zonemap-indexed table, the scan is pruned to roughly just the target partition instead of the whole table.

The remaining inefficiency: even after pruning, the current implementation still enumerates the _rowaddr of every matching row in the target partition to build the deletion set and prove full coverage (matched.getLongCardinality() == fragment.getNumRows()). So removing an aligned partition is O(matched rows), not O(#fragments) — it is not yet a true metadata-only partition drop.

Proposed optimization: for a fragment the zonemap can prove is fully covered by the predicate (every zone on the filtered column has min == max == the predicate value — the detectFragmentKeys idea), add its id straight to removedFragmentIds with no row scan at all. Only fragments the index cannot prove fully-covered (straddling boundaries, unindexed columns, uncovered/new fragments) fall back to the existing scan-based path. That would make the aligned case genuinely metadata-only — O(#fragments) — matching Iceberg REPLACE PARTITION cost, while preserving the current path as the always-correct fallback.

This is a strict performance enhancement on top of the correct scan-based implementation and can land after #756.