SQLite User Forum

Assertion failure in RIGHT JOIN
Login

Assertion failure in RIGHT JOIN

(1) By Song Liu (songliu) on 2023-03-24 18:20:20 [link] [source]

I found an assertion failure while SQLite (latest, 98d30400e4721b1d) executes the following queries.

CREATE TABLE v1 ( c1 INTEGER PRIMARY KEY ) WITHOUT ROWID; 
CREATE VIEW v2 AS SELECT 0 FROM v1 WHERE c1 GLOB 'a' OR c1 == 1; 
SELECT 2 FROM v2 RIGHT JOIN v2; 

Here are the outputs:

sqlite3: sqlite3.c:95129: int sqlite3VdbeExec(Vdbe *): Assertion `pOp[1].opcode==OP_IdxLT || pOp[1].opcode==OP_IdxGT' failed.
[1]    824964 abort      ./sqlite3 < poc4

Here is the result of bisecting:

 13 BAD     2022-04-21 19:38:17 f766dff012af0ea3 CURRENT
 12 GOOD    2022-04-21 19:20:38 b870d2a2fa54ddab

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 INTEGER PRIMARY KEY ) WITHOUT ROWID; 
CREATE VIEW v2 AS SELECT 0 FROM v1 WHERE c1 GLOB 'a' OR c1 == 1; 

.testctrl optimizations 0x00000001;
.print '-- disable optimization of SQLITE_QueryFlattener'
SELECT 2 FROM v2 RIGHT JOIN v2; 

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-24 19:22:51 in reply to 1 [link] [source]

The SQLITE_ENABLE_CURSOR_HINTS compile-time option is undocumented and unsupported - or at least unsupported for public users. We do have one private customer who uses CURSOR_HINTS, but they do not use WITHOUT ROWID tables, nor RIGHT JOINs, and so this problem does not affect them.

Cursor-hints is a mechanism by which the byte-code engine tells the b-tree layer that a particular cursor will be used only for direct look-up and never for a scan. The built-in b-tree layer in SQLite never uses this hint. The hint is only used by developers who substitute their on private b-tree layer in place of the one that is built into SQLite. So enabling cursor-hints in a standard build does nothing more than activate a few extra assert() statements that ensure that cursor-hints are working correctly.

You discovered a mechanism by which cursor-hints did not work correctly on a WITHOUT ROWID table which is used in a RIGHT JOIN. But since literally nobody in the world ever does this, the error is without consequence.

The problem is fixed on trunk.

(3) By Song Liu (songliu) on 2023-03-24 20:07:39 in reply to 2 [source]

Thanks for your detailed explanation! Nice to see SQLite getting better!