FA-45006 / Data systems / Open access
Full outer join stores right IDs but tests physical positions · case 01
Full outer join stores right IDs but tests physical positions.
ROOT CAUSE
full-outer-match-flags: Full outer join stores right IDs but tests physical positions.
VERIFIED REPAIR
Preserve the stated physical representation and operation order: Execute a full outer join of [id,key,value] rows with equality on non-null keys and right.value>=left.value. Track right matches by physical position; failed residuals do not mark a row matched. Emit left-major pairs, then unmatched right rows.
Unsuccessful approach: Storing join keys still uses a different domain from physical row positions.
Case contract
Execute a full outer join of [id,key,value] rows with equality on non-null keys and right.value>=left.value. Track right matches by physical position; failed residuals do not mark a row matched. Emit left-major pairs, then unmatched right rows.
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:
left,right=d
seen=set(); out=[]
for ident,key,value in left:
matched=False
for pos,(other,k,v) in enumerate(right):
if key is not None and k==key and v>=value:
matched=True
seen.add(other)
out.append([ident,other])
if not matched: out.append([ident,None])
for pos,(other,k,v) in enumerate(right):
if pos not in seen: out.append([None,other])
return out
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('residual failure', solve([[[10, 'a', 2]], [[20, 'a', 1]]]), [[10, None], [None, 20]])
check('matched then unmatched', solve([[[10, 'a', 1], [11, 'b', 1]], [[20, 'a', 2]]]), [[10, 20], [11, None]])
check('null keys', solve([[[10, None, 1]], [[20, None, 1]]]), [[10, None], [None, 20]])
check('right unmatched', solve([[[10, 'a', 1]], [[20, 'a', 1], [21, 'b', 1]]]), [[10, 20], [None, 21]])
check('right physical duplicates', solve([[[10, 'a', 1]], [[20, 'a', 1], [20, 'a', 1]]]), [[10, 20], [10, 20]])
check('empty left', solve([[], [[20, 'a', 1]]]), [[None, 20]])
check('empty right', solve([[[10, 'a', 1]], []]), [[10, None]])
elif N == 2:
check('residual failure', solve([[[10, 'a', 3]], [[20, 'a', 2]]]), [[10, None], [None, 20]])
check('matched then unmatched', solve([[[10, 'a', 2], [11, 'b', 2]], [[20, 'a', 3]]]), [[10, 20], [11, None]])
check('null keys', solve([[[10, None, 2]], [[20, None, 2]]]), [[10, None], [None, 20]])
check('right unmatched', solve([[[10, 'a', 2]], [[20, 'a', 2], [21, 'b', 2]]]), [[10, 20], [None, 21]])
check('right physical duplicates', solve([[[10, 'a', 2]], [[20, 'a', 2], [20, 'a', 2]]]), [[10, 20], [10, 20]])
check('empty left', solve([[], [[20, 'a', 2]]]), [[None, 20]])
check('empty right', solve([[[10, 'a', 2]], []]), [[10, None]])
elif N == 3:
check('residual failure', solve([[[10, 'a', 4]], [[20, 'a', 3]]]), [[10, None], [None, 20]])
check('matched then unmatched', solve([[[10, 'a', 3], [11, 'b', 3]], [[20, 'a', 4]]]), [[10, 20], [11, None]])
check('null keys', solve([[[10, None, 3]], [[20, None, 3]]]), [[10, None], [None, 20]])
check('right unmatched', solve([[[10, 'a', 3]], [[20, 'a', 3], [21, 'b', 3]]]), [[10, 20], [None, 21]])
check('right physical duplicates', solve([[[10, 'a', 3]], [[20, 'a', 3], [20, 'a', 3]]]), [[10, 20], [10, 20]])
check('empty left', solve([[], [[20, 'a', 3]]]), [[None, 20]])
check('empty right', solve([[[10, 'a', 3]], []]), [[10, None]])
elif N == 4:
check('residual failure', solve([[[10, 'a', 5]], [[20, 'a', 4]]]), [[10, None], [None, 20]])
check('matched then unmatched', solve([[[10, 'a', 4], [11, 'b', 4]], [[20, 'a', 5]]]), [[10, 20], [11, None]])
check('null keys', solve([[[10, None, 4]], [[20, None, 4]]]), [[10, None], [None, 20]])
check('right unmatched', solve([[[10, 'a', 4]], [[20, 'a', 4], [21, 'b', 4]]]), [[10, 20], [None, 21]])
check('right physical duplicates', solve([[[10, 'a', 4]], [[20, 'a', 4], [20, 'a', 4]]]), [[10, 20], [10, 20]])
check('empty left', solve([[], [[20, 'a', 4]]]), [[None, 20]])
check('empty right', solve([[[10, 'a', 4]], []]), [[10, None]])
elif N == 5:
check('residual failure', solve([[[10, 'a', 6]], [[20, 'a', 5]]]), [[10, None], [None, 20]])
check('matched then unmatched', solve([[[10, 'a', 5], [11, 'b', 5]], [[20, 'a', 6]]]), [[10, 20], [11, None]])
check('null keys', solve([[[10, None, 5]], [[20, None, 5]]]), [[10, None], [None, 20]])
check('right unmatched', solve([[[10, 'a', 5]], [[20, 'a', 5], [21, 'b', 5]]]), [[10, 20], [None, 21]])
check('right physical duplicates', solve([[[10, 'a', 5]], [[20, 'a', 5], [20, 'a', 5]]]), [[10, 20], [10, 20]])
check('empty left', solve([[], [[20, 'a', 5]]]), [[None, 20]])
check('empty right', solve([[[10, 'a', 5]], []]), [[10, None]])
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 fixture | Actual | Expected | Outcome |
|---|---|---|---|
| residual failure | [[10, None], [None, 20]] | [[10, None], [None, 20]] | Passed |
| matched then unmatched | [[10, 20], [11, None], [None, 20]] | [[10, 20], [11, None]] | Failed |
| null keys | [[10, None], [None, 20]] | [[10, None], [None, 20]] | Passed |
| right unmatched | [[10, 20], [None, 20], [None, 21]] | [[10, 20], [None, 21]] | Failed |
| right physical duplicates | [[10, 20], [10, 20], [None, 20], [None, 20]] | [[10, 20], [10, 20]] | Failed |
| empty left | [[None, 20]] | [[None, 20]] | Passed |
| empty right | [[10, None]] | [[10, None]] | Passed |
SHA-256 / 00123f848e5e5a01027747f72beeab793e397425fd1252dc40c7b441c6c2cbda
2 / The unsuccessful fix
Exit 1"""Failure Map reference implementation. Python standard library only."""
import json
N = 1
observations = []
def solve(d):
try:
left,right=d
seen=set(); out=[]
for ident,key,value in left:
matched=False
for pos,(other,k,v) in enumerate(right):
if key is not None and k==key and v>=value:
matched=True
seen.add(k)
out.append([ident,other])
if not matched: out.append([ident,None])
for pos,(other,k,v) in enumerate(right):
if pos not in seen: out.append([None,other])
return out
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('residual failure', solve([[[10, 'a', 2]], [[20, 'a', 1]]]), [[10, None], [None, 20]])
check('matched then unmatched', solve([[[10, 'a', 1], [11, 'b', 1]], [[20, 'a', 2]]]), [[10, 20], [11, None]])
check('null keys', solve([[[10, None, 1]], [[20, None, 1]]]), [[10, None], [None, 20]])
check('right unmatched', solve([[[10, 'a', 1]], [[20, 'a', 1], [21, 'b', 1]]]), [[10, 20], [None, 21]])
check('right physical duplicates', solve([[[10, 'a', 1]], [[20, 'a', 1], [20, 'a', 1]]]), [[10, 20], [10, 20]])
check('empty left', solve([[], [[20, 'a', 1]]]), [[None, 20]])
check('empty right', solve([[[10, 'a', 1]], []]), [[10, None]])
elif N == 2:
check('residual failure', solve([[[10, 'a', 3]], [[20, 'a', 2]]]), [[10, None], [None, 20]])
check('matched then unmatched', solve([[[10, 'a', 2], [11, 'b', 2]], [[20, 'a', 3]]]), [[10, 20], [11, None]])
check('null keys', solve([[[10, None, 2]], [[20, None, 2]]]), [[10, None], [None, 20]])
check('right unmatched', solve([[[10, 'a', 2]], [[20, 'a', 2], [21, 'b', 2]]]), [[10, 20], [None, 21]])
check('right physical duplicates', solve([[[10, 'a', 2]], [[20, 'a', 2], [20, 'a', 2]]]), [[10, 20], [10, 20]])
check('empty left', solve([[], [[20, 'a', 2]]]), [[None, 20]])
check('empty right', solve([[[10, 'a', 2]], []]), [[10, None]])
elif N == 3:
check('residual failure', solve([[[10, 'a', 4]], [[20, 'a', 3]]]), [[10, None], [None, 20]])
check('matched then unmatched', solve([[[10, 'a', 3], [11, 'b', 3]], [[20, 'a', 4]]]), [[10, 20], [11, None]])
check('null keys', solve([[[10, None, 3]], [[20, None, 3]]]), [[10, None], [None, 20]])
check('right unmatched', solve([[[10, 'a', 3]], [[20, 'a', 3], [21, 'b', 3]]]), [[10, 20], [None, 21]])
check('right physical duplicates', solve([[[10, 'a', 3]], [[20, 'a', 3], [20, 'a', 3]]]), [[10, 20], [10, 20]])
check('empty left', solve([[], [[20, 'a', 3]]]), [[None, 20]])
check('empty right', solve([[[10, 'a', 3]], []]), [[10, None]])
elif N == 4:
check('residual failure', solve([[[10, 'a', 5]], [[20, 'a', 4]]]), [[10, None], [None, 20]])
check('matched then unmatched', solve([[[10, 'a', 4], [11, 'b', 4]], [[20, 'a', 5]]]), [[10, 20], [11, None]])
check('null keys', solve([[[10, None, 4]], [[20, None, 4]]]), [[10, None], [None, 20]])
check('right unmatched', solve([[[10, 'a', 4]], [[20, 'a', 4], [21, 'b', 4]]]), [[10, 20], [None, 21]])
check('right physical duplicates', solve([[[10, 'a', 4]], [[20, 'a', 4], [20, 'a', 4]]]), [[10, 20], [10, 20]])
check('empty left', solve([[], [[20, 'a', 4]]]), [[None, 20]])
check('empty right', solve([[[10, 'a', 4]], []]), [[10, None]])
elif N == 5:
check('residual failure', solve([[[10, 'a', 6]], [[20, 'a', 5]]]), [[10, None], [None, 20]])
check('matched then unmatched', solve([[[10, 'a', 5], [11, 'b', 5]], [[20, 'a', 6]]]), [[10, 20], [11, None]])
check('null keys', solve([[[10, None, 5]], [[20, None, 5]]]), [[10, None], [None, 20]])
check('right unmatched', solve([[[10, 'a', 5]], [[20, 'a', 5], [21, 'b', 5]]]), [[10, 20], [None, 21]])
check('right physical duplicates', solve([[[10, 'a', 5]], [[20, 'a', 5], [20, 'a', 5]]]), [[10, 20], [10, 20]])
check('empty left', solve([[], [[20, 'a', 5]]]), [[None, 20]])
check('empty right', solve([[[10, 'a', 5]], []]), [[10, None]])
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 fixture | Actual | Expected | Outcome |
|---|---|---|---|
| residual failure | [[10, None], [None, 20]] | [[10, None], [None, 20]] | Passed |
| matched then unmatched | [[10, 20], [11, None], [None, 20]] | [[10, 20], [11, None]] | Failed |
| null keys | [[10, None], [None, 20]] | [[10, None], [None, 20]] | Passed |
| right unmatched | [[10, 20], [None, 20], [None, 21]] | [[10, 20], [None, 21]] | Failed |
| right physical duplicates | [[10, 20], [10, 20], [None, 20], [None, 20]] | [[10, 20], [10, 20]] | Failed |
| empty left | [[None, 20]] | [[None, 20]] | Passed |
| empty right | [[10, None]] | [[10, None]] | Passed |
SHA-256 / 6cf37807ec77d6df091d587b4be10c701d72644aec54fa496575bc68e6d933eb
3 / The verified repair
Exit 0"""Failure Map reference implementation. Python standard library only."""
import json
N = 1
observations = []
def solve(d):
try:
left,right=d
seen=set(); out=[]
for ident,key,value in left:
matched=False
for pos,(other,k,v) in enumerate(right):
if key is not None and k==key and v>=value:
matched=True
seen.add(pos)
out.append([ident,other])
if not matched: out.append([ident,None])
for pos,(other,k,v) in enumerate(right):
if pos not in seen: out.append([None,other])
return out
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('residual failure', solve([[[10, 'a', 2]], [[20, 'a', 1]]]), [[10, None], [None, 20]])
check('matched then unmatched', solve([[[10, 'a', 1], [11, 'b', 1]], [[20, 'a', 2]]]), [[10, 20], [11, None]])
check('null keys', solve([[[10, None, 1]], [[20, None, 1]]]), [[10, None], [None, 20]])
check('right unmatched', solve([[[10, 'a', 1]], [[20, 'a', 1], [21, 'b', 1]]]), [[10, 20], [None, 21]])
check('right physical duplicates', solve([[[10, 'a', 1]], [[20, 'a', 1], [20, 'a', 1]]]), [[10, 20], [10, 20]])
check('empty left', solve([[], [[20, 'a', 1]]]), [[None, 20]])
check('empty right', solve([[[10, 'a', 1]], []]), [[10, None]])
elif N == 2:
check('residual failure', solve([[[10, 'a', 3]], [[20, 'a', 2]]]), [[10, None], [None, 20]])
check('matched then unmatched', solve([[[10, 'a', 2], [11, 'b', 2]], [[20, 'a', 3]]]), [[10, 20], [11, None]])
check('null keys', solve([[[10, None, 2]], [[20, None, 2]]]), [[10, None], [None, 20]])
check('right unmatched', solve([[[10, 'a', 2]], [[20, 'a', 2], [21, 'b', 2]]]), [[10, 20], [None, 21]])
check('right physical duplicates', solve([[[10, 'a', 2]], [[20, 'a', 2], [20, 'a', 2]]]), [[10, 20], [10, 20]])
check('empty left', solve([[], [[20, 'a', 2]]]), [[None, 20]])
check('empty right', solve([[[10, 'a', 2]], []]), [[10, None]])
elif N == 3:
check('residual failure', solve([[[10, 'a', 4]], [[20, 'a', 3]]]), [[10, None], [None, 20]])
check('matched then unmatched', solve([[[10, 'a', 3], [11, 'b', 3]], [[20, 'a', 4]]]), [[10, 20], [11, None]])
check('null keys', solve([[[10, None, 3]], [[20, None, 3]]]), [[10, None], [None, 20]])
check('right unmatched', solve([[[10, 'a', 3]], [[20, 'a', 3], [21, 'b', 3]]]), [[10, 20], [None, 21]])
check('right physical duplicates', solve([[[10, 'a', 3]], [[20, 'a', 3], [20, 'a', 3]]]), [[10, 20], [10, 20]])
check('empty left', solve([[], [[20, 'a', 3]]]), [[None, 20]])
check('empty right', solve([[[10, 'a', 3]], []]), [[10, None]])
elif N == 4:
check('residual failure', solve([[[10, 'a', 5]], [[20, 'a', 4]]]), [[10, None], [None, 20]])
check('matched then unmatched', solve([[[10, 'a', 4], [11, 'b', 4]], [[20, 'a', 5]]]), [[10, 20], [11, None]])
check('null keys', solve([[[10, None, 4]], [[20, None, 4]]]), [[10, None], [None, 20]])
check('right unmatched', solve([[[10, 'a', 4]], [[20, 'a', 4], [21, 'b', 4]]]), [[10, 20], [None, 21]])
check('right physical duplicates', solve([[[10, 'a', 4]], [[20, 'a', 4], [20, 'a', 4]]]), [[10, 20], [10, 20]])
check('empty left', solve([[], [[20, 'a', 4]]]), [[None, 20]])
check('empty right', solve([[[10, 'a', 4]], []]), [[10, None]])
elif N == 5:
check('residual failure', solve([[[10, 'a', 6]], [[20, 'a', 5]]]), [[10, None], [None, 20]])
check('matched then unmatched', solve([[[10, 'a', 5], [11, 'b', 5]], [[20, 'a', 6]]]), [[10, 20], [11, None]])
check('null keys', solve([[[10, None, 5]], [[20, None, 5]]]), [[10, None], [None, 20]])
check('right unmatched', solve([[[10, 'a', 5]], [[20, 'a', 5], [21, 'b', 5]]]), [[10, 20], [None, 21]])
check('right physical duplicates', solve([[[10, 'a', 5]], [[20, 'a', 5], [20, 'a', 5]]]), [[10, 20], [10, 20]])
check('empty left', solve([[], [[20, 'a', 5]]]), [[None, 20]])
check('empty right', solve([[[10, 'a', 5]], []]), [[10, None]])
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 fixture | Actual | Expected | Outcome |
|---|---|---|---|
| residual failure | [[10, None], [None, 20]] | [[10, None], [None, 20]] | Passed |
| matched then unmatched | [[10, 20], [11, None]] | [[10, 20], [11, None]] | Passed |
| null keys | [[10, None], [None, 20]] | [[10, None], [None, 20]] | Passed |
| right unmatched | [[10, 20], [None, 21]] | [[10, 20], [None, 21]] | Passed |
| right physical duplicates | [[10, 20], [10, 20]] | [[10, 20], [10, 20]] | Passed |
| empty left | [[None, 20]] | [[None, 20]] | Passed |
| empty right | [[10, None]] | [[10, None]] | Passed |
SHA-256 / 27e6a66367b010d8da22d602a6f47bb2c9402b1cce4147048ab6d4d708e02aec
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:17.860695+00:00.
Case digest / 761948d4a0e3572db906915bd8cb6e0762fbe7a006615c249734062cace64589