FA-45021 / Data systems / Open access
Full outer join emits already matched right rows again · case 01
Full outer join emits already matched right rows again.
ROOT CAUSE
full-outer-match-flags: Full outer join emits already matched right rows again.
THE FAILURE
full-outer-match-flags: Full outer join emits already matched right rows again.
Unsuccessful approach: Inverting the set test keeps exactly the wrong right-side rows.
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(pos)
out.append([ident,other])
if not matched: out.append([ident,None])
for pos,(other,k,v) in enumerate(right):
if True: 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 / ef6fb80a8e82112da5ab5229b667250ed29e6230715e7b9eb23457755bc9d9ab
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(pos)
out.append([ident,other])
if not matched: out.append([ident,None])
for pos,(other,k,v) in enumerate(right):
if pos 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]] | [[10, None], [None, 20]] | Failed |
| matched then unmatched | [[10, 20], [11, None], [None, 20]] | [[10, 20], [11, None]] | Failed |
| null keys | [[10, None]] | [[10, None], [None, 20]] | Failed |
| right unmatched | [[10, 20], [None, 20]] | [[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]] | Failed |
| empty right | [[10, None]] | [[10, None]] | Passed |
SHA-256 / 7187443d35a02912676cd557bf75feceadda1b21851aa90b0769067878513cc4
HELD IN THE MEMBER ARCHIVE
The verified repair and its recorded checks are member-only.
This mechanism has 7 recorded checks per implementation. The open-access tier publishes the failure and the unsuccessful fix; the repaired source that passes every check, and the observations that prove it, are available to members.
Every case sharing this mechanism uses the same contract and the same repair, so this one record is held back for all of them.
Member access is invitation-based. Sign in with your invited account to inspect the repair.
Sign in to the archive ↗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.961992+00:00.
Case digest / 2b87c4e025290774a04153e0124e31886e224bf29a7c431d29e2d423fc4b7168