SQLite Forum

Assertion failure in NATURAL JOIN
Login

Assertion failure in NATURAL JOIN

(1) By Song Liu (songliu) on 2023-03-25 17:18:32 [source]

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

CREATE TABLE v0(c0, PRIMARY KEY(c0 COLLATE nocase), UNIQUE(c0, c0));
INSERT INTO v0 VALUES ('');
CREATE VIEW v1 AS SELECT (SELECT c0) c0 FROM v0;
SELECT 1 FROM v0 NATURAL JOIN v1

Here are the outputs:

sqlite3: sqlite3.c:91259: sqlite3VdbeExec: Assertion `memIsValid(&aMem[pOp->p1])' failed.
[1]    1264195 abort      ./sqlite3 < poc

Here is the result of bisecting:

 11 BAD     2023-03-01 20:23:46 198b3e33dcfd74c7
 12 BAD     2023-03-01 15:21:53 e72661eb680ea707 CURRENT

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 v0(c0, PRIMARY KEY(c0 COLLATE nocase), UNIQUE(c0, c0));
INSERT INTO v0 VALUES ('');
CREATE VIEW v1 AS SELECT (SELECT c0) c0 FROM v0;

.testctrl optimizations 0x00000001;
.print '-- disable optimization of SQLITE_QueryFlattener'
SELECT 1 FROM v0 NATURAL JOIN v1

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 Song Liu (songliu) on 2023-03-25 19:59:45 in reply to 1 [link] [source]

It seems the bug is fixed at commit c104e5c6eeb89575. Thanks for your work!