FA-44821 / Data systems / Open access

Scalar subquery cardinality counts distinct values · case 01

Scalar subquery cardinality counts distinct values.

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

ROOT CAUSE

scalar-subquery-cardinality: Scalar subquery cardinality counts distinct values.

VERIFIED REPAIR

Preserve the stated physical representation and operation order: Evaluate a correlated scalar lookup per outer key. Zero rows yield None, exactly one row yields its value including None, and two or more matching rows yield the explicit value CARDINALITY. Count physical rows, not distinct or non-null values.

Unsuccessful approach: Removing duplicate and null values hides multiple physical rows.

Case contract

Evaluate a correlated scalar lookup per outer key. Zero rows yield None, exactly one row yields its value including None, and two or more matching rows yield the explicit value CARDINALITY. Count physical rows, not distinct or non-null values.

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:
        keys,inner=d
        out=[]
        for key in keys:
            matches=[value for k,value in inner if key is not None and k==key]
            count=len(set(matches))
            if count==0: value=None
            elif count==1: value=matches[0]
            else: value='CARDINALITY'
            out.append(value)
        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('duplicate physical rows', solve([['a'], [['a', 1], ['a', 1]]]), ['CARDINALITY'])
    check('one null and one value', solve([['a'], [['a', None], ['a', 1]]]), ['CARDINALITY'])
    check('unrelated keys', solve([['a', 'b'], [['a', 1], ['b', 2]]]), [1, 2])
    check('null correlation', solve([[None], [[None, 1]]]), [None])
    check('empty lookup', solve([['a'], []]), [None])
    check('single null', solve([['a'], [['a', None]]]), [None])
    check('two values', solve([['a'], [['a', 1], ['a', 2]]]), ['CARDINALITY'])
    check('null row cannot correlate', solve([['a'], [['a', 1], [None, 2]]]), [1])
    check('no outer rows', solve([[], [['a', 1]]]), [])
elif N == 2:
    check('duplicate physical rows', solve([['a'], [['a', 2], ['a', 2]]]), ['CARDINALITY'])
    check('one null and one value', solve([['a'], [['a', None], ['a', 2]]]), ['CARDINALITY'])
    check('unrelated keys', solve([['a', 'b'], [['a', 2], ['b', 3]]]), [2, 3])
    check('null correlation', solve([[None], [[None, 2]]]), [None])
    check('empty lookup', solve([['a'], []]), [None])
    check('single null', solve([['a'], [['a', None]]]), [None])
    check('two values', solve([['a'], [['a', 2], ['a', 3]]]), ['CARDINALITY'])
    check('null row cannot correlate', solve([['a'], [['a', 2], [None, 3]]]), [2])
    check('no outer rows', solve([[], [['a', 2]]]), [])
elif N == 3:
    check('duplicate physical rows', solve([['a'], [['a', 3], ['a', 3]]]), ['CARDINALITY'])
    check('one null and one value', solve([['a'], [['a', None], ['a', 3]]]), ['CARDINALITY'])
    check('unrelated keys', solve([['a', 'b'], [['a', 3], ['b', 4]]]), [3, 4])
    check('null correlation', solve([[None], [[None, 3]]]), [None])
    check('empty lookup', solve([['a'], []]), [None])
    check('single null', solve([['a'], [['a', None]]]), [None])
    check('two values', solve([['a'], [['a', 3], ['a', 4]]]), ['CARDINALITY'])
    check('null row cannot correlate', solve([['a'], [['a', 3], [None, 4]]]), [3])
    check('no outer rows', solve([[], [['a', 3]]]), [])
elif N == 4:
    check('duplicate physical rows', solve([['a'], [['a', 4], ['a', 4]]]), ['CARDINALITY'])
    check('one null and one value', solve([['a'], [['a', None], ['a', 4]]]), ['CARDINALITY'])
    check('unrelated keys', solve([['a', 'b'], [['a', 4], ['b', 5]]]), [4, 5])
    check('null correlation', solve([[None], [[None, 4]]]), [None])
    check('empty lookup', solve([['a'], []]), [None])
    check('single null', solve([['a'], [['a', None]]]), [None])
    check('two values', solve([['a'], [['a', 4], ['a', 5]]]), ['CARDINALITY'])
    check('null row cannot correlate', solve([['a'], [['a', 4], [None, 5]]]), [4])
    check('no outer rows', solve([[], [['a', 4]]]), [])
