Summary
merge_insert on an indexed composite key can fail with
Invalid user input: Ambiguous merge inserts are prohibited: multiple source rows match the same target row on (a = 1, b = 10). Please ensure each target row is matched by at most one source row.
even though no target row is matched by more than one source row.
Cause
The indexed path (create_indexed_scan_joined_stream) probes the scalar indices once per source batch. For a composite key the probe is an AND of one IsIn per key column, and those per-column lists do not correlate values across the tuple, so a batch's probe over-matches: a batch holding (1, 20) and (2, 10) probes a IsIn [1, 2] AND b IsIn [20, 10], which also reaches (1, 10) and (2, 20).
Over-matching within a batch is harmless — the downstream join applies the real key predicate. But candidate row addresses are never de-duplicated across batches, so a target row reached by two different batches is read twice. The duplicate target row then looks like two candidate matches for one source row, and the ambiguity check rejects the merge.
Reproduction
Target with the composite key rows (1,10) (1,20) (2,10) (2,20), a scalar index on both a and b, and a source split so that the second batch's probe reaches the first batch's row:
// batch 0 matches (1, 10) exactly.
let first = record_batch!(("a", Int32, [1]), ("b", Int32, [10]), ("value", Int32, [901]))?;
// batch 1's probe is a IsIn [1, 2] AND b IsIn [20, 10], which also reaches (1, 10).
let second = record_batch!(("a", Int32, [1, 2]), ("b", Int32, [20, 10]), ("value", Int32, [902, 903]))?;
MergeInsertBuilder::try_new(dataset, vec!["a".into(), "b".into()])?
.when_matched(WhenMatched::UpdateAll)
.when_not_matched(WhenNotMatched::InsertAll)
.try_build()?
.execute_reader(reader_to_stream(Box::new(RecordBatchIterator::new(
vec![Ok(first), Ok(second)],
schema,
))))
.awaitFails with the ambiguity error. The same merge succeeds with use_index(false), or with both batches concatenated into one.
Notes
Whether this triggers depends on how the source stream happens to be batched, so it is not reproducible from the merge configuration alone.
The DataFusion path grew the same defect when it learned to probe indices in #8055, and that PR fixes it there by de-duplicating candidate row addresses before the take. The fix for the legacy path is the same idea, but it is a behavior change to that path and was left out of scope.
Summary
merge_inserton an indexed composite key can fail witheven though no target row is matched by more than one source row.
Cause
The indexed path (
create_indexed_scan_joined_stream) probes the scalar indices once per source batch. For a composite key the probe is an AND of oneIsInper key column, and those per-column lists do not correlate values across the tuple, so a batch's probe over-matches: a batch holding(1, 20)and(2, 10)probesa IsIn [1, 2] AND b IsIn [20, 10], which also reaches(1, 10)and(2, 20).Over-matching within a batch is harmless — the downstream join applies the real key predicate. But candidate row addresses are never de-duplicated across batches, so a target row reached by two different batches is read twice. The duplicate target row then looks like two candidate matches for one source row, and the ambiguity check rejects the merge.
Reproduction
Target with the composite key rows
(1,10) (1,20) (2,10) (2,20), a scalar index on bothaandb, and a source split so that the second batch's probe reaches the first batch's row:Fails with the ambiguity error. The same merge succeeds with
use_index(false), or with both batches concatenated into one.Notes
Whether this triggers depends on how the source stream happens to be batched, so it is not reproducible from the merge configuration alone.
The DataFusion path grew the same defect when it learned to probe indices in #8055, and that PR fixes it there by de-duplicating candidate row addresses before the take. The fix for the legacy path is the same idea, but it is a behavior change to that path and was left out of scope.