Why SQLite index results can change when database bytes do not

Same file, different runtime, different answer

Each fixture had three rows and one index. Copies opened with the original, missing and changed callback had exactly the same SHA-256. All observations opened those copies read-only; hashes before and after each observation also matched. The rebuilt copies were intentionally modified.

The function fixture stored values 1, 2 and 3. Its original score(x)=x became score(x)=x+100, under the same SQL name. The changed-runtime query for score 102 should select row 2. The table scan did; the old index returned no rows.

The text fixture stored ant, bee and cat as rows 1, 2 and 3. Its named comparator changed from ascending to descending ASCII order. For value COLLATE APP_ORDER >= 'bee', the new comparison admits ant and bee. The old index also returned cat. This is a changed comparison rule, not an alphabetical-order claim.

INDEXED BY idx and NOT INDEXED force the two access paths. The recorded query plans show which path ran. Explicit expected answers, derived from the fixture rules, prevent agreement between two wrong queries from passing the test.

The two missing-callback controls failed the queries explicitly. A missing function still allowed quick_check to return ok; a missing collation made both checking commands fail. A checking error is not an empty diagnostic list or a successful check.

An index preserves assumptions about computation

An expression index stores computed keys. SQLite requires functions used there to be deterministic across the database's lifetime, not merely repeatable within one connection. Tagging an application function deterministic is a promise; it does not make incompatible implementations agree. [3] [2]

A collation supplies comparison behavior to a connection. Its name does not identify a preserved implementation version. These fixtures deliberately changed the promised semantics without rebuilding the old index. They demonstrate a documented compatibility hazard, not a newly discovered SQLite defect. [1]

quick_check omits UNIQUE verification and the comparison of index contents with table contents. Full integrity_check reported missing index entries in both changed-runtime cases. The different outcomes follow the check scopes; neither a file hash nor a quick check tests every runtime dependency. [5]

Agreement is not the same as a correct answer

Rigger and Su's OSDI 2020 paper, Testing Database Engines via Pivoted Query Synthesis, constructs queries that must include a chosen row. Its independent expression evaluator supplies an expectation instead of treating agreement between engines as proof. The paper also explains the boundary: row containment alone cannot validate result cardinality or ordering. [7]

Here the small, explicit fixtures let us check the entire ordered answer, including extra and missing rows. The callback rules define that answer outside the index. This is a manually specified oracle, not a SQLancer run or a reproduction of the paper's bug-finding campaign. Disagreement after deliberately changing index assumptions is not evidence of an optimizer defect.

Small platform changes can matter too

SQLite's current stale-index documentation describes less dramatic sources of drift: math-library differences, ICU changes and revised numeric conversions. It describes such cases as uncommon. Our large, deliberate changes expose the mechanism clearly; they do not measure those real-world changes or their frequency. [4]

Version scope matters. That documentation says SQLite 3.53.0 added repair of some stale expression entries during row updates or deletion, while integrity checking still reports stale entries. It does not establish that opening or querying every incompatible database repairs it. The recorded experiment used 3.45.1 and did not test the newer behavior. [4]

Rebuilding cannot choose new application meaning

With the changed callback registered, REINDEX main.idx restored agreement in both three-row fixtures and full checks returned ok. SQLite documents rebuilding for changed collations and expression functions. The schema-qualified index name selects one object and avoids confusion with a collation of the same name. [6]

A separate two-row control explains why this is not a universal repair recipe. A UNIQUE index originally distinguished A and a. Under a new ASCII case-insensitive comparator, they became equal. quick_check returned ok, full checking reported a non-unique entry, and targeted rebuilding failed with SQLITE_CONSTRAINT_UNIQUE (2067).

The experiment rolled back that attempt and verified both original rows and the original file hash. Resolving this collision requires an application decision about identifiers or duplicate records. A rebuild cannot make two newly equal keys unique without changing data or the rule.

For an upgrade or restore, preserve the original, identify the callback implementations and dependent indexes, and test a disposable restored copy under the intended runtime. Treat a callback change as a migration: examine collisions before rebuilding, validate full check results, then run application acceptance queries. Do not rewrite an archival original merely to silence a diagnostic.

This adds an interpretation dependency to the existing backup verification analysis. A successful copy can preserve every byte and still require application code, comparison rules and their versions to reproduce the intended answers.

Reproduce and inspect the evidence

The standalone Python harness uses the standard library, creates private temporary fixtures and refuses an existing output file. It accepts a working directory, never a production database path. The complete observations include SQL, query plans, full rows, diagnostics, hashes, compile options and SQLite source identity.

python3 observe.py --work-root . --output my-results.json

Eight primary observations cover two index kinds and four runtime states. One additional fixture tests a UNIQUE collision. These are designed cases, not sampled incidents, so no failure-rate estimate or sampling interval applies. The result was recorded on 2026-10-18 with Python 3.12.3 and SQLite 3.45.1.

Copies came from closed DELETE-journal fixtures. No backup API, concurrent writer, live-file copying, WAL, power interruption, ICU upgrade or cross-platform transfer was tested. The forced plans isolate semantics; this is not a benchmark or a prediction of which plan a production query will choose. Rebuilt indexes were checked under the selected callback, not certified against an entire application's requirements.

Primary sources

1. SQLite C API: Define New Collating Sequences
2. SQLite: Deterministic SQL Functions, sections 2 and 4
3. SQLite: Indexes On Expressions, restrictions
4. SQLite: Stale Expression Indexes, platform variation and version-scoped recovery
5. SQLite PRAGMA quick_check: omitted checks
6. SQLite REINDEX: rebuild scope and name resolution
7. Manuel Rigger and Zhendong Su: Testing Database Engines via Pivoted Query Synthesis, OSDI 2020, sections 3 and 6, pp. 667-682