FAILURE MAP
← Case archive

FA-45406 / Data systems / Open access

MERGE treats matched rows as insert-if-absent · case 01

MERGE treats matched rows as insert-if-absent.

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

ROOT CAUSE

merge-statement-preflight: MERGE treats matched rows as insert-if-absent.

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: Explicit insert-only logic still fails to update existing keys.

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(keys))!=len(keys): return ['CARDINALITY',sorted(target)]
        for key,value in source:
            state.setdefault(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, 1]]]['OK', [[10, 2]]]Failed
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 / ed2e13189af8c6585760508ca80962cf2d4efb9c8cd71bb423c39d9bb065c101

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(keys))!=len(keys): return ['CARDINALITY',sorted(target)]
        for key,value in source:
            if key not in state: 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, 1]]]['OK', [[10, 2]]]Failed
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 / 53c76bc10c648291eb1019d910f1ccac53fc4d81855ea1eadbebe96529d5e70b

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.893168+00:00.

Case digest / 20012b329982b7f6cbf5fe76c20d9fa58e99ffbcccc31bf50a0231858c9de2fb