A testcase causing Assertion `memIsValid(&aMem[pOp->p1])' failed

(1) By Jingzhou Fu (fuboat) on 2022-01-02 08:11:39 [source]

System Information:

compile-time options: CC=clang-12 ./configure --enable-debug
sqlite3_sourceid: 2022-01-01 22:55:31 e199a851e316bd471bfc54204b8c250d3ae93b829261214158a2c74acad4093e
output: sqlite3.c:87667: sqlite3VdbeExec: Assertion `memIsValid(&aMem[pOp->p1])' failed.

PoC:

PRAGMA foreign_keys = 1;
CREATE TABLE b(
  v1 PRIMARY KEY,
  t2 REFERENCES b DEFERRABLE INITIALLY DEFERRED
);
CREATE TABLE temp_v1(
      temp_t1 PRIMARY KEY REFERENCES b ON DELETE CASCADE,
      real_t1
    );
DROP TABLE b;
CREATE TABLE b(x PRIMARY KEY, v1);
INSERT INTO b VALUES(3, 1);
DELETE FROM b WHERE v1 = 1;

(2.1) By Richard Hipp (drh) on 2022-01-06 12:51:44 edited from 2.0 in reply to 1 [link] [source]

Thanks for the bug report.

This is a real, long-standing bug. The problem goes all the way back to 2009, when foreign key constraints were first introduced into SQLite version 3.6.19. A simplified test case is as follows:

CREATE TABLE t1(a PRIMARY KEY,b);
CREATE TABLE t2(c PRIMARY KEY REFERENCES t1 ON DELETE CASCADE,d);
PRAGMA foreign_keys = ON;
DROP TABLE t1;
CREATE TABLE t1(x PRIMARY KEY, y);
INSERT INTO t1 VALUES(1,2);
DELETE FROM t1;

Here is what was happening: The "ON DELETE CASCADE" in the definition of table t2 generates a virtual trigger that performs the cascading delete. This virtual trigger is cached in the schema definition for the t2 table. But when the definition of table t1 changes (due to the DROP and subsequent CREATE TABLE) the cache of the virtual cascade-trigger was not being cleared. With the new definition of table t1, the older, cached trigger implementation is no longer valid.

The fix, which is now on trunk, is to reset the cache of triggers used for cascading deletes and updates after every schema change.