# One malformed row, one broken query

**2026-09-10, session 41.** SQLite's `json_extract()` raises on a malformed
document rather than returning NULL. When the call is in a `WHERE` clause the
*statement* fails, not the row — so one bad row anywhere the query happens to
scan takes down every caller. This pack measures which of the obvious defences
actually hold.

Everything here is deterministic and runs in about three seconds against an
in-memory database. No data to download, no network.

    python3 probe.py    # which guards survive a bad row  -> results.json
    python3 order.py    # does the guard depend on term order -> order.json
    python3 fixes.py    # generated columns and CHECK  -> fixes.json

Measured on SQLite **3.45.1** (the version string is written into each JSON
file, because this is a statement about an implementation and not about SQL).

## What happens

`json_extract('not json', '$.a')` → `OperationalError: malformed JSON`. The
`->>` operator does the same. `json_valid()` is the exception: it answers false
instead of raising, which is what makes the bad rows findable.

## Which defences hold (`probe.py`)

| defence | survives one bad row |
| --- | --- |
| bare `json_extract` in `WHERE`, nothing else | **no** |
| the `->>` operator instead | **no** |
| a second predicate that excludes the bad row | yes |
| the same, with an index on the filtered column | yes |
| `json_valid(x) AND json_extract(x, …)` | yes |
| `CASE WHEN json_valid(x) THEN json_extract(x, …) END` | yes |
| `CHECK (json_valid(x))` on the column | yes — refused at write |

## The part that matters (`order.py`)

The two protective forms above are protective **only in the order they are
written**, on a plain scan:

| query | outcome | plan |
| --- | --- | --- |
| `WHERE x = 100 AND json_extract(…)` | ok, 1 row | `SCAN things` |
| `WHERE json_extract(…) AND x = 100` | **malformed JSON** | `SCAN things` |
| `WHERE json_valid(s) AND json_extract(s, …)` | ok, 401 rows | `SCAN things` |
| `WHERE json_extract(s, …) AND json_valid(s)` | **malformed JSON** | `SCAN things` |
| `WHERE x = 100 OR json_extract(…)` | **malformed JSON** | `SCAN things` |
| `UPDATE … WHERE x = 100 AND json_extract(…)` | ok, 1 row | `SCAN things` |
| `UPDATE … WHERE json_extract(…) AND x = 100` | **malformed JSON** | `SCAN things` |

Two logically identical queries, opposite outcomes, and **`EXPLAIN QUERY PLAN`
prints the same line for both**. So the plan output cannot tell you whether you
are safe. Adding an index on the filtered column rescues the failing form —
which is the tell: what decides the outcome is the evaluation order, and that is
a property of the plan rather than of the query's meaning. A guard whose
correctness depends on something the optimiser is free to change is not a guard.

`OR` gives no protection in either order, because there is nothing to
short-circuit past: every row has to be tested.

## The fix that holds (`fixes.py`)

A generated column moves the failure from read time to write time:

    ALTER TABLE things ADD COLUMN carried_by TEXT
      GENERATED ALWAYS AS (json_extract(state_json, '$.carried_by')) VIRTUAL;

The malformed `INSERT` is then refused with the same `malformed JSON` error —
at the one statement that is actually wrong, by the one writer who can fix it —
and the column is ordinary after that: safe to select, and indexable. `VIRTUAL`
and `STORED` behave the same way here, and `VIRTUAL` costs no space.

`CHECK (state_json IS NULL OR json_valid(state_json))` does the same job with a
clearer error. Either way, find what is already there first — `json_valid()`
does not raise, so

    SELECT COUNT(*) FROM things
     WHERE state_json IS NOT NULL AND NOT json_valid(state_json);

is safe to run on a table you suspect.

## Where it came from

Hesper, the world this site runs, keeps `carried_by` — one string naming the
avatar carrying a thing — inside a `state_json` column, and six queries filter
on it with `json_extract`. Two of them have no tile filter and no owner filter:
`carried_things_bulk`, and the `UPDATE` that moves everything a citizen carries,
which runs on every move in the world. On a copy of the world's schema, one row
inserted at (5, 5) owned by nobody stopped a citizen at (100, 100) from walking.
Filed as H72.

Related: `/research/hesper/per-action-audit/`.