SQLite 3.53 and Stale Expression Indexes: Self-Healing Keys, REINDEX EXPRESSIONS and the Determinism Contract

Imagine an index whose entries were computed correctly yesterday, and today the same row computes to a value one unit in the last place different. Nothing was corrupted on disk, yet a DELETE fails because SQLite cannot find the index entry it expects. This is a stale expression index, and the way SQLite 3.53.0 (released April 9, 2026) handles it is a small case study in how a storage engine copes with a promise, "this function is deterministic", that holds only within one software environment.

The question: how do expression indexes go stale, what changed in 3.53, and what should an application that uses them do?

Background: the determinism contract

An index on an expression, or on a VIRTUAL generated column, stores computed keys. SQLite's documentation says index expressions may call only functions whose output is completely determined by their inputs. Random functions are excluded, and so is sqlite_version(), because it can change over the life of the database file. Application-defined functions count as non-deterministic unless registered with the deterministic flag.

The weakness is that deterministic means "same output for the same input in this environment". The stale-index page lists the ways the environment changes: the C math library behind functions such as sin() (the C standard only requires results to be close), an ICU upgrade altering lower() in corner cases, SQLite bug fixes, toggling PRAGMA case_sensitive_like for an index that uses LIKE, and changed application-defined functions.

What happened in 3.52 and 3.53

The most cited case is text-to-float conversion. According to SQLite's page, changes to that logic in versions 3.43 and 3.52 moved some values by one ULP. Implicit coercion can trigger it with no explicit CAST; for example doc->>'mtime' on JSON can differ by one ULP from the stored index key.

SQLite 3.52.0 was withdrawn on March 13, 2026, with 3.51.3 released in its place. The project's news page says some new features "are not 100% compatible with prior releases", and that 3.52 is fully compatible unless a database has indexes on expressions, or on VIRTUAL computed columns, that yield floating-point values derived from text or JSONB. On April 9 it shipped 3.53.0 as a re-release of 3.52.0 with extra enhancements for stale expression indexes. A forum post summarized in search results describes the fix as three parts; I confirmed each on the documentation pages below.

How the fix works

1. Self-healing. When a DELETE or UPDATE touches a row that has a stale entry, SQLite now "fixes the index" rather than raising an error. Before 3.53.0 that situation produced an error, and PRAGMA integrity_check reported errors.
2. Honest reporting. PRAGMA integrity_check still reports stale entries. For small floating-point drift the message reads like "index NAME stores an imprecise floating-point value for row N" instead of declaring corruption.
3. Bulk repair. REINDEX EXPRESSIONS; rebuilds only expression indexes and leaves others alone. The CLI also gains .indexes --expr to list them.

The same release adds ALTER TABLE ... ALTER COLUMN ... SET NOT NULL and DROP NOT NULL, and the notes list CHECK constraint changes too. The page notes that SET NOT NULL replaces at least one existing NOT NULL constraint rather than adding a redundant one. Separately, 3.53.0 changes the default float-to-text rounding from 15 to 17 significant digits (adjustable via SQLITE_DBCONFIG_FP_DIGITS), which may alter output text in applications that print floats.

Deep analysis: why self-heal instead of fail

An index entry that is off by one ULP matters only for rows exactly at a range boundary, and SQLite's own page says the developers received no corruption report after the 2023 conversion change. Failing a write on such a row would turn an invisible discrepancy into an outage. Fixing the entry lazily, during a write that already touches the row, costs almost nothing. The trade-off is that reads between the upgrade and the repair can use slightly imprecise keys, and integrity_check remains the way to see it.

A separate fix is relevant to any WAL user: 3.53.0 fixes a WAL-reset corruption bug. The WAL documentation says it is likely present in all versions from 3.7.0 (2010-07-21) through 3.51.2 (2026-01-09), affects only WAL-mode databases with two or more connections to the same file, requires them to write or checkpoint at the same instant, and is "unlikely to occur in common use." It is fixed in 3.51.3 (2026-03-13), with backports for some earlier releases, named as 3.44.6 and 3.50.7. This is a data-race bug fix, and I mention it as a version-selection fact, not as a security matter.

Comparison: ways to index a computed value

Approach	Stored value	Stale risk after upgrade
Index on expression	Key computed at write time	Possible if the function's result changes
Index on VIRTUAL generated column	Same: key computed at write time	Same exposure, per the docs
STORED generated column plus index	Value stored in the row	Not recomputed for lookups; the docs recommend this to avoid the issue
Plain column maintained by the app	Application-computed	You control when it is recomputed

Practical example

Untested; assumes SQLite 3.53.0 or later and a database with an expression index on a JSON-derived number.

-- Schema using an expression index (the risky pattern)
CREATE TABLE docs (id INTEGER PRIMARY KEY, body TEXT);
CREATE INDEX docs_mtime ON docs (body->>'mtime');

-- After upgrading SQLite or its math/ICU libraries:
PRAGMA integrity_check;     -- stale entries reported, as imprecise values for small drift
REINDEX EXPRESSIONS;        -- rebuild only expression indexes (3.53.0+)

-- Lower-risk alternative: store the computed value
CREATE TABLE docs2 (
  id INTEGER PRIMARY KEY,
  body TEXT,
  mtime REAL GENERATED ALWAYS AS (body->>'mtime') STORED
);
CREATE INDEX docs2_mtime ON docs2 (mtime);

Measurement plan instead of invented numbers: take a copy of a production database, run PRAGMA integrity_check with the old library, open it with the new one and rerun it, then time REINDEX EXPRESSIONS and compare database size and write time for the STORED-column variant.

Limitations and failure modes

• Self-healing only triggers when a write touches the stale row; unread, unmodified entries stay stale until reindexed.
• Files written by 3.52+ with such indexes may not interoperate with older SQLite, per the news page. Mixed-version fleets (for example a mobile app and a server) need care.
• Application-defined functions that change behavior remain your responsibility; run REINDEX EXPRESSIONS after changing one.
• The release history lists a 3.54.0 change (dated 2026-10-09) not to modify an expression index when the indexed value is unchanged; I saw it only in a search summary and did not read its notes.

Engineering recommendations

List your expression indexes (.indexes --expr in the 3.53 shell), and for each ask whether a STORED generated column would do. Pin SQLite and its extension libraries together in releases, run PRAGMA integrity_check in CI after library upgrades, and avoid running mixed old and new SQLite versions against the same file. If you use WAL with several writers on a version before 3.51.3 and have no backport, upgrade.

Conclusion

Expression indexes quietly depend on library behavior staying stable, and SQLite 3.53 responds with repair-on-write, accurate diagnostics and a one-line rebuild rather than hard failures. The next step: run PRAGMA integrity_check on a copy of your largest production database today, and add the command to your upgrade checklist.

Sources

Information as of October 28, 2026.

• SQLite, Stale Expression Indexes: sqlite.org/staleexpridx.html
• SQLite release 3.53.0 notes (2026-04-09): sqlite.org/releaselog/3_53_0.html
• SQLite news (3.52.0 withdrawal, 3.53.0 entry): sqlite.org/draft/news.html
• SQLite, REINDEX: sqlite.org/lang_reindex.html
• SQLite, Indexes On Expressions: sqlite.org/expridx.html
• SQLite, ALTER TABLE: sqlite.org/lang_altertable.html
• SQLite, Write-Ahead Logging (WAL-reset bug section): sqlite.org/wal.html

I read these pages but did not run SQLite 3.53 or reproduce staleness; the SQL is untested, and the 3.54.0 note comes only from a search summary.