FAILURE MAP
← Case archive

FA-45391 / Data systems / Open access

MERGE preflight detects duplicate whole source rows instead of duplicate keys · case 01

MERGE preflight detects duplicate whole source rows instead of duplicate keys.

Verified by executionVariant 1 · 7 checks per implementationDownload source bundle ↓JSON ↗

ROOT CAUSE

merge-statement-preflight: MERGE preflight detects duplicate whole source rows instead of duplicate keys.

VERIFIED REPAIR

Preserve the stated physical representation and operation order: Merge source [key,value] into a target unique-key table. Any repeated source key is CARDINALITY and leaves target unchanged; otherwise matched keys replace payloads and unmatched keys insert. Return status and sorted final rows. Source null keys are ordinary distinct keys in this stipulated integer-key model only (inputs here are integer keys).

Unsuccessful approach: Payload equality is not source-key cardinality.

Case contract

Merge source [key,value] into a target unique-key table. Any repeated source key is CARDINALITY and leaves target unchanged; otherwise matched keys replace payloads and unmatched keys insert. Return status and sorted final rows. Source null keys are ordinary distinct keys in this stipulated integer-key model only (inputs here are integer keys).

Why this case matters

A bounded deterministic data engine model makes representation and changelog faults reproducible.

1 / The failure

Exit 1
"""Failure Map reference implementation. Python standard library only."""
import json

N = 1
observations = []
def solve(d):
    try:
        target,source=d
        state=dict(target)
        keys=[k for k,v in source]
        if len(set(tuple(r) for r in source))!=len(source): return ['CARDINALITY',sorted(target)]
        for key,value in source:
            state[key]=value
        return ['OK',sorted([[k,v] for k,v in state.items()])]
    except (IndexError, KeyError, ValueError, StopIteration) as exc:
        return {"representation_error": type(exc).__name__}
def check(label, actual, expected):
    observations.append({"check": label, "actual": actual, "expected": expected, "passed": actual == expected})
if N == 1:
    check('matched update', solve([[[10, 1]], [[10, 2]]]), ['OK', [[10, 2]]])
    check('unmatched insert', solve([[[10, 1]], [[11, 2]]]), ['OK', [[10, 1], [11, 2]]])
    check('new duplicate keys', solve([[], [[10, 1], [10, 2]]]), ['CARDINALITY', []])
    check('existing duplicate keys', solve([[[10, 1]], [[10, 2], [10, 3]]]), ['CARDINALITY', [[10, 1]]])
    check('duplicate payload not key', solve([[], [[10, 1], [11, 1]]]), ['OK', [[10, 1], [11, 1]]])
    check('empty source', solve([[[10, 1]], []]), ['OK', [[10, 1]]])
    check('empty target', solve([[], [[10, 1]]]), ['OK', [[10, 1]]])
elif N == 2:
    check('matched update', solve([[[10, 2]], [[10, 3]]]), ['OK', [[10, 3]]])
    check('unmatched insert', solve([[[10, 2]], [[11, 3]]]), ['OK', [[10, 2], [11, 3]]])
    check('new duplicate keys', solve([[], [[10, 2], [10, 3]]]), ['CARDINALITY', []])
    check('existing duplicate keys', solve([[[10, 2]], [[10, 3], [10, 4]]]), ['CARDINALITY', [[10, 2]]])
    check('duplicate payload not key', solve([[], [[10, 2], [11, 2]]]), ['OK', [[10, 2], [11, 2]]])
    check('empty source', solve([[[10, 2]], []]), ['OK', [[10, 2]]])
    check('empty target', solve([[], [[10, 2]]]), ['OK', [[10, 2]]])
