{"abstract":"Scalar subquery cardinality counts distinct values.","category":"Data systems","checks":9,"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.","evaluation_group":"s3-data-systems-scalar-subquery-cardinality","failed_approach":"Removing duplicate and null values hides multiple physical rows.","family":"s3-data-systems-scalar-subquery-cardinality-distinct-cardinality","id":"FA-44821","implementations":{"attempt":{"sha256":"fef8c12cccce290fa211db3679fe669bd692456ffe4ae0c355bce261455fe16f","source":"\"\"\"Failure Map reference implementation. Python standard library only.\"\"\"\nimport json\n\nN = 1\nobservations = []\ndef solve(d):\n    try:\n        keys,inner=d\n        out=[]\n        for key in keys:\n            matches=[value for k,value in inner if key is not None and k==key]\n            count=sum(v is not None for v in set(matches))\n            if count==0: value=None\n            elif count==1: value=matches[0]\n            else: value='CARDINALITY'\n            out.append(value)\n        return out\n    except (IndexError, KeyError, ValueError, StopIteration) as exc:\n        return {\"representation_error\": type(exc).__name__}\ndef check(label, actual, expected):\n    observations.append({\"check\": label, \"actual\": actual, \"expected\": expected, \"passed\": actual == expected})\nif N == 1:\n    check('duplicate physical rows', solve([['a'], [['a', 1], ['a', 1]]]), ['CARDINALITY'])\n    check('one null and one value', solve([['a'], [['a', None], ['a', 1]]]), ['CARDINALITY'])\n    check('unrelated keys', solve([['a', 'b'], [['a', 1], ['b', 2]]]), [1, 2])\n    check('null correlation', solve([[None], [[None, 1]]]), [None])\n    check('empty lookup', solve([['a'], []]), [None])\n    check('single null', solve([['a'], [['a', None]]]), [None])\n    check('two values', solve([['a'], [['a', 1], ['a', 2]]]), ['CARDINALITY'])\n    check('null row cannot correlate', solve([['a'], [['a', 1], [None, 2]]]), [1])\n    check('no outer rows', solve([[], [['a', 1]]]), [])\nelif N == 2:\n    check('duplicate physical rows', solve([['a'], [['a', 2], ['a', 2]]]), ['CARDINALITY'])\n    check('one null and one value', solve([['a'], [['a', None], ['a', 2]]]), ['CARDINALITY'])\n    check('unrelated keys', solve([['a', 'b'], [['a', 2], ['b', 3]]]), [2, 3])\n    check('null correlation', solve([[None], [[None, 2]]]), [None])\n    check('empty lookup', solve([['a'], []]), [None])\n    check('single null', solve([['a'], [['a', None]]]), [None])\n    check('two values', solve([['a'], [['a', 2], ['a', 3]]]), ['CARDINALITY'])\n    check('null row cannot correlate', solve([['a'], [['a', 2], [None, 3]]]), [2])\n    check('no outer rows', solve([[], [['a', 2]]]), [])\nelif N == 3:\n    check('duplicate physical rows', solve([['a'], [['a', 3], ['a', 3]]]), ['CARDINALITY'])\n    check('one null and one value', solve([['a'], [['a', None], ['a', 3]]]), ['CARDINALITY'])\n    check('unrelated keys', solve([['a', 'b'], [['a', 3], ['b', 4]]]), [3, 4])\n    check('null correlation', solve([[None], [[None, 3]]]), [None])\n    check('empty lookup', solve([['a'], []]), [None])\n    check('single null', solve([['a'], [['a', None]]]), [None])\n    check('two values', solve([['a'], [['a', 3], ['a', 4]]]), ['CARDINALITY'])\n    check('null row cannot correlate', solve([['a'], [['a', 3], [None, 4]]]), [3])\n    check('no outer rows', solve([[], [['a', 3]]]), [])\nelif N == 4:\n    check('duplicate physical rows', solve([['a'], [['a', 4], ['a', 4]]]), ['CARDINALITY'])\n    check('one null and one value', solve([['a'], [['a', None], ['a', 4]]]), ['CARDINALITY'])\n    check('unrelated keys', solve([['a', 'b'], [['a', 4], ['b', 5]]]), [4, 5])\n    check('null correlation', solve([[None], [[None, 4]]]), [None])\n    check('empty lookup', solve([['a'], []]), [None])\n    check('single null', solve([['a'], [['a', None]]]), [None])\n    check('two values', solve([['a'], [['a', 4], ['a', 5]]]), ['CARDINALITY'])\n    check('null row cannot correlate', solve([['a'], [['a', 4], [None, 5]]]), [4])\n    check('no outer rows', solve([[], [['a', 4]]]), [])\nelif N == 5:\n    check('duplicate physical rows', solve([['a'], [['a', 5], ['a', 5]]]), ['CARDINALITY'])\n    check('one null and one value', solve([['a'], [['a', None], ['a', 5]]]), ['CARDINALITY'])\n    check('unrelated keys', solve([['a', 'b'], [['a', 5], ['b', 6]]]), [5, 6])\n    check('null correlation', solve([[None], [[None, 5]]]), [None])\n    check('empty lookup', solve([['a'], []]), [None])\n    check('single null', solve([['a'], [['a', None]]]), [None])\n    check('two values', solve([['a'], [['a', 5], ['a', 6]]]), ['CARDINALITY'])\n    check('null row cannot correlate', solve([['a'], [['a', 5], [None, 6]]]), [5])\n    check('no outer rows', solve([[], [['a', 5]]]), [])\nprint(json.dumps({\"observations\": observations, \"passed\": all(x[\"passed\"] for x in observations)}, ensure_ascii=False))\nraise SystemExit(0 if all(x[\"passed\"] for x in observations) else 1)\n"},"broken":{"sha256":"b43c0f5d7c9936a9070eb4226907c14ab826597c80125477f4a1a2ec56a63fcc","source":"\"\"\"Failure Map reference implementation. Python standard library only.\"\"\"\nimport json\n\nN = 1\nobservations = []\ndef solve(d):\n    try:\n        keys,inner=d\n        out=[]\n        for key in keys:\n            matches=[value for k,value in inner if key is not None and k==key]\n            count=len(set(matches))\n            if count==0: value=None\n            elif count==1: value=matches[0]\n            else: value='CARDINALITY'\n            out.append(value)\n        return out\n    except (IndexError, KeyError, ValueError, StopIteration) as exc:\n        return {\"representation_error\": type(exc).__name__}\ndef check(label, actual, expected):\n    observations.append({\"check\": label, \"actual\": actual, \"expected\": expected, \"passed\": actual == expected})\nif N == 1:\n    check('duplicate physical rows', solve([['a'], [['a', 1], ['a', 1]]]), ['CARDINALITY'])\n    check('one null and one value', solve([['a'], [['a', None], ['a', 1]]]), ['CARDINALITY'])\n    check('unrelated keys', solve([['a', 'b'], [['a', 1], ['b', 2]]]), [1, 2])\n    check('null correlation', solve([[None], [[None, 1]]]), [None])\n    check('empty lookup', solve([['a'], []]), [None])\n    check('single null', solve([['a'], [['a', None]]]), [None])\n    check('two values', solve([['a'], [['a', 1], ['a', 2]]]), ['CARDINALITY'])\n    check('null row cannot correlate', solve([['a'], [['a', 1], [None, 2]]]), [1])\n    check('no outer rows', solve([[], [['a', 1]]]), [])\nelif N == 2:\n    check('duplicate physical rows', solve([['a'], [['a', 2], ['a', 2]]]), ['CARDINALITY'])\n    check('one null and one value', solve([['a'], [['a', None], ['a', 2]]]), ['CARDINALITY'])\n    check('unrelated keys', solve([['a', 'b'], [['a', 2], ['b', 3]]]), [2, 3])\n    check('null correlation', solve([[None], [[None, 2]]]), [None])\n    check('empty lookup', solve([['a'], []]), [None])\n    check('single null', solve([['a'], [['a', None]]]), [None])\n    check('two values', solve([['a'], [['a', 2], ['a', 3]]]), ['CARDINALITY'])\n    check('null row cannot correlate', solve([['a'], [['a', 2], [None, 3]]]), [2])\n    check('no outer rows', solve([[], [['a', 2]]]), [])\nelif N == 3:\n    check('duplicate physical rows', solve([['a'], [['a', 3], ['a', 3]]]), ['CARDINALITY'])\n    check('one null and one value', solve([['a'], [['a', None], ['a', 3]]]), ['CARDINALITY'])\n    check('unrelated keys', solve([['a', 'b'], [['a', 3], ['b', 4]]]), [3, 4])\n    check('null correlation', solve([[None], [[None, 3]]]), [None])\n    check('empty lookup', solve([['a'], []]), [None])\n    check('single null', solve([['a'], [['a', None]]]), [None])\n    check('two values', solve([['a'], [['a', 3], ['a', 4]]]), ['CARDINALITY'])\n    check('null row cannot correlate', solve([['a'], [['a', 3], [None, 4]]]), [3])\n    check('no outer rows', solve([[], [['a', 3]]]), [])\nelif N == 4:\n    check('duplicate physical rows', solve([['a'], [['a', 4], ['a', 4]]]), ['CARDINALITY'])\n    check('one null and one value', solve([['a'], [['a', None], ['a', 4]]]), ['CARDINALITY'])\n    check('unrelated keys', solve([['a', 'b'], [['a', 4], ['b', 5]]]), [4, 5])\n    check('null correlation', solve([[None], [[None, 4]]]), [None])\n    check('empty lookup', solve([['a'], []]), [None])\n    check('single null', solve([['a'], [['a', None]]]), [None])\n    check('two values', solve([['a'], [['a', 4], ['a', 5]]]), ['CARDINALITY'])\n    check('null row cannot correlate', solve([['a'], [['a', 4], [None, 5]]]), [4])\n    check('no outer rows', solve([[], [['a', 4]]]), [])\nelif N == 5:\n    check('duplicate physical rows', solve([['a'], [['a', 5], ['a', 5]]]), ['CARDINALITY'])\n    check('one null and one value', solve([['a'], [['a', None], ['a', 5]]]), ['CARDINALITY'])\n    check('unrelated keys', solve([['a', 'b'], [['a', 5], ['b', 6]]]), [5, 6])\n    check('null correlation', solve([[None], [[None, 5]]]), [None])\n    check('empty lookup', solve([['a'], []]), [None])\n    check('single null', solve([['a'], [['a', None]]]), [None])\n    check('two values', solve([['a'], [['a', 5], ['a', 6]]]), ['CARDINALITY'])\n    check('null row cannot correlate', solve([['a'], [['a', 5], [None, 6]]]), [5])\n    check('no outer rows', solve([[], [['a', 5]]]), [])\nprint(json.dumps({\"observations\": observations, \"passed\": all(x[\"passed\"] for x in observations)}, ensure_ascii=False))\nraise SystemExit(0 if all(x[\"passed\"] for x in observations) else 1)\n"},"fixed":{"sha256":"73080acd94c7c7776982dd9bb7d339cf014226446e4ff5671c5a65aee079a914","source":"\"\"\"Failure Map reference implementation. Python standard library only.\"\"\"\nimport json\n\nN = 1\nobservations = []\ndef solve(d):\n    try:\n        keys,inner=d\n        out=[]\n        for key in keys:\n            matches=[value for k,value in inner if key is not None and k==key]\n            count=len(matches)\n            if count==0: value=None\n            elif count==1: value=matches[0]\n            else: value='CARDINALITY'\n            out.append(value)\n        return out\n    except (IndexError, KeyError, ValueError, StopIteration) as exc:\n        return {\"representation_error\": type(exc).__name__}\ndef check(label, actual, expected):\n    observations.append({\"check\": label, \"actual\": actual, \"expected\": expected, \"passed\": actual == expected})\nif N == 1:\n    check('duplicate physical rows', solve([['a'], [['a', 1], ['a', 1]]]), ['CARDINALITY'])\n    check('one null and one value', solve([['a'], [['a', None], ['a', 1]]]), ['CARDINALITY'])\n    check('unrelated keys', solve([['a', 'b'], [['a', 1], ['b', 2]]]), [1, 2])\n    check('null correlation', solve([[None], [[None, 1]]]), [None])\n    check('empty lookup', solve([['a'], []]), [None])\n    check('single null', solve([['a'], [['a', None]]]), [None])\n    check('two values', solve([['a'], [['a', 1], ['a', 2]]]), ['CARDINALITY'])\n    check('null row cannot correlate', solve([['a'], [['a', 1], [None, 2]]]), [1])\n    check('no outer rows', solve([[], [['a', 1]]]), [])\nelif N == 2:\n    check('duplicate physical rows', solve([['a'], [['a', 2], ['a', 2]]]), ['CARDINALITY'])\n    check('one null and one value', solve([['a'], [['a', None], ['a', 2]]]), ['CARDINALITY'])\n    check('unrelated keys', solve([['a', 'b'], [['a', 2], ['b', 3]]]), [2, 3])\n    check('null correlation', solve([[None], [[None, 2]]]), [None])\n    check('empty lookup', solve([['a'], []]), [None])\n    check('single null', solve([['a'], [['a', None]]]), [None])\n    check('two values', solve([['a'], [['a', 2], ['a', 3]]]), ['CARDINALITY'])\n    check('null row cannot correlate', solve([['a'], [['a', 2], [None, 3]]]), [2])\n    check('no outer rows', solve([[], [['a', 2]]]), [])\nelif N == 3:\n    check('duplicate physical rows', solve([['a'], [['a', 3], ['a', 3]]]), ['CARDINALITY'])\n    check('one null and one value', solve([['a'], [['a', None], ['a', 3]]]), ['CARDINALITY'])\n    check('unrelated keys', solve([['a', 'b'], [['a', 3], ['b', 4]]]), [3, 4])\n    check('null correlation', solve([[None], [[None, 3]]]), [None])\n    check('empty lookup', solve([['a'], []]), [None])\n    check('single null', solve([['a'], [['a', None]]]), [None])\n    check('two values', solve([['a'], [['a', 3], ['a', 4]]]), ['CARDINALITY'])\n    check('null row cannot correlate', solve([['a'], [['a', 3], [None, 4]]]), [3])\n    check('no outer rows', solve([[], [['a', 3]]]), [])\nelif N == 4:\n    check('duplicate physical rows', solve([['a'], [['a', 4], ['a', 4]]]), ['CARDINALITY'])\n    check('one null and one value', solve([['a'], [['a', None], ['a', 4]]]), ['CARDINALITY'])\n    check('unrelated keys', solve([['a', 'b'], [['a', 4], ['b', 5]]]), [4, 5])\n    check('null correlation', solve([[None], [[None, 4]]]), [None])\n    check('empty lookup', solve([['a'], []]), [None])\n    check('single null', solve([['a'], [['a', None]]]), [None])\n    check('two values', solve([['a'], [['a', 4], ['a', 5]]]), ['CARDINALITY'])\n    check('null row cannot correlate', solve([['a'], [['a', 4], [None, 5]]]), [4])\n    check('no outer rows', solve([[], [['a', 4]]]), [])\nelif N == 5:\n    check('duplicate physical rows', solve([['a'], [['a', 5], ['a', 5]]]), ['CARDINALITY'])\n    check('one null and one value', solve([['a'], [['a', None], ['a', 5]]]), ['CARDINALITY'])\n    check('unrelated keys', solve([['a', 'b'], [['a', 5], ['b', 6]]]), [5, 6])\n    check('null correlation', solve([[None], [[None, 5]]]), [None])\n    check('empty lookup', solve([['a'], []]), [None])\n    check('single null', solve([['a'], [['a', None]]]), [None])\n    check('two values', solve([['a'], [['a', 5], ['a', 6]]]), ['CARDINALITY'])\n    check('null row cannot correlate', solve([['a'], [['a', 5], [None, 6]]]), [5])\n    check('no outer rows', solve([[], [['a', 5]]]), [])\nprint(json.dumps({\"observations\": observations, \"passed\": all(x[\"passed\"] for x in observations)}, ensure_ascii=False))\nraise SystemExit(0 if all(x[\"passed\"] for x in observations) else 1)\n"}},"limitations":"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.","method":"Deterministic executable model with adversarial boundary fixtures.","provenance":{"created_by":"Failure Map","dependencies":"Python standard library","family":"s3-data-systems-scalar-subquery-cardinality-distinct-cardinality","generated_at":"2026-09-29T14:44:15.946382+00:00","license":"CC0-1.0","python":"3.12.14","seed":1,"split":"open-access"},"relevance":"A bounded deterministic data engine model makes representation and changelog faults reproducible.","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.","root_cause":"scalar-subquery-cardinality: Scalar subquery cardinality counts distinct values.","sha256":"31853eb5cedc7f7b96b2cef426c8ce7c4310c65301b51ef1c0dae7acca419b1c","title":"Scalar subquery cardinality counts distinct values · case 01","variant":1,"variant_policy":"Five numbered records share a model and may reuse boundary fixtures.","verification":{"attempt":{"elapsed_ms":41.512,"exit_code":1,"observations":[{"actual":[1],"check":"duplicate physical rows","expected":["CARDINALITY"],"passed":false},{"actual":[null],"check":"one null and one value","expected":["CARDINALITY"],"passed":false},{"actual":[1,2],"check":"unrelated keys","expected":[1,2],"passed":true},{"actual":[null],"check":"null correlation","expected":[null],"passed":true},{"actual":[null],"check":"empty lookup","expected":[null],"passed":true},{"actual":[null],"check":"single null","expected":[null],"passed":true},{"actual":["CARDINALITY"],"check":"two values","expected":["CARDINALITY"],"passed":true},{"actual":[1],"check":"null row cannot correlate","expected":[1],"passed":true},{"actual":[],"check":"no outer rows","expected":[],"passed":true}],"passed":false,"stderr":"","stdout":"{\"observations\": [{\"check\": \"duplicate physical rows\", \"actual\": [1], \"expected\": [\"CARDINALITY\"], \"passed\": false}, {\"check\": \"one null and one value\", \"actual\": [null], \"expected\": [\"CARDINALITY\"], \"passed\": false}, {\"check\": \"unrelated keys\", \"actual\": [1, 2], \"expected\": [1, 2], \"passed\": true}, {\"check\": \"null correlation\", \"actual\": [null], \"expected\": [null], \"passed\": true}, {\"check\": \"empty lookup\", \"actual\": [null], \"expected\": [null], \"passed\": true}, {\"check\": \"single null\", \"actual\": [null], \"expected\": [null], \"passed\": true}, {\"check\": \"two values\", \"actual\": [\"CARDINALITY\"], \"expected\": [\"CARDINALITY\"], \"passed\": true}, {\"check\": \"null row cannot correlate\", \"actual\": [1], \"expected\": [1], \"passed\": true}, {\"check\": \"no outer rows\", \"actual\": [], \"expected\": [], \"passed\": true}], \"passed\": false}\n"},"broken":{"elapsed_ms":41.607,"exit_code":1,"observations":[{"actual":[1],"check":"duplicate physical rows","expected":["CARDINALITY"],"passed":false},{"actual":["CARDINALITY"],"check":"one null and one value","expected":["CARDINALITY"],"passed":true},{"actual":[1,2],"check":"unrelated keys","expected":[1,2],"passed":true},{"actual":[null],"check":"null correlation","expected":[null],"passed":true},{"actual":[null],"check":"empty lookup","expected":[null],"passed":true},{"actual":[null],"check":"single null","expected":[null],"passed":true},{"actual":["CARDINALITY"],"check":"two values","expected":["CARDINALITY"],"passed":true},{"actual":[1],"check":"null row cannot correlate","expected":[1],"passed":true},{"actual":[],"check":"no outer rows","expected":[],"passed":true}],"passed":false,"stderr":"","stdout":"{\"observations\": [{\"check\": \"duplicate physical rows\", \"actual\": [1], \"expected\": [\"CARDINALITY\"], \"passed\": false}, {\"check\": \"one null and one value\", \"actual\": [\"CARDINALITY\"], \"expected\": [\"CARDINALITY\"], \"passed\": true}, {\"check\": \"unrelated keys\", \"actual\": [1, 2], \"expected\": [1, 2], \"passed\": true}, {\"check\": \"null correlation\", \"actual\": [null], \"expected\": [null], \"passed\": true}, {\"check\": \"empty lookup\", \"actual\": [null], \"expected\": [null], \"passed\": true}, {\"check\": \"single null\", \"actual\": [null], \"expected\": [null], \"passed\": true}, {\"check\": \"two values\", \"actual\": [\"CARDINALITY\"], \"expected\": [\"CARDINALITY\"], \"passed\": true}, {\"check\": \"null row cannot correlate\", \"actual\": [1], \"expected\": [1], \"passed\": true}, {\"check\": \"no outer rows\", \"actual\": [], \"expected\": [], \"passed\": true}], \"passed\": false}\n"},"fixed":{"elapsed_ms":46.635,"exit_code":0,"observations":[{"actual":["CARDINALITY"],"check":"duplicate physical rows","expected":["CARDINALITY"],"passed":true},{"actual":["CARDINALITY"],"check":"one null and one value","expected":["CARDINALITY"],"passed":true},{"actual":[1,2],"check":"unrelated keys","expected":[1,2],"passed":true},{"actual":[null],"check":"null correlation","expected":[null],"passed":true},{"actual":[null],"check":"empty lookup","expected":[null],"passed":true},{"actual":[null],"check":"single null","expected":[null],"passed":true},{"actual":["CARDINALITY"],"check":"two values","expected":["CARDINALITY"],"passed":true},{"actual":[1],"check":"null row cannot correlate","expected":[1],"passed":true},{"actual":[],"check":"no outer rows","expected":[],"passed":true}],"passed":true,"stderr":"","stdout":"{\"observations\": [{\"check\": \"duplicate physical rows\", \"actual\": [\"CARDINALITY\"], \"expected\": [\"CARDINALITY\"], \"passed\": true}, {\"check\": \"one null and one value\", \"actual\": [\"CARDINALITY\"], \"expected\": [\"CARDINALITY\"], \"passed\": true}, {\"check\": \"unrelated keys\", \"actual\": [1, 2], \"expected\": [1, 2], \"passed\": true}, {\"check\": \"null correlation\", \"actual\": [null], \"expected\": [null], \"passed\": true}, {\"check\": \"empty lookup\", \"actual\": [null], \"expected\": [null], \"passed\": true}, {\"check\": \"single null\", \"actual\": [null], \"expected\": [null], \"passed\": true}, {\"check\": \"two values\", \"actual\": [\"CARDINALITY\"], \"expected\": [\"CARDINALITY\"], \"passed\": true}, {\"check\": \"null row cannot correlate\", \"actual\": [1], \"expected\": [1], \"passed\": true}, {\"check\": \"no outer rows\", \"actual\": [], \"expected\": [], \"passed\": true}], \"passed\": true}\n"}},"verified":true,"visibility":"public"}