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!