elif N == 3:
    check('matched update', solve([[[10, 3]], [[10, 4]]]), ['OK', [[10, 4]]])
    check('unmatched insert', solve([[[10, 3]], [[11, 4]]]), ['OK', [[10, 3], [11, 4]]])
    check('new duplicate keys', solve([[], [[10, 3], [10, 4]]]), ['CARDINALITY', []])
    check('existing duplicate keys', solve([[[10, 3]], [[10, 4], [10, 5]]]), ['CARDINALITY', [[10, 3]]])
    check('duplicate payload not key', solve([[], [[10, 3], [11, 3]]]), ['OK', [[10, 3], [11, 3]]])
    check('empty source', solve([[[10, 3]], []]), ['OK', [[10, 3]]])
    check('empty target', solve([[], [[10, 3]]]), ['OK', [[10, 3]]])
elif N == 4:
    check('matched update', solve([[[10, 4]], [[10, 5]]]), ['OK', [[10, 5]]])
    check('unmatched insert', solve([[[10, 4]], [[11, 5]]]), ['OK', [[10, 4], [11, 5]]])
    check('new duplicate keys', solve([[], [[10, 4], [10, 5]]]), ['CARDINALITY', []])
    check('existing duplicate keys', solve([[[10, 4]], [[10, 5], [10, 6]]]), ['CARDINALITY', [[10, 4]]])
    check('duplicate payload not key', solve([[], [[10, 4], [11, 4]]]), ['OK', [[10, 4], [11, 4]]])
    check('empty source', solve([[[10, 4]], []]), ['OK', [[10, 4]]])
    check('empty target', solve([[], [[10, 4]]]), ['OK', [[10, 4]]])
elif N == 5:
    check('matched update', solve([[[10, 5]], [[10, 6]]]), ['OK', [[10, 6]]])
    check('unmatched insert', solve([[[10, 5]], [[11, 6]]]), ['OK', [[10, 5], [11, 6]]])
    check('new duplicate keys', solve([[], [[10, 5], [10, 6]]]), ['CARDINALITY', []])
    check('existing duplicate keys', solve([[[10, 5]], [[10, 6], [10, 7]]]), ['CARDINALITY', [[10, 5]]])
    check('duplicate payload not key', solve([[], [[10, 5], [11, 5]]]), ['OK', [[10, 5], [11, 5]]])
    check('empty source', solve([[[10, 5]], []]), ['OK', [[10, 5]]])
    check('empty target', solve([[], [[10, 5]]]), ['OK', [[10, 5]]])
print(json.dumps({"observations": observations, "passed": all(x["passed"] for x in observations)}, ensure_ascii=False))
raise SystemExit(0 if all(x["passed"] for x in observations) else 1)
Boundary fixtureActualExpectedOutcome
matched update['OK', [[10, 2]]]['OK', [[10, 2]]]Passed
unmatched insert['OK', [[10, 1], [11, 2]]]['OK', [[10, 1], [11, 2]]]Passed
new duplicate keys['OK', [[10, 2]]]['CARDINALITY', []]Failed
existing duplicate keys['OK', [[10, 3]]]['CARDINALITY', [[10, 1]]]Failed
duplicate payload not key['OK', [[10, 1], [11, 1]]]['OK', [[10, 1], [11, 1]]]Passed
empty source['OK', [[10, 1]]]['OK', [[10, 1]]]Passed
empty target['OK', [[10, 1]]]['OK', [[10, 1]]]Passed

SHA-256 / c05f8ce1f02fb785daed87eb835cffbf82d18b3624d8f75ba32c467d1914c521

2 / The unsuccessful fix

Exit 1
"""Failure Map reference implementation. Python standard library only."""
import json

N = 1
observations = []
def solve(d):
    try:
        target,source=d
        state=dict(target)
        keys=[k for k,v in source]
        if len(set(v for k,v in source))!=len(source): return ['CARDINALITY',sorted(target)]
        for key,value in source:
            state[key]=value
        return ['OK',sorted([[k,v] for k,v in state.items()])]
    except (IndexError, KeyError, ValueError, StopIteration) as exc:
        return {"representation_error": type(exc).__name__}
def check(label, actual, expected):
    observations.append({"check": label, "actual": actual, "expected": expected, "passed": actual == expected})
