FAILURE MAP
← Case archive

FA-111 / Storage and queries / Open access

An exclusion query loses candidates when its subquery contains NULL · case 01

A single unknown exclusion value removes unrelated candidates from an anti-join result.

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

ROOT CAUSE

SQL NOT IN propagates UNKNOWN from NULL, whereas the requested operation is absence of an equal row.

VERIFIED REPAIR

Use a correlated NOT EXISTS predicate with ordinary SQL equality, preserving left-side NULL values and multiplicity.

Unsuccessful approach: Removing NULL from the exclusion set still drops a NULL candidate whenever the filtered set is nonempty.

Case contract

For each candidate in input order, retain it unless an excluded non-NULL value is SQL-equal to it. NULL never equals any value, including NULL. Duplicates on the left remain duplicates. Inputs are integers or None.

Why this case matters

Executes real in-memory SQLite three-valued logic to isolate nullable exclusion behavior; this does not simulate a complete database optimizer, concurrent workload, or production query plan.

1 / The failure

Exit 1
"""Failure Map reference implementation. Python standard library only."""
import json
import sqlite3
N = 1
observations = []
def solve(candidates, excluded):
    db = sqlite3.connect(':memory:')
    try:
        db.execute('CREATE TABLE candidates (v INTEGER)')
        db.execute('CREATE TABLE excluded (v INTEGER)')
        db.executemany('INSERT INTO candidates VALUES (?)', [(v,) for v in candidates])
        db.executemany('INSERT INTO excluded VALUES (?)', [(v,) for v in excluded])
        return [row[0] for row in db.execute('SELECT c.v FROM candidates AS c WHERE c.v NOT IN (SELECT v FROM excluded) ORDER BY c.rowid')]
    finally:
        db.close()
def check(label, actual, expected):
    observations.append({"check": label, "actual": actual, "expected": expected, "passed": actual == expected})
check('unknown exclusion does not poison other values', solve([N, N+1], [None, N]), [N+1])
check('unknown candidate is not equal to a known exclusion', solve([None, N+1], [N]), [None, N+1])
check('NULL is not equal to NULL', solve([None, N], [None]), [None, N])
check('empty exclusion retains every occurrence', solve([N, N, None], []), [N, N, None])
check('exact known matches are excluded', solve([N, N+1, N+2], [N+2, N]), [N+1])
check('left multiplicity survives unrelated right duplicates', solve([N, N, N+1], [N+1, N+1]), [N, N])
check('empty candidates', solve([], [None, N]), [])
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 fixtureActualExpectedOutcome
unknown exclusion does not poison other values[][2]Failed
unknown candidate is not equal to a known exclusion[2][None, 2]Failed
NULL is not equal to NULL[][None, 1]Failed
empty exclusion retains every occurrence[1, 1, None][1, 1, None]Passed
exact known matches are excluded[2][2]Passed
left multiplicity survives unrelated right duplicates[1, 1][1, 1]Passed
empty candidates[][]Passed

SHA-256 / c3b67db39110c3fbd51a27871ef64ddc120636b5816c6eefc43d8baafb87b44b

2 / The unsuccessful fix

Exit 1
"""Failure Map reference implementation. Python standard library only."""
import json
import sqlite3
N = 1
observations = []
def solve(candidates, excluded):
    db = sqlite3.connect(':memory:')
    try:
        db.execute('CREATE TABLE candidates (v INTEGER)')
        db.execute('CREATE TABLE excluded (v INTEGER)')
        db.executemany('INSERT INTO candidates VALUES (?)', [(v,) for v in candidates])
        db.executemany('INSERT INTO excluded VALUES (?)', [(v,) for v in excluded])
        return [row[0] for row in db.execute('SELECT c.v FROM candidates AS c WHERE c.v NOT IN (SELECT v FROM excluded WHERE v IS NOT NULL) ORDER BY c.rowid')]
    finally:
        db.close()
def check(label, actual, expected):
    observations.append({"check": label, "actual": actual, "expected": expected, "passed": actual == expected})
check('unknown exclusion does not poison other values', solve([N, N+1], [None, N]), [N+1])
check('unknown candidate is not equal to a known exclusion', solve([None, N+1], [N]), [None, N+1])
check('NULL is not equal to NULL', solve([None, N], [None]), [None, N])
check('empty exclusion retains every occurrence', solve([N, N, None], []), [N, N, None])
check('exact known matches are excluded', solve([N, N+1, N+2], [N+2, N]), [N+1])
check('left multiplicity survives unrelated right duplicates', solve([N, N, N+1], [N+1, N+1]), [N, N])
check('empty candidates', solve([], [None, N]), [])
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 fixtureActualExpectedOutcome
unknown exclusion does not poison other values[2][2]Passed
unknown candidate is not equal to a known exclusion[2][None, 2]Failed
NULL is not equal to NULL[None, 1][None, 1]Passed
empty exclusion retains every occurrence[1, 1, None][1, 1, None]Passed
exact known matches are excluded[2][2]Passed
left multiplicity survives unrelated right duplicates[1, 1][1, 1]Passed
empty candidates[][]Passed

SHA-256 / 8e58083175b40fdb0eefd1c4aec1b217300e70e9982f74e46bdf062b97c133fe

3 / The verified repair

Exit 0
"""Failure Map reference implementation. Python standard library only."""
import json
import sqlite3
N = 1
observations = []
def solve(candidates, excluded):
    db = sqlite3.connect(':memory:')
    try:
        db.execute('CREATE TABLE candidates (v INTEGER)')
        db.execute('CREATE TABLE excluded (v INTEGER)')
        db.executemany('INSERT INTO candidates VALUES (?)', [(v,) for v in candidates])
        db.executemany('INSERT INTO excluded VALUES (?)', [(v,) for v in excluded])
        return [row[0] for row in db.execute('SELECT c.v FROM candidates AS c WHERE NOT EXISTS (SELECT 1 FROM excluded AS e WHERE e.v = c.v) ORDER BY c.rowid')]
    finally:
        db.close()
def check(label, actual, expected):
    observations.append({"check": label, "actual": actual, "expected": expected, "passed": actual == expected})
check('unknown exclusion does not poison other values', solve([N, N+1], [None, N]), [N+1])
check('unknown candidate is not equal to a known exclusion', solve([None, N+1], [N]), [None, N+1])
check('NULL is not equal to NULL', solve([None, N], [None]), [None, N])
check('empty exclusion retains every occurrence', solve([N, N, None], []), [N, N, None])
check('exact known matches are excluded', solve([N, N+1, N+2], [N+2, N]), [N+1])
check('left multiplicity survives unrelated right duplicates', solve([N, N, N+1], [N+1, N+1]), [N, N])
check('empty candidates', solve([], [None, N]), [])
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 fixtureActualExpectedOutcome
unknown exclusion does not poison other values[2][2]Passed
unknown candidate is not equal to a known exclusion[None, 2][None, 2]Passed
NULL is not equal to NULL[None, 1][None, 1]Passed
empty exclusion retains every occurrence[1, 1, None][1, 1, None]Passed
exact known matches are excluded[2][2]Passed
left multiplicity survives unrelated right duplicates[1, 1][1, 1]Passed
empty candidates[][]Passed

SHA-256 / 86bccbbdf22db8ec59066c8b2bab1df361172c1bda06cad3d05007cb2601bfc5

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

Case digest / 2ba07716a5c4ed2a27461f3a11b34995530ac8dde4ce328fb4b587c67b08e4cb