elif N == 5:
    check('duplicate physical rows', solve([['a'], [['a', 5], ['a', 5]]]), ['CARDINALITY'])
    check('one null and one value', solve([['a'], [['a', None], ['a', 5]]]), ['CARDINALITY'])
    check('unrelated keys', solve([['a', 'b'], [['a', 5], ['b', 6]]]), [5, 6])
    check('null correlation', solve([[None], [[None, 5]]]), [None])
    check('empty lookup', solve([['a'], []]), [None])
    check('single null', solve([['a'], [['a', None]]]), [None])
    check('two values', solve([['a'], [['a', 5], ['a', 6]]]), ['CARDINALITY'])
    check('null row cannot correlate', solve([['a'], [['a', 5], [None, 6]]]), [5])
    check('no outer rows', solve([[], [['a', 5]]]), [])
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
duplicate physical rows	`[1]`	`['CARDINALITY']`	Failed
one null and one value	`['CARDINALITY']`	`['CARDINALITY']`	Passed
unrelated keys	`[1, 2]`	`[1, 2]`	Passed
null correlation	`[None]`	`[None]`	Passed
empty lookup	`[None]`	`[None]`	Passed
single null	`[None]`	`[None]`	Passed
two values	`['CARDINALITY']`	`['CARDINALITY']`	Passed
null row cannot correlate	`[1]`	`[1]`	Passed
no outer rows	`[]`	`[]`	Passed

SHA-256 / b43c0f5d7c9936a9070eb4226907c14ab826597c80125477f4a1a2ec56a63fcc

2 / The unsuccessful fix

Exit 1

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

N = 1
observations = []
def solve(d):
    try:
        keys,inner=d
        out=[]
        for key in keys:
            matches=[value for k,value in inner if key is not None and k==key]
            count=sum(v is not None for v in set(matches))
            if count==0: value=None
            elif count==1: value=matches[0]
            else: value='CARDINALITY'
            out.append(value)
        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('duplicate physical rows', solve([['a'], [['a', 1], ['a', 1]]]), ['CARDINALITY'])
    check('one null and one value', solve([['a'], [['a', None], ['a', 1]]]), ['CARDINALITY'])
    check('unrelated keys', solve([['a', 'b'], [['a', 1], ['b', 2]]]), [1, 2])
    check('null correlation', solve([[None], [[None, 1]]]), [None])
    check('empty lookup', solve([['a'], []]), [None])
    check('single null', solve([['a'], [['a', None]]]), [None])
    check('two values', solve([['a'], [['a', 1], ['a', 2]]]), ['CARDINALITY'])
    check('null row cannot correlate', solve([['a'], [['a', 1], [None, 2]]]), [1])
    check('no outer rows', solve([[], [['a', 1]]]), [])
elif N == 2:
    check('duplicate physical rows', solve([['a'], [['a', 2], ['a', 2]]]), ['CARDINALITY'])
    check('one null and one value', solve([['a'], [['a', None], ['a', 2]]]), ['CARDINALITY'])
    check('unrelated keys', solve([['a', 'b'], [['a', 2], ['b', 3]]]), [2, 3])
    check('null correlation', solve([[None], [[None, 2]]]), [None])
    check('empty lookup', solve([['a'], []]), [None])
    check('single null', solve([['a'], [['a', None]]]), [None])
    check('two values', solve([['a'], [['a', 2], ['a', 3]]]), ['CARDINALITY'])
    check('null row cannot correlate', solve([['a'], [['a', 2], [None, 3]]]), [2])
    check('no outer rows', solve([[], [['a', 2]]]), [])