if N == 1:
    check('matched update', solve([[[10, 1]], [[10, 2]]]), ['OK', [[10, 2]]])
    check('unmatched insert', solve([[[10, 1]], [[11, 2]]]), ['OK', [[10, 1], [11, 2]]])
    check('new duplicate keys', solve([[], [[10, 1], [10, 2]]]), ['CARDINALITY', []])
    check('existing duplicate keys', solve([[[10, 1]], [[10, 2], [10, 3]]]), ['CARDINALITY', [[10, 1]]])
    check('duplicate payload not key', solve([[], [[10, 1], [11, 1]]]), ['OK', [[10, 1], [11, 1]]])
    check('empty source', solve([[[10, 1]], []]), ['OK', [[10, 1]]])
    check('empty target', solve([[], [[10, 1]]]), ['OK', [[10, 1]]])
elif N == 2:
    check('matched update', solve([[[10, 2]], [[10, 3]]]), ['OK', [[10, 3]]])
    check('unmatched insert', solve([[[10, 2]], [[11, 3]]]), ['OK', [[10, 2], [11, 3]]])
    check('new duplicate keys', solve([[], [[10, 2], [10, 3]]]), ['CARDINALITY', []])
    check('existing duplicate keys', solve([[[10, 2]], [[10, 3], [10, 4]]]), ['CARDINALITY', [[10, 2]]])
    check('duplicate payload not key', solve([[], [[10, 2], [11, 2]]]), ['OK', [[10, 2], [11, 2]]])
    check('empty source', solve([[[10, 2]], []]), ['OK', [[10, 2]]])
    check('empty target', solve([[], [[10, 2]]]), ['OK', [[10, 2]]])
elif N == 3:
    check('matched update', solve([[[10, 3]], [[10, 4]]]), ['OK', [[10, 4]]])
    check('unmatched insert', solve([[[10, 3]], [[11, 4]]]), ['OK', [[10, 3], [11, 4]]])
    check('new duplicate keys', solve([[], [[10, 3], [10, 4]]]), ['CARDINALITY', []])
    check('existing duplicate keys', solve([[[10, 3]], [[10, 4], [10, 5]]]), ['CARDINALITY', [[10, 3]]])
    check('duplicate payload not key', solve([[], [[10, 3], [11, 3]]]), ['OK', [[10, 3], [11, 3]]])
    check('empty source', solve([[[10, 3]], []]), ['OK', [[10, 3]]])
    check('empty target', solve([[], [[10, 3]]]), ['OK', [[10, 3]]])
elif N == 4:
    check('matched update', solve([[[10, 4]], [[10, 5]]]), ['OK', [[10, 5]]])
    check('unmatched insert', solve([[[10, 4]], [[11, 5]]]), ['OK', [[10, 4], [11, 5]]])
    check('new duplicate keys', solve([[], [[10, 4], [10, 5]]]), ['CARDINALITY', []])
    check('existing duplicate keys', solve([[[10, 4]], [[10, 5], [10, 6]]]), ['CARDINALITY', [[10, 4]]])
    check('duplicate payload not key', solve([[], [[10, 4], [11, 4]]]), ['OK', [[10, 4], [11, 4]]])
    check('empty source', solve([[[10, 4]], []]), ['OK', [[10, 4]]])
    check('empty target', solve([[], [[10, 4]]]), ['OK', [[10, 4]]])
elif N == 5:
    check('matched update', solve([[[10, 5]], [[10, 6]]]), ['OK', [[10, 6]]])
    check('unmatched insert', solve([[[10, 5]], [[11, 6]]]), ['OK', [[10, 5], [11, 6]]])
    check('new duplicate keys', solve([[], [[10, 5], [10, 6]]]), ['CARDINALITY', []])
    check('existing duplicate keys', solve([[[10, 5]], [[10, 6], [10, 7]]]), ['CARDINALITY', [[10, 5]]])
    check('duplicate payload not key', solve([[], [[10, 5], [11, 5]]]), ['OK', [[10, 5], [11, 5]]])
    check('empty source', solve([[[10, 5]], []]), ['OK', [[10, 5]]])
    check('empty target', solve([[], [[10, 5]]]), ['OK', [[10, 5]]])
