FA-116 / Storage and queries / Open access

A scalar aggregate confuses no observations with a measured zero · case 01

An empty or all-NULL aggregate is rendered as zero, while an attempted repair also erases genuine zero totals.

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

ROOT CAUSE

A display fallback overwrites the distinction between SQL SUM's NULL result and a numeric additive identity.

THE FAILURE

A display fallback overwrites the distinction between SQL SUM's NULL result and a numeric additive identity.

Unsuccessful approach: NULLIF(total, 0) restores some missing values but also converts legitimate cancellation and zero measurements to NULL.

Case contract

Return [sum,count_of_non_NULL_values,total_row_count]. Sum is None when there are no non-NULL values, otherwise their exact integer sum, including zero. A scalar aggregate always returns this one triple.

Why this case matters

Uses SQLite to distinguish empty input, NULL rows and zero-valued observations in scalar query results; the intended task is aggregate result semantics, not estimating a missing-data mean.

1 / The failure

Exit 1

"""Failure Map reference implementation. Python standard library only."""
import json
import sqlite3
N = 1
observations = []
def solve(values):
    db = sqlite3.connect(':memory:')
    try:
        db.execute('CREATE TABLE measurements (v INTEGER)')
        db.executemany('INSERT INTO measurements VALUES (?)', [(v,) for v in values])
        return list(db.execute('SELECT COALESCE(SUM(v), 0), COUNT(v), COUNT(*) FROM measurements').fetchone())
    finally:
        db.close()
def check(label, actual, expected):
    observations.append({"check": label, "actual": actual, "expected": expected, "passed": actual == expected})
check('no input rows', solve([]), [None, 0, 0])
check('all rows unknown', solve([None] * N), [None, 0, N])
check('a real zero is a measurement', solve([0]), [0, 1, 1])
check('positive and negative values cancel', solve([N, -N, None]), [0, 2, 3])
check('known values amid unknowns', solve([N, None, 2*N]), [3*N, 2, 3])
check('negative aggregate is retained', solve([-N, -N-1]), [-2*N-1, 2, 2])
check('repeated zero rows retain counts', solve([0] * (N+1)), [0, N+1, N+1])
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
no input rows	`[0, 0, 0]`	`[None, 0, 0]`	Failed
all rows unknown	`[0, 0, 1]`	`[None, 0, 1]`	Failed
a real zero is a measurement	`[0, 1, 1]`	`[0, 1, 1]`	Passed
positive and negative values cancel	`[0, 2, 3]`	`[0, 2, 3]`	Passed
known values amid unknowns	`[3, 2, 3]`	`[3, 2, 3]`	Passed
negative aggregate is retained	`[-3, 2, 2]`	`[-3, 2, 2]`	Passed
repeated zero rows retain counts	`[0, 2, 2]`	`[0, 2, 2]`	Passed

SHA-256 / 42ff3bcc6c891ff1ebbfe9f5c490675c31af01b0fcbb6aaba54c996eff6cb575

2 / The unsuccessful fix

Exit 1

"""Failure Map reference implementation. Python standard library only."""
import json
import sqlite3
N = 1
observations = []
def solve(values):
    db = sqlite3.connect(':memory:')
    try:
        db.execute('CREATE TABLE measurements (v INTEGER)')
        db.executemany('INSERT INTO measurements VALUES (?)', [(v,) for v in values])
        return list(db.execute('SELECT NULLIF(SUM(v), 0), COUNT(v), COUNT(*) FROM measurements').fetchone())
    finally:
        db.close()
def check(label, actual, expected):
    observations.append({"check": label, "actual": actual, "expected": expected, "passed": actual == expected})
check('no input rows', solve([]), [None, 0, 0])
check('all rows unknown', solve([None] * N), [None, 0, N])
check('a real zero is a measurement', solve([0]), [0, 1, 1])
check('positive and negative values cancel', solve([N, -N, None]), [0, 2, 3])
check('known values amid unknowns', solve([N, None, 2*N]), [3*N, 2, 3])
check('negative aggregate is retained', solve([-N, -N-1]), [-2*N-1, 2, 2])
check('repeated zero rows retain counts', solve([0] * (N+1)), [0, N+1, N+1])
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
no input rows	`[None, 0, 0]`	`[None, 0, 0]`	Passed
all rows unknown	`[None, 0, 1]`	`[None, 0, 1]`	Passed
a real zero is a measurement	`[None, 1, 1]`	`[0, 1, 1]`	Failed
positive and negative values cancel	`[None, 2, 3]`	`[0, 2, 3]`	Failed
known values amid unknowns	`[3, 2, 3]`	`[3, 2, 3]`	Passed
negative aggregate is retained	`[-3, 2, 2]`	`[-3, 2, 2]`	Passed
repeated zero rows retain counts	`[None, 2, 2]`	`[0, 2, 2]`	Failed

SHA-256 / a5786c7ce95aa8b3f533642468f9c7ae66bd6aaa518de88e59a74479a14fb701

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.

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

Case digest / 16a55c662792af90c4fe580dbddcce25f06b60b6c1f16769d218ee95c3c92010