Assertion failure in sqlite3BtreeInsert function

(1.1) By Song Liu (songliu) on 2023-04-22 04:10:27 edited from 1.0 [source]

I found an assertion failure while SQLite (latest, 7809e7ce6a70657b) executes the following queries.

CREATE TABLE v0 (c1,T PRIMARY KEY);
INSERT INTO v0 VALUES(0,'');
CREATE INDEX i ON v0(0);
CREATE INDEX i0 ON v0(c1,c1);
CREATE TABLE a (b,sqlite_stat1 UNIQUE,PRIMARY KEY(b)) WITHOUT ROWID;
PRAGMA writable_schema=1;
UPDATE sqlite_master SET rootpage=5;
CREATE VIRTUAL TABLE v USING e;
UPDATE a SET sqlite_stat1=sqlite_stat1;

Here are the outputs:

Runtime error near line 8: no such module: e
sqlite3: sqlite3.c:77940: int sqlite3BtreeInsert(BtCursor *, const BtreePayload *, int, int): Assertion `pCur->eState==CURSOR_VALID || (pCur->eState==CURSOR_INVALID && loc)' failed.

My compilation flags:

export CFLAGS="-g -DSQLITE_DEBUG
            -DSQLITE_ENABLE_TREETRACE
            -DSQLITE_ENABLE_WHERETRACE
            -DSQLITE_ENABLE_CURSOR_HINTS
            -DSQLITE_COUNTOFVIEW_OPTIMIZATION
            -DSQLITE_ENABLE_STAT4"
./configure --enable-all --enable-debug --disable-shared && make

(2.1) By Larry Brasfield (larrybr) on 2023-04-23 14:05:24 edited from 2.0 in reply to 1.1 [link] [source]

(Edits for full disclosure.)

With recent changes (of 2023-04-22) that input sequence no longer hits an assert.
Instead, it reports an error, "database disk image is malformed".

The above could only happen when the new CLI invocation option, --unsafe-testing, is provided. Without that option, writes to sqlite_master (aka "sqlite_schema") are blocked.

When I see "UPDATE sqlite_master", I find myself thinking a new assert(X) macro may be required.
Its long name would be assert_true_or_db_was_dorked(X).
The "was dorked" condition would be set whenever the schema table is written directly by DML.

(3) By Richard Hipp (drh) on 2023-04-23 15:42:48 in reply to 2.1 [link] [source]

There is a common idiom in the SQLite source code:

assert( <some invariant> || CORRUPT_DB );

What this means is that the invariant holds if and only if the database file is not corrupt. This is a very powerful tool to help document and reason about the code.

The CORRUPT_DB term is a macro that evaluates to a global variable named sqlite3Config.neverCorrupt. That variable is false by default, because you never really know if the database file is corrupt or not. In fact, you should always assume that the database file is corrupt. But that global variable can be set to true for testing purposes via a particular sqlite3_test_control() call. The neverCorrupt global is set to true by default in TH3, and there are special commands to turn it back off in those tests that deliberately operate on corrupt database files. Because TH3 provides such rich coverage, we have high confidence that the invariants identified by these kinds of assert() statements really are true.

The neverCorrupt global is only ever used inside of assert() statements. The value of that variable has no effect on normal operation.

For the issue reported by the initial report of this thread, an invariant was reported as being true for all database files. But Song Liu's fuzzer found a case where the invariant is not true for a corrupt database. So the fix (check-in cd485b302c54aef0) was to add the "|| CORRUPT_DB" term to the assert() statement that proves the invariant, thus showing that the invariant only holds for well-formed databases.