print(json.dumps({"observations": observations, "passed": all(x["passed"] for x in observations)}, ensure_ascii=False))
raise SystemExit(0 if all(x["passed"] for x in observations) else 1)
Boundary fixtureActualExpectedOutcome
matched update['OK', [[10, 2]]]['OK', [[10, 2]]]Passed
unmatched insert['OK', [[10, 1], [11, 2]]]['OK', [[10, 1], [11, 2]]]Passed
new duplicate keys['OK', [[10, 2]]]['CARDINALITY', []]Failed
existing duplicate keys['OK', [[10, 3]]]['CARDINALITY', [[10, 1]]]Failed
duplicate payload not key['CARDINALITY', []]['OK', [[10, 1], [11, 1]]]Failed
empty source['OK', [[10, 1]]]['OK', [[10, 1]]]Passed
empty target['OK', [[10, 1]]]['OK', [[10, 1]]]Passed

SHA-256 / c2807d0753792a8b6fc2d9580d2a0d04a2297a7f1720f6c6cffb93649249b5ff

3 / The verified repair

Exit 0
"""Failure Map reference implementation. Python standard library only."""
import json

N = 1
observations = []
def solve(d):
    try:
        target,source=d
        state=dict(target)
        keys=[k for k,v in source]
        if len(set(keys))!=len(keys): return ['CARDINALITY',sorted(target)]
        for key,value in source:
            state[key]=value
        return ['OK',sorted([[k,v] for k,v in state.items()])]
    except (IndexError, KeyError, ValueError, StopIteration) as exc:
        return {"representation_error": type(exc).__name__}
def check(label, actual, expected):
    observations.append({"check": label, "actual": actual, "expected": expected, "passed": actual == expected})
if N == 1:
    check('matched update', solve([[[10, 1]], [[10, 2]]]), ['OK', [[10, 2]]])
    check('unmatched insert', solve([[[10, 1]], [[11, 2]]]), ['OK', [[10, 1], [11, 2]]])
    check('new duplicate keys', solve([[], [[10, 1], [10, 2]]]), ['CARDINALITY', []])
    check('existing duplicate keys', solve([[[10, 1]], [[10, 2], [10, 3]]]), ['CARDINALITY', [[10, 1]]])
    check('duplicate payload not key', solve([[], [[10, 1], [11, 1]]]), ['OK', [[10, 1], [11, 1]]])
    check('empty source', solve([[[10, 1]], []]), ['OK', [[10, 1]]])
    check('empty target', solve([[], [[10, 1]]]), ['OK', [[10, 1]]])
elif N == 2:
    check('matched update', solve([[[10, 2]], [[10, 3]]]), ['OK', [[10, 3]]])
    check('unmatched insert', solve([[[10, 2]], [[11, 3]]]), ['OK', [[10, 2], [11, 3]]])
    check('new duplicate keys', solve([[], [[10, 2], [10, 3]]]), ['CARDINALITY', []])
    check('existing duplicate keys', solve([[[10, 2]], [[10, 3], [10, 4]]]), ['CARDINALITY', [[10, 2]]])
    check('duplicate payload not key', solve([[], [[10, 2], [11, 2]]]), ['OK', [[10, 2], [11, 2]]])
    check('empty source', solve([[[10, 2]], []]), ['OK', [[10, 2]]])
    check('empty target', solve([[], [[10, 2]]]), ['OK', [[10, 2]]])
