SQLite User Forum

Assertion failure in cursor
Login

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.