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.
[1] 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[1].opcode==OP_IdxLT || pOp[1].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.