elif N == 3:
    check('duplicate physical rows', solve([['a'], [['a', 3], ['a', 3]]]), ['CARDINALITY'])
    check('one null and one value', solve([['a'], [['a', None], ['a', 3]]]), ['CARDINALITY'])
    check('unrelated keys', solve([['a', 'b'], [['a', 3], ['b', 4]]]), [3, 4])
    check('null correlation', solve([[None], [[None, 3]]]), [None])
    check('empty lookup', solve([['a'], []]), [None])
    check('single null', solve([['a'], [['a', None]]]), [None])
    check('two values', solve([['a'], [['a', 3], ['a', 4]]]), ['CARDINALITY'])
    check('null row cannot correlate', solve([['a'], [['a', 3], [None, 4]]]), [3])
    check('no outer rows', solve([[], [['a', 3]]]), [])
elif N == 4:
    check('duplicate physical rows', solve([['a'], [['a', 4], ['a', 4]]]), ['CARDINALITY'])
    check('one null and one value', solve([['a'], [['a', None], ['a', 4]]]), ['CARDINALITY'])
    check('unrelated keys', solve([['a', 'b'], [['a', 4], ['b', 5]]]), [4, 5])
    check('null correlation', solve([[None], [[None, 4]]]), [None])
    check('empty lookup', solve([['a'], []]), [None])
    check('single null', solve([['a'], [['a', None]]]), [None])
    check('two values', solve([['a'], [['a', 4], ['a', 5]]]), ['CARDINALITY'])
    check('null row cannot correlate', solve([['a'], [['a', 4], [None, 5]]]), [4])
    check('no outer rows', solve([[], [['a', 4]]]), [])
elif N == 5:
    check('duplicate physical rows', solve([['a'], [['a', 5], ['a', 5]]]), ['CARDINALITY'])
    check('one null and one value', solve([['a'], [['a', None], ['a', 5]]]), ['CARDINALITY'])
    check('unrelated keys', solve([['a', 'b'], [['a', 5], ['b', 6]]]), [5, 6])
    check('null correlation', solve([[None], [[None, 5]]]), [None])
    check('empty lookup', solve([['a'], []]), [None])
    check('single null', solve([['a'], [['a', None]]]), [None])
    check('two values', solve([['a'], [['a', 5], ['a', 6]]]), ['CARDINALITY'])
    check('null row cannot correlate', solve([['a'], [['a', 5], [None, 6]]]), [5])
    check('no outer rows', solve([[], [['a', 5]]]), [])
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
duplicate physical rows	`[1]`	`['CARDINALITY']`	Failed
one null and one value	`[None]`	`['CARDINALITY']`	Failed
unrelated keys	`[1, 2]`	`[1, 2]`	Passed
null correlation	`[None]`	`[None]`	Passed
empty lookup	`[None]`	`[None]`	Passed
single null	`[None]`	`[None]`	Passed
two values	`['CARDINALITY']`	`['CARDINALITY']`	Passed
null row cannot correlate	`[1]`	`[1]`	Passed
no outer rows	`[]`	`[]`	Passed

SHA-256 / fef8c12cccce290fa211db3679fe669bd692456ffe4ae0c355bce261455fe16f

3 / The verified repair

Exit 0

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

N = 1
observations = []
def solve(d):
    try:
        keys,inner=d
        out=[]
        for key in keys:
            matches=[value for k,value in inner if key is not None and k==key]
            count=len(matches)
            if count==0: value=None
            elif count==1: value=matches[0]
            else: value='CARDINALITY'
            out.append(value)
        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('duplicate physical rows', solve([['a'], [['a', 1], ['a', 1]]]), ['CARDINALITY'])
    check('one null and one value', solve([['a'], [['a', None], ['a', 1]]]), ['CARDINALITY'])
    check('unrelated keys', solve([['a', 'b'], [['a', 1], ['b', 2]]]), [1, 2])
    check('null correlation', solve([[None], [[None, 1]]]), [None])
    check('empty lookup', solve([['a'], []]), [None])
    check('single null', solve([['a'], [['a', None]]]), [None])
    check('two values', solve([['a'], [['a', 1], ['a', 2]]]), ['CARDINALITY'])
    check('null row cannot correlate', solve([['a'], [['a', 1], [None, 2]]]), [1])
    check('no outer rows', solve([[], [['a', 1]]]), [])
