FA-45016 / Data systems / Open access
Full outer join omits unmatched left placeholders · case 01
Full outer join omits unmatched left placeholders.
ROOT CAUSE
full-outer-match-flags: Full outer join omits unmatched left placeholders.
THE FAILURE
full-outer-match-flags: Full outer join omits unmatched left placeholders.
Unsuccessful approach: Swapping placeholder sides changes the joined schema.
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: pass
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 | [[None, 20]] | [[10, None], [None, 20]] | Failed |
| matched then unmatched | [[10, 20]] | [[10, 20], [11, None]] | Failed |
| null keys | [[None, 20]] | [[10, None], [None, 20]] | Failed |
| 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]] | Failed |
SHA-256 / d6a8b01d8238adbca6613d31f6e7efcbd4e82393315f660ed207bf27508d58ef
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([None,ident])
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 | [[None, 10], [None, 20]] | [[10, None], [None, 20]] | Failed |
| matched then unmatched | [[10, 20], [None, 11]] | [[10, 20], [11, None]] | Failed |
| null keys | [[None, 10], [None, 20]] | [[10, None], [None, 20]] | Failed |
| 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 | [[None, 10]] | [[10, None]] | Failed |
SHA-256 / bb8fbc6713eef3a06f7b86c2af9812b453ae2a13c8531b0211522a11f0de2df5
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 / 87905711c65a1f0a00d18ebd65537bb8d00e104cdd64e1a51de00189733c1a5d