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.  2709893 abort ./sqlite3 < poc
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!