FA-121 / Storage and queries / Open access

A descriptive join multiplies an invoice subtotal · case 01

Adding multiple descriptive tags inflates the subtotal by repeating each invoice line in a join product.

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

ROOT CAUSE

The aggregate is calculated at the joined line-by-tag grain rather than at the invoice-line grain.

VERIFIED REPAIR

Aggregate line amounts before combining them with independently deduplicated descriptive tags.

Unsuccessful approach: SUM(DISTINCT amount) hides some fanout but collapses separate invoice lines that happen to cost the same amount.

Case contract

Given one invoice's integer line amounts and descriptive tag strings, return [sum_of_all_lines, sorted_unique_tags]. Each line occurrence contributes once, even equal-priced lines. Missing tags do not remove an invoice or its revenue.

Why this case matters

Models a many-to-many query fanout and grain mismatch, a frequent analytical-query error. It makes the relational cardinality explicit without claiming to reproduce a full billing database.

1 / The failure

Exit 1

"""Failure Map reference implementation. Python standard library only."""
import json

N = 1
observations = []
def solve(lines, tags):
    return [sum(amount for amount in lines for tag in tags), sorted(set(tags))]
def check(label, actual, expected):
    observations.append({"check": label, "actual": actual, "expected": expected, "passed": actual == expected})
check('two lines crossed with multiple tags', solve([N, N+1], ['red', 'blue']), [2*N+1, ['blue', 'red']])
check('equal-priced lines are distinct facts', solve([N] * (N+1), ['one']), [N*(N+1), ['one']])
check('untagged invoice remains in the result', solve([N, N+2], []), [2*N+2, []])
check('duplicate tag rows do not multiply revenue', solve([N], ['a', 'a', 'b']), [N, ['a', 'b']])
check('empty invoice preserves descriptive tags', solve([], ['b', 'a']), [0, ['a', 'b']])
check('credit and charge lines remain separate', solve([N, -N, N], ['credit']), [N, ['credit']])
check('no lines or tags', solve([], []), [0, []])
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
two lines crossed with multiple tags	`[6, ['blue', 'red']]`	`[3, ['blue', 'red']]`	Failed
equal-priced lines are distinct facts	`[2, ['one']]`	`[2, ['one']]`	Passed
untagged invoice remains in the result	`[0, []]`	`[4, []]`	Failed
duplicate tag rows do not multiply revenue	`[3, ['a', 'b']]`	`[1, ['a', 'b']]`	Failed
empty invoice preserves descriptive tags	`[0, ['a', 'b']]`	`[0, ['a', 'b']]`	Passed
credit and charge lines remain separate	`[1, ['credit']]`	`[1, ['credit']]`	Passed
no lines or tags	`[0, []]`	`[0, []]`	Passed

SHA-256 / a7cfb36e88987c1015c975830a9bc10e4d86a2c8b1fa858fc37019df460ba4f1

2 / The unsuccessful fix

Exit 1

"""Failure Map reference implementation. Python standard library only."""
import json

N = 1
observations = []
def solve(lines, tags):
    return [sum(set(lines)), sorted(set(tags))]
def check(label, actual, expected):
    observations.append({"check": label, "actual": actual, "expected": expected, "passed": actual == expected})
check('two lines crossed with multiple tags', solve([N, N+1], ['red', 'blue']), [2*N+1, ['blue', 'red']])
check('equal-priced lines are distinct facts', solve([N] * (N+1), ['one']), [N*(N+1), ['one']])
check('untagged invoice remains in the result', solve([N, N+2], []), [2*N+2, []])
check('duplicate tag rows do not multiply revenue', solve([N], ['a', 'a', 'b']), [N, ['a', 'b']])
check('empty invoice preserves descriptive tags', solve([], ['b', 'a']), [0, ['a', 'b']])
check('credit and charge lines remain separate', solve([N, -N, N], ['credit']), [N, ['credit']])
check('no lines or tags', solve([], []), [0, []])
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
two lines crossed with multiple tags	`[3, ['blue', 'red']]`	`[3, ['blue', 'red']]`	Passed
equal-priced lines are distinct facts	`[1, ['one']]`	`[2, ['one']]`	Failed
untagged invoice remains in the result	`[4, []]`	`[4, []]`	Passed
duplicate tag rows do not multiply revenue	`[1, ['a', 'b']]`	`[1, ['a', 'b']]`	Passed
empty invoice preserves descriptive tags	`[0, ['a', 'b']]`	`[0, ['a', 'b']]`	Passed
credit and charge lines remain separate	`[0, ['credit']]`	`[1, ['credit']]`	Failed
no lines or tags	`[0, []]`	`[0, []]`	Passed

SHA-256 / ce4e1c3cac61416effd8ccbb0766e5e160713201bfdd1b12c6e90c173c3acaa1

3 / The verified repair

Exit 0

"""Failure Map reference implementation. Python standard library only."""
import json

N = 1
observations = []
def solve(lines, tags):
    return [sum(lines), sorted(set(tags))]
def check(label, actual, expected):
    observations.append({"check": label, "actual": actual, "expected": expected, "passed": actual == expected})
check('two lines crossed with multiple tags', solve([N, N+1], ['red', 'blue']), [2*N+1, ['blue', 'red']])
check('equal-priced lines are distinct facts', solve([N] * (N+1), ['one']), [N*(N+1), ['one']])
check('untagged invoice remains in the result', solve([N, N+2], []), [2*N+2, []])
check('duplicate tag rows do not multiply revenue', solve([N], ['a', 'a', 'b']), [N, ['a', 'b']])
check('empty invoice preserves descriptive tags', solve([], ['b', 'a']), [0, ['a', 'b']])
check('credit and charge lines remain separate', solve([N, -N, N], ['credit']), [N, ['credit']])
check('no lines or tags', solve([], []), [0, []])
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
two lines crossed with multiple tags	`[3, ['blue', 'red']]`	`[3, ['blue', 'red']]`	Passed
equal-priced lines are distinct facts	`[2, ['one']]`	`[2, ['one']]`	Passed
untagged invoice remains in the result	`[4, []]`	`[4, []]`	Passed
duplicate tag rows do not multiply revenue	`[1, ['a', 'b']]`	`[1, ['a', 'b']]`	Passed
empty invoice preserves descriptive tags	`[0, ['a', 'b']]`	`[0, ['a', 'b']]`	Passed
credit and charge lines remain separate	`[1, ['credit']]`	`[1, ['credit']]`	Passed
no lines or tags	`[0, []]`	`[0, []]`	Passed

SHA-256 / f3f8b7e0dd4b44e3deab12b1a243e138b36feb910055421cc77b45a844136994

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

Case digest / 5b417389a4b4e12020181b8ba141286238a2346fdd9113508bd76ff3bfbc7fe1