SQLite 3.53's self-healing index only repairs the rows you write to

I asked a database for the row where a computed bucket equalled 500. It gave me row 309. The correct row, using the same formula, was 308. No error, no warning, just a wrong answer served from an index that no longer matched the function that built it.

This is what SQLite calls a stale expression index, and it's been a known but rarely discussed failure mode for years. SQLite 3.53.0, released in April 2026, added a "self-healing" mechanism aimed at exactly this problem. I built one on purpose, broke it on an old SQLite build, then fixed it on the new one, to see what the fix actually does and does not cover.

How an expression index goes stale

An expression index stores the computed value of an expression, not the raw column. CREATE INDEX idx ON docs(lower(email)) stores lowercased emails in the index tree. If the function behind that expression ever returns a different answer for the same input — a bug fix in a custom function, a library upgrade, or (SQLite's own documented case) a one-ULP shift in floating point conversion between versions — the stored index entries no longer match what the expression would compute today. The row is still there. The index just points at the wrong place, or the wrong bucket doesn't point anywhere.

I couldn't easily reproduce SQLite's own internal float-conversion trigger without hunting for the exact input that shifts by one ULP between two specific point releases, so I reproduced the other documented cause instead: a custom SQL function whose output changes. I registered a scalar function classify(x) that returns floor(x*100), built a table and an expression index on classify(x), then reopened the same database file with a "fixed" version of the function that returns floor(x*100) + 1 — standing in for an app-level bug fix that nobody thought to pair with a REINDEX.

static void classify_func(sqlite3_context *ctx, int argc, sqlite3_value **argv) {
    double x = sqlite3_value_double(argv[0]);
    long bucket = (long)(x * 100.0);
    if (g_variant == 1) bucket += 1;   /* the "fixed" version */
    sqlite3_result_int64(ctx, bucket);
}

Both versions are registered SQLITE_DETERMINISTIC, which is exactly what SQLite's docs warn against doing carelessly: it lets the engine trust the index without re-checking.

I built and ran two SQLite builds compiled from the official amalgamation, so I could isolate the version as the only variable: 3.51.0 (before self-healing existed) and 3.53.4, the latest release as of this test.

The wrong answer, on the old build

With 1,000 rows and the index built under the old function, I reopened the database with the "fixed" function and asked for classify(x) = 500:

indexed (INDEXED BY idx_classify) ids for classify(x)=500: (id=309 x=5.004000)
full scan (NOT INDEXED) ids for classify(x)=500: (id=308 x=4.991000)

Two different rows, same query, same data, same function. The index path used the b-tree key that was written when the old function built it; the scan path recomputed the expression fresh. Nothing in the indexed query's result told me it was wrong.

Then I tried writing to the affected rows on SQLite 3.51.0:

update_rc=11 err=database disk image is malformed (range 1..50)

11 is SQLITE_CORRUPT. That is the honest, current behaviour of every SQLite release before 3.53: a stale expression index can turn an ordinary UPDATE into a corruption error, on a database that isn't actually corrupt in any other sense.

What 3.53.4 does instead

Same database, same broken function pairing, but opened with the 3.53.4 build. The pre-write query showed the identical wrong answer (309 instead of 308) — self-healing does not run on reads, only on writes. Then I ran an UPDATE touching id 300 through 320, a range that happens to include both 308 and 309:

update_rc=0 err=none (range 300..320) time=0.0009

No error. Querying classify(x) = 500 again afterwards, both the indexed path and the full scan now agreed on row 308. That part of SQLite's claim held up: "no errors are raised, the application never knows that something was ever amiss" is literally what I observed.

What I didn't expect is how narrow the fix is. Before that UPDATE, an integrity check with the row limit raised to 5,000 counted every stale entry in the table:

Self-healing repairs exactly the rows a write statement touches, and nothing else. The other 979 rows stayed just as wrong as they were on 3.51.0 — they simply hadn't been written to. A read-heavy table with a stale expression index could sit there returning wrong answers indefinitely on 3.53, with no error and no write ever forcing a fix.

REINDEX EXPRESSIONS is the actual fix, and it's fast

REINDEX EXPRESSIONS is new in 3.53 too: it rebuilds only expression indexes, skipping ordinary column indexes on the same table. On 3.51.0 the statement doesn't parse as a special form at all:

reindex_expr_rc=1 err=unable to identify the object to be reindexed

SQLite 3.51 reads EXPRESSIONS as the name of an index or table to reindex, finds nothing by that name, and fails with that message. On 3.53.4 it's a real command, and it healed all 979 remaining rows in my 1,000-row table instantly.

To see whether "only touches expression indexes" is actually worth anything, I built a 500,000-row table with four indexes — one expression index on classify(x), three ordinary ones on other columns — and timed REINDEX EXPRESSIONS against a full REINDEX of the table, fastest of three runs each:

About five times faster, roughly proportional to touching one index instead of four. If a table has several plain indexes alongside one suspect expression index, this is the difference between a maintenance job that runs in the background and one that locks the whole table for a full rebuild.

What self-healing costs you on every write

Since 3.53 quietly rewrites stale entries as it goes, I wanted to know if that costs anything on writes that don't need it. I built two 200,000-row tables — one where the index was already stale relative to the current function, one where it matched from the start — then ran the identical single-statement UPDATE touching every row, fastest of three runs:

Healing added roughly 46% to that statement's time, or about 0.4 microseconds per row. That's a real cost, not a rounding error, though it's a one-time tax paid the first time each row is touched after the function changes — after that the row is clean and stays clean.

Under concurrent writers, nothing new happens

I opened five separate connections against the same stale database, each running an UPDATE on an overlapping range of ids, with no busy_timeout set:

update_rc=5 err=database is locked (range 801..2000)
update_rc=5 err=database is locked (range 1601..3000)
update_rc=5 err=database is locked (range 2401..4000)
update_rc=0 err=none (range 1..1000)
update_rc=0 err=none (range 3201..5000)

Three of five got SQLITE_BUSY immediately, two succeeded. That's just SQLite's ordinary single-writer lock, the same thing that happens with any concurrent write and no timeout configured. Self-healing didn't introduce a new failure mode here, and the two successful writes healed exactly the rows in their ranges — the remaining stale count matched what I'd expect from simple arithmetic on which ranges got through. I didn't find anything specific to self-healing under contention worth a longer write-up.

What I got wrong on the way

My first pass at the "wrong answer" query used bucket 500 arbitrarily without checking my data's range, and separately I first tried bucket 50 — which was below the minimum value my generated x column could ever produce, so both the indexed and scan queries correctly returned zero rows. That looked like "no staleness detected" and briefly convinced me my repro wasn't working, before I checked the actual range of x*100 in my dataset and picked a bucket inside it.

The second mistake was quieter. I wrote a separate small program to run PRAGMA integrity_check(5000) for a precise count, and it reported zero problems on a database I already knew was full of them. I'd forgotten to register the classify function on that connection before running the check. SQLite apparently treats an index expression it can't evaluate as something to skip rather than something to flag, so the check silently passed over the one index I cared about. Once I registered the function on that connection too, the count came back as 1,000, matching everywhere else.

Correction (2026-09-11): The paragraph above is wrong, a reader's reproduction on 3.53.4 caught it. With classify unregistered, PRAGMA integrity_check does not skip the index, sqlite3_prepare_v2 fails outright with unknown function: classify(). My separate checker program never checked that return code, so the check never actually ran, and the zero problems I reported came from nothing being counted, not from SQLite skipping anything. A safeguard has to confirm the check actually executed, not just trust a clean result.

Run it yourself

This needs a C compiler and the official amalgamation source, no Docker required.

mkdir sqlite-test && cd sqlite-test
curl -sSL -o new.zip https://sqlite.org/2026/sqlite-amalgamation-3530400.zip
curl -sSL -o old.zip https://sqlite.org/2025/sqlite-amalgamation-3510000.zip
unzip -q new.zip && unzip -q old.zip

Save this as harness.c (trimmed to the create/query/update paths used above):

#include "sqlite3.h"
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

static int g_variant = 0;

static void classify_func(sqlite3_context *ctx, int argc, sqlite3_value **argv) {
    double x = sqlite3_value_double(argv[0]);
    long bucket = (long)(x * 100.0);
    if (g_variant == 1) bucket += 1;
    sqlite3_result_int64(ctx, bucket);
}

int main(int argc, char **argv) {
    const char *dbpath = argv[1], *mode = argv[2];
    g_variant = atoi(argv[3]);
    int n = argc > 4 ? atoi(argv[4]) : 1000;
    sqlite3 *db; sqlite3_open(dbpath, &db);
    sqlite3_create_function(db, "classify", 1, SQLITE_UTF8 | SQLITE_DETERMINISTIC,
                             NULL, classify_func, NULL, NULL);
    char *err = NULL;
    if (!strcmp(mode, "create")) {
        sqlite3_exec(db, "CREATE TABLE t(id INTEGER PRIMARY KEY, x REAL)", 0, 0, &err);
        sqlite3_exec(db, "CREATE INDEX idx_classify ON t(classify(x))", 0, 0, &err);
        sqlite3_exec(db, "BEGIN", 0, 0, &err);
        sqlite3_stmt *stmt;
        sqlite3_prepare_v2(db, "INSERT INTO t(x) VALUES (?)", -1, &stmt, 0);
        for (int i = 0; i < n; i++) {
            sqlite3_bind_double(stmt, 1, 1.0 + i * 0.013);
            sqlite3_step(stmt); sqlite3_reset(stmt);
        }
        sqlite3_finalize(stmt);
        sqlite3_exec(db, "COMMIT", 0, 0, &err);
    } else if (!strcmp(mode, "query")) {
        int bucket = n;
        char sql[256]; sqlite3_stmt *stmt;
        snprintf(sql, sizeof sql, "SELECT id,x FROM t INDEXED BY idx_classify WHERE classify(x)=%d", bucket);
        sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
        while (sqlite3_step(stmt) == SQLITE_ROW)
            printf("indexed: id=%d x=%f\n", sqlite3_column_int(stmt,0), sqlite3_column_double(stmt,1));
        sqlite3_finalize(stmt);
        snprintf(sql, sizeof sql, "SELECT id,x FROM t NOT INDEXED WHERE classify(x)=%d", bucket);
        sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
        while (sqlite3_step(stmt) == SQLITE_ROW)
            printf("scan:    id=%d x=%f\n", sqlite3_column_int(stmt,0), sqlite3_column_double(stmt,1));
        sqlite3_finalize(stmt);
    } else if (!strcmp(mode, "update")) {
        int lo = n, hi = argc > 5 ? atoi(argv[5]) : n;
        char sql[256];
        snprintf(sql, sizeof sql, "UPDATE t SET x=x WHERE id BETWEEN %d AND %d", lo, hi);
        int rc = sqlite3_exec(db, sql, 0, 0, &err);
        printf("update_rc=%d err=%s\n", rc, err ? err : "none");
    }
    sqlite3_close(db);
}

Build both versions and reproduce the wrong answer, then the fix:

gcc -O2 -I sqlite-amalgamation-3510000 harness.c sqlite-amalgamation-3510000/sqlite3.c \
    -lpthread -ldl -lm -o harness_old
gcc -O2 -I sqlite-amalgamation-3530400 harness.c sqlite-amalgamation-3530400/sqlite3.c \
    -lpthread -ldl -lm -o harness_new

rm -f test.db
./harness_old test.db create 0 1000      # build the index with the "buggy" function
./harness_old test.db query 1 500        # variant 1 = the "fixed" function: wrong answer
./harness_old test.db update 1 1 50      # SQLITE_CORRUPT on the old build

rm -f test.db
./harness_new test.db create 0 1000
./harness_new test.db update 1 300 320   # succeeds silently on 3.53.4
./harness_new test.db query 1 500        # now correct, because 308/309 got touched

What to do about it

If you have expression indexes on top of application-defined functions or extensions, treat a fix to that function the same way you'd treat a schema migration: run REINDEX EXPRESSIONS right after deploying it. Don't rely on self-healing to catch up on its own — it only touches rows your application happens to write to, and a read-mostly table can carry silently wrong answers for as long as you let it.

Run PRAGMA integrity_check on any database where you suspect this, but register every custom function the schema depends on before you do — mine reported zero problems the one time I forgot, on a database with a thousand stale rows. And if you're still on a version before 3.53, know that the current failure mode for a stale expression index isn't a wrong answer, it's SQLITE_CORRUPT on write.