A testcase causing Assertion failed in sqlite3WhereEnd

(1) By Jingzhou Fu (fuboat) on 2022-03-21 08:30:33 [source]

System Information:

compile-time options: CC=clang-12 ./configure --enable-debug
sqlite_source_id: 2022-03-19 15:19:35 d8b65a2dab97392ff81bcc33ff707b4e626a10d84a258c6452e45f90cd2c7f45
output: sqlite3: sqlite3.c:158402: sqlite3WhereEnd: Assertion `(pLoop->wsFlags & WHERE_IDX_ONLY)==0 || x>=0 || pOp->opcode==OP_Offset || pWInfo->eOnePass' failed.

PoC:

CREATE TABLE t1(a);
CREATE VIEW x(a, c1) AS 
SELECT CAST(0.0 AS NUMERIC), COUNT(*) OVER () FROM t1;
CREATE TRIGGER t2a INSTEAD OF UPDATE ON x BEGIN
  SELECT 1;
END;
UPDATE x SET a=t1.a FROM t1 WHERE x.a=t1.a;

(2) By Richard Hipp (drh) on 2022-03-21 13:55:39 in reply to 1 [link] [source]

This is an error in the assert() statement that is failing. Since assert() is used for testing and debugging only and is disabled in production builds, this problem does not affect production builds.

The error occurs in the logic that attempt to translate references to tables into a reference to the corresponding index. That logic is an attempt to avoid unnecessary seeks on the table. (This is part of the covering index optimization.) The assert() tries to verify that required translations really have occurred. A table-to-index translation is required if the table cursor has been optimized out.

The assert() in question was fragile, and has caused problem before. It has now been replaced by a new implementation that is more code, but also more direct and hopefully less fragile.