FA-141 / Storage and queries / Open access
A joined row loses a field when two columns share a label · case 01
Converting a joined result into an object overwrites one table's identifier with another table's identifier.
ROOT CAUSE
Bare column labels are not unique across joined relations, but the row-to-object mapper treats them as stable keys.
VERIFIED REPAIR
Use the explicit relation alias and column name as a qualified output key for every selected column.
Unsuccessful approach: Appending ordinal suffixes to duplicate bare names depends on projection order and does not preserve the client's qualified schema.
Case contract
Each projection entry is [relation_alias,column_name] and has a matching value. Pairs are unique; components contain no dots. Return a map keyed by relation_alias + '.' + column_name, retaining every value including NULL and zero.
Why this case matters
Models client-side hydration of relational join results. It illustrates loss of provenance and stable field names when mapping column arrays into objects; it does not infer table names from database drivers.
1 / The failure
Exit 1"""Failure Map reference implementation. Python standard library only."""
import json
N = 1
observations = []
def solve(columns, values):
return {column[1]: value for column, value in zip(columns, values)}
def check(label, actual, expected):
observations.append({"check": label, "actual": actual, "expected": expected, "passed": actual == expected})
check('joined identifiers both survive', solve([['orders', 'id'], ['users', 'id']], [N, N+1]), {'orders.id': N, 'users.id': N+1})
check('reversing projection preserves qualified schema', solve([['users', 'id'], ['orders', 'id']], [N+1, N]), {'orders.id': N, 'users.id': N+1})
check('nullable outer-join field is retained', solve([['left', 'id'], ['right', 'id']], [N, None]), {'left.id': N, 'right.id': None})
check('zero identifier is retained', solve([['left', 'id'], ['right', 'id']], [0, N]), {'left.id': 0, 'right.id': N})
check('ordinary names are qualified consistently', solve([['a', 'name'], ['b', 'amount']], ['case-'+str(N), N*3]), {'a.name': 'case-'+str(N), 'b.amount': N*3})
check('ordinal-looking source name cannot collide', solve([['a', 'id'], ['b', 'id'], ['c', 'id_2']], [N, N+1, N+2]), {'a.id': N, 'b.id': N+1, 'c.id_2': N+2})
check('empty projection', solve([], []), {})
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 |
|---|---|---|---|
| joined identifiers both survive | {'id': 2} | {'orders.id': 1, 'users.id': 2} | Failed |
| reversing projection preserves qualified schema | {'id': 1} | {'orders.id': 1, 'users.id': 2} | Failed |
| nullable outer-join field is retained | {'id': None} | {'left.id': 1, 'right.id': None} | Failed |
| zero identifier is retained | {'id': 1} | {'left.id': 0, 'right.id': 1} | Failed |
| ordinary names are qualified consistently | {'amount': 3, 'name': 'case-1'} | {'a.name': 'case-1', 'b.amount': 3} | Failed |
| ordinal-looking source name cannot collide | {'id': 2, 'id_2': 3} | {'a.id': 1, 'b.id': 2, 'c.id_2': 3} | Failed |
| empty projection | {} | {} | Passed |
SHA-256 / a218b40ae86ccdfce146d7a8cbedcae8a486a102bd26552c51a35c30d813c04e
2 / The unsuccessful fix
Exit 1"""Failure Map reference implementation. Python standard library only."""
import json
N = 1
observations = []
def solve(columns, values):
result = {}
for column, value in zip(columns, values):
name = column[1]
suffix = 2
while name in result:
name = column[1] + '_' + str(suffix)
suffix += 1
result[name] = value
return result
def check(label, actual, expected):
observations.append({"check": label, "actual": actual, "expected": expected, "passed": actual == expected})
check('joined identifiers both survive', solve([['orders', 'id'], ['users', 'id']], [N, N+1]), {'orders.id': N, 'users.id': N+1})
check('reversing projection preserves qualified schema', solve([['users', 'id'], ['orders', 'id']], [N+1, N]), {'orders.id': N, 'users.id': N+1})
check('nullable outer-join field is retained', solve([['left', 'id'], ['right', 'id']], [N, None]), {'left.id': N, 'right.id': None})
check('zero identifier is retained', solve([['left', 'id'], ['right', 'id']], [0, N]), {'left.id': 0, 'right.id': N})
check('ordinary names are qualified consistently', solve([['a', 'name'], ['b', 'amount']], ['case-'+str(N), N*3]), {'a.name': 'case-'+str(N), 'b.amount': N*3})
check('ordinal-looking source name cannot collide', solve([['a', 'id'], ['b', 'id'], ['c', 'id_2']], [N, N+1, N+2]), {'a.id': N, 'b.id': N+1, 'c.id_2': N+2})
check('empty projection', solve([], []), {})
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 |
|---|---|---|---|
| joined identifiers both survive | {'id': 1, 'id_2': 2} | {'orders.id': 1, 'users.id': 2} | Failed |
| reversing projection preserves qualified schema | {'id': 2, 'id_2': 1} | {'orders.id': 1, 'users.id': 2} | Failed |
| nullable outer-join field is retained | {'id': 1, 'id_2': None} | {'left.id': 1, 'right.id': None} | Failed |
| zero identifier is retained | {'id': 0, 'id_2': 1} | {'left.id': 0, 'right.id': 1} | Failed |
| ordinary names are qualified consistently | {'amount': 3, 'name': 'case-1'} | {'a.name': 'case-1', 'b.amount': 3} | Failed |
| ordinal-looking source name cannot collide | {'id': 1, 'id_2': 2, 'id_2_2': 3} | {'a.id': 1, 'b.id': 2, 'c.id_2': 3} | Failed |
| empty projection | {} | {} | Passed |
SHA-256 / 8ad2aaac49cc4ec84ec9741c45e436935048a8b1a90e4030f7f418dda1cf866e
3 / The verified repair
Exit 0"""Failure Map reference implementation. Python standard library only."""
import json
N = 1
observations = []
def solve(columns, values):
return {column[0] + '.' + column[1]: value for column, value in zip(columns, values)}
def check(label, actual, expected):
observations.append({"check": label, "actual": actual, "expected": expected, "passed": actual == expected})
check('joined identifiers both survive', solve([['orders', 'id'], ['users', 'id']], [N, N+1]), {'orders.id': N, 'users.id': N+1})
check('reversing projection preserves qualified schema', solve([['users', 'id'], ['orders', 'id']], [N+1, N]), {'orders.id': N, 'users.id': N+1})
check('nullable outer-join field is retained', solve([['left', 'id'], ['right', 'id']], [N, None]), {'left.id': N, 'right.id': None})
check('zero identifier is retained', solve([['left', 'id'], ['right', 'id']], [0, N]), {'left.id': 0, 'right.id': N})
check('ordinary names are qualified consistently', solve([['a', 'name'], ['b', 'amount']], ['case-'+str(N), N*3]), {'a.name': 'case-'+str(N), 'b.amount': N*3})
check('ordinal-looking source name cannot collide', solve([['a', 'id'], ['b', 'id'], ['c', 'id_2']], [N, N+1, N+2]), {'a.id': N, 'b.id': N+1, 'c.id_2': N+2})
check('empty projection', solve([], []), {})
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 |
|---|---|---|---|
| joined identifiers both survive | {'orders.id': 1, 'users.id': 2} | {'orders.id': 1, 'users.id': 2} | Passed |
| reversing projection preserves qualified schema | {'orders.id': 1, 'users.id': 2} | {'orders.id': 1, 'users.id': 2} | Passed |
| nullable outer-join field is retained | {'left.id': 1, 'right.id': None} | {'left.id': 1, 'right.id': None} | Passed |
| zero identifier is retained | {'left.id': 0, 'right.id': 1} | {'left.id': 0, 'right.id': 1} | Passed |
| ordinary names are qualified consistently | {'a.name': 'case-1', 'b.amount': 3} | {'a.name': 'case-1', 'b.amount': 3} | Passed |
| ordinal-looking source name cannot collide | {'a.id': 1, 'b.id': 2, 'c.id_2': 3} | {'a.id': 1, 'b.id': 2, 'c.id_2': 3} | Passed |
| empty projection | {} | {} | Passed |
SHA-256 / 8e51f411b046737b0307aeec5e43aecfadb4a55431672439a249880f1b79167d
Verification & scope
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:36:50.954302+00:00.
Case digest / 9ace37763a3e1837fd8c7a4e1995120daddba28c36dfe27b013b388bb00ff783