elif N == 2:
    check('duplicate physical rows', solve([['a'], [['a', 2], ['a', 2]]]), ['CARDINALITY'])
    check('one null and one value', solve([['a'], [['a', None], ['a', 2]]]), ['CARDINALITY'])
    check('unrelated keys', solve([['a', 'b'], [['a', 2], ['b', 3]]]), [2, 3])
    check('null correlation', solve([[None], [[None, 2]]]), [None])
    check('empty lookup', solve([['a'], []]), [None])
    check('single null', solve([['a'], [['a', None]]]), [None])
    check('two values', solve([['a'], [['a', 2], ['a', 3]]]), ['CARDINALITY'])
    check('null row cannot correlate', solve([['a'], [['a', 2], [None, 3]]]), [2])
    check('no outer rows', solve([[], [['a', 2]]]), [])
elif N == 3:
    check('duplicate physical rows', solve([['a'], [['a', 3], ['a', 3]]]), ['CARDINALITY'])
    check('one null and one value', solve([['a'], [['a', None], ['a', 3]]]), ['CARDINALITY'])
    check('unrelated keys', solve([['a', 'b'], [['a', 3], ['b', 4]]]), [3, 4])
    check('null correlation', solve([[None], [[None, 3]]]), [None])
    check('empty lookup', solve([['a'], []]), [None])
    check('single null', solve([['a'], [['a', None]]]), [None])
    check('two values', solve([['a'], [['a', 3], ['a', 4]]]), ['CARDINALITY'])
    check('null row cannot correlate', solve([['a'], [['a', 3], [None, 4]]]), [3])
    check('no outer rows', solve([[], [['a', 3]]]), [])
elif N == 4:
    check('duplicate physical rows', solve([['a'], [['a', 4], ['a', 4]]]), ['CARDINALITY'])
    check('one null and one value', solve([['a'], [['a', None], ['a', 4]]]), ['CARDINALITY'])
    check('unrelated keys', solve([['a', 'b'], [['a', 4], ['b', 5]]]), [4, 5])
    check('null correlation', solve([[None], [[None, 4]]]), [None])
    check('empty lookup', solve([['a'], []]), [None])
    check('single null', solve([['a'], [['a', None]]]), [None])
    check('two values', solve([['a'], [['a', 4], ['a', 5]]]), ['CARDINALITY'])
    check('null row cannot correlate', solve([['a'], [['a', 4], [None, 5]]]), [4])
    check('no outer rows', solve([[], [['a', 4]]]), [])
elif N == 5:
    check('duplicate physical rows', solve([['a'], [['a', 5], ['a', 5]]]), ['CARDINALITY'])
    check('one null and one value', solve([['a'], [['a', None], ['a', 5]]]), ['CARDINALITY'])
    check('unrelated keys', solve([['a', 'b'], [['a', 5], ['b', 6]]]), [5, 6])
    check('null correlation', solve([[None], [[None, 5]]]), [None])
    check('empty lookup', solve([['a'], []]), [None])
    check('single null', solve([['a'], [['a', None]]]), [None])
    check('two values', solve([['a'], [['a', 5], ['a', 6]]]), ['CARDINALITY'])
    check('null row cannot correlate', solve([['a'], [['a', 5], [None, 6]]]), [5])
    check('no outer rows', solve([[], [['a', 5]]]), [])
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
duplicate physical rows	`['CARDINALITY']`	`['CARDINALITY']`	Passed
one null and one value	`['CARDINALITY']`	`['CARDINALITY']`	Passed
unrelated keys	`[1, 2]`	`[1, 2]`	Passed
null correlation	`[None]`	`[None]`	Passed
empty lookup	`[None]`	`[None]`	Passed
single null	`[None]`	`[None]`	Passed
two values	`['CARDINALITY']`	`['CARDINALITY']`	Passed
null row cannot correlate	`[1]`	`[1]`	Passed
no outer rows	`[]`	`[]`	Passed

SHA-256 / 73080acd94c7c7776982dd9bb7d339cf014226446e4ff5671c5a65aee079a914

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

Case digest / 31853eb5cedc7f7b96b2cef426c8ce7c4310c65301b51ef1c0dae7acca419b1c