elif N == 3:
    check('matched update', solve([[[10, 3]], [[10, 4]]]), ['OK', [[10, 4]]])
    check('unmatched insert', solve([[[10, 3]], [[11, 4]]]), ['OK', [[10, 3], [11, 4]]])
    check('new duplicate keys', solve([[], [[10, 3], [10, 4]]]), ['CARDINALITY', []])
    check('existing duplicate keys', solve([[[10, 3]], [[10, 4], [10, 5]]]), ['CARDINALITY', [[10, 3]]])
    check('duplicate payload not key', solve([[], [[10, 3], [11, 3]]]), ['OK', [[10, 3], [11, 3]]])
    check('empty source', solve([[[10, 3]], []]), ['OK', [[10, 3]]])
    check('empty target', solve([[], [[10, 3]]]), ['OK', [[10, 3]]])
elif N == 4:
    check('matched update', solve([[[10, 4]], [[10, 5]]]), ['OK', [[10, 5]]])
    check('unmatched insert', solve([[[10, 4]], [[11, 5]]]), ['OK', [[10, 4], [11, 5]]])
    check('new duplicate keys', solve([[], [[10, 4], [10, 5]]]), ['CARDINALITY', []])
    check('existing duplicate keys', solve([[[10, 4]], [[10, 5], [10, 6]]]), ['CARDINALITY', [[10, 4]]])
    check('duplicate payload not key', solve([[], [[10, 4], [11, 4]]]), ['OK', [[10, 4], [11, 4]]])
    check('empty source', solve([[[10, 4]], []]), ['OK', [[10, 4]]])
    check('empty target', solve([[], [[10, 4]]]), ['OK', [[10, 4]]])
elif N == 5:
    check('matched update', solve([[[10, 5]], [[10, 6]]]), ['OK', [[10, 6]]])
    check('unmatched insert', solve([[[10, 5]], [[11, 6]]]), ['OK', [[10, 5], [11, 6]]])
    check('new duplicate keys', solve([[], [[10, 5], [10, 6]]]), ['CARDINALITY', []])
    check('existing duplicate keys', solve([[[10, 5]], [[10, 6], [10, 7]]]), ['CARDINALITY', [[10, 5]]])
    check('duplicate payload not key', solve([[], [[10, 5], [11, 5]]]), ['OK', [[10, 5], [11, 5]]])
    check('empty source', solve([[[10, 5]], []]), ['OK', [[10, 5]]])
    check('empty target', solve([[], [[10, 5]]]), ['OK', [[10, 5]]])
print(json.dumps({"observations": observations, "passed": all(x["passed"] for x in observations)}, ensure_ascii=False))
raise SystemExit(0 if all(x["passed"] for x in observations) else 1)
Boundary fixtureActualExpectedOutcome
matched update['OK', [[10, 2]]]['OK', [[10, 2]]]Passed
unmatched insert['OK', [[10, 1], [11, 2]]]['OK', [[10, 1], [11, 2]]]Passed
new duplicate keys['CARDINALITY', []]['CARDINALITY', []]Passed
existing duplicate keys['CARDINALITY', [[10, 1]]]['CARDINALITY', [[10, 1]]]Passed
duplicate payload not key['OK', [[10, 1], [11, 1]]]['OK', [[10, 1], [11, 1]]]Passed
empty source['OK', [[10, 1]]]['OK', [[10, 1]]]Passed
empty target['OK', [[10, 1]]]['OK', [[10, 1]]]Passed

SHA-256 / 274dd6ded9120359afa14a1dc5f6c57a09b8478baaecd9c92eb041cdfb6276c9

Verification & scope

Offline stipulated semantics over valid small inputs; no performance, concurrency, or production-engine conformance claim. This reproducer isolates one failure mechanism. Results cover the supplied fixtures. Variants within a family share a test contract and should remain grouped when constructing evaluation splits. Related mechanisms with a shared evaluation_group must also remain together; these controlled models are not independent production incidents.

Observations recorded using Python 3.12.14 at 2026-09-29T14:44:21.751078+00:00.

Case digest / f4d533d3483fb9b7dfa18ff4276884c5e01da1908ef181293b5d6f7ab28a44ea