Assertion failure in Cursor Hints
(1.1) By Song Liu (songliu) on 2023-03-25 01:45:19 edited from 1.0 [source]
I found an assertion failure while SQLite (latest, 74d14900afd10d02) executes the following queries.
CREATE TABLE v1 (c1, PRIMARY KEY( c1 )) WITHOUT ROWID; CREATE VIEW v2 AS SELECT 0 FROM v1 WHERE c1 IS '' OR c1 > ''; CREATE VIEW v3 AS SELECT 0 FROM v2 JOIN (v2 RIGHT JOIN v1); CREATE VIEW v4 AS SELECT 0 FROM v3, v3; SELECT * FROM v3 JOIN v3 AS a0, v4 AS a1, v4 AS a2, v3 AS a3, v3 AS a4, v4 AS a5 ORDER BY 1
Here are the outputs:
sqlite3: sqlite3.c:94938: sqlite3VdbeExec: Assertion `pOp->opcode==OP_SeekGE || pOp->opcode==OP_SeekLE' failed.  1919893 abort ./sqlite3 < poc
Note that we have a post (c2b7dcdb31) to discuss cursor hints before, and here is another assertion failure about cursor hint.
assert( pOp->opcode==OP_SeekGE || pOp->opcode==OP_SeekLE ); <----- current reporting case triggers this failure assert( pOp.opcode==OP_IdxLT || pOp.opcode==OP_IdxGT ); <----- the case discussed by forum post c2b7dcdb31
Here is the result of bisecting:
13 BAD 2022-09-01 10:29:02 609fbb94b8f01d67 CURRENT 10 GOOD 2022-08-31 15:04:42 5007742886bd20de
The assertion failure may be caused by the optimizations. SQLite crashes by default with all optimizations enabled. If I disable the SQLITE_QueryFlattener optimization, SQLite works well.
CREATE TABLE v1 (c1, PRIMARY KEY( c1 )) WITHOUT ROWID; CREATE VIEW v2 AS SELECT 0 FROM v1 WHERE c1 IS '' OR c1 > ''; CREATE VIEW v3 AS SELECT 0 FROM v2 JOIN (v2 RIGHT JOIN v1); CREATE VIEW v4 AS SELECT 0 FROM v3, v3; .testctrl optimizations 0x00000001; .print '-- disable optimization of SQLITE_QueryFlattener' SELECT * FROM v3 JOIN v3 AS a0, v4 AS a1, v4 AS a2, v3 AS a3, v3 AS a4, v4 AS a5 ORDER BY 1
My compilation flags:
export CFLAGS="-g -O0 -DSQLITE_DEBUG -DSQLITE_ENABLE_TREETRACE -DSQLITE_ENABLE_WHERETRACE -DSQLITE_ENABLE_CURSOR_HINTS -DSQLITE_COUNTOFVIEW_OPTIMIZATION -DSQLITE_ENABLE_STAT4"
(2) By Richard Hipp (drh) on 2023-03-25 02:09:07 in reply to 1.1 [link] [source]
This was fixed by check-in 221fdcec964f8317. That check-in was not previously cherry-picked to branch-3.41, since it is not a bug that can cause any problems nor that any real user will ever hit, and we strive to keep changes on branch-3.41 to a minimum. But since you are testing on that branch, I went ahead and cherry-picked it now.
(3) By Song Liu (songliu) on 2023-03-25 02:12:34 in reply to 2 [link] [source]
Sorry for the duplicated bug. I will test the trunk branch only.