SQLite Forum

Assertion failure in ANALYZE for STAT4
Login

Assertion failure in ANALYZE for STAT4

(1) By Song Liu (songliu) on 2023-03-25 16:22:04 [source]

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

CREATE TABLE v0 ( c1 PRIMARY KEY ) WITHOUT ROWID; 
CREATE INDEX i2 ON v0 ( c1|c1, c1||c1, c1, c1 ); 
INSERT INTO v0 ( c1 ) VALUES ( 0 ); 
ANALYZE;
ANALYZE;

Here are the outputs:

sqlite3: sqlite3.c:92068: sqlite3VdbeExec: Assertion `pIn1!=pOut' failed.
[1]    2709893 abort      ./sqlite3 < poc

We discussed this problem before bc39e531e5, here is the patch 13c8c60bb6b4447b. It seems the patch is not complete and there is another case that can trigger this assertion failure.

Here is the result of bisecting:

 15 BAD     2015-09-04 18:03:45 1efece95ff8777b8
 16 BAD     2015-09-04 17:32:19 2131a5ca53f0e9b0 CURRENT

The assertion failure may be caused by the optimizations. SQLite crashes by default with all optimizations enabled. If I disable the SQLITE_Stat4 optimization, SQLite works well.

CREATE TABLE v0 ( c1 PRIMARY KEY ) WITHOUT ROWID; 
CREATE INDEX i2 ON v0 ( c1|c1, c1||c1, c1, c1 ); 
INSERT INTO v0 ( c1 ) VALUES ( 0 ); 
ANALYZE; 

.testctrl optimizations 0x00000800;
.print '-- disable optimization of SQLITE_Stat4'
ANALYZE; 

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 18:39:50 in reply to 1 [link] [source]

This is essentially the same problem reported by bc39e531e5. The prior fix was incomplete. A better fix is now on trunk and on branch-3.41.

The problem only arises when STAT4 is enabled and you try to ANALYZE a database that contains indexes on wonky expressions. The bisect reported in the original problem description took you to check-in 2131a5ca53f0e9b0 which was the check-in that added the ability to have an index on an expression.

(3) By Song Liu (songliu) on 2023-03-25 18:42:41 in reply to 2 [link] [source]

Thanks for your detailed explanation!