SQLite Forum

Assertion Failure in constructAutomaticIndex()
Login

Assertion Failure in constructAutomaticIndex()

(1.1) By Yoda (lsy641) on 2021-08-24 14:43:22 edited from 1.0 [link] [source]

Testcase

CREATE TABLE tab(id INTEGER,eventtype INTEGER);
/* turn off the optimization SQLITE_QueryFlattener */
.testctrl optimizations 0x00000001;
SELECT tab.id IN(SELECT sum(tab.id) OVER( ORDER BY tab.eventtype + tab.id) FROM tab JOIN (SELECT tab.eventtype,tab.id,tab.id,tab.id FROM tab) ra6 ON tab.id=tab.eventtype AND ra6.eventtype='klmnopqrs'),(SELECT ra4.id FROM tab AS ra4 WHERE likely(ra4.eventtype)>likely(ra4.id)) FROM tab;
/* Assertion failure: void constructAutomaticIndex(Parse *, WhereClause *, SrcItem *, Bitmask, WhereLevel *): Assertion `pColl!=0 || pParse->nErr>0' failed. */

Path

(in constructAutomaticIndex) pColl = sqlite3ExprCompareCollSeq(pParse, pX);

==> (in sqlite3ExprCompareCollSeq) return sqlite3BinaryCompareCollSeq(pParse, p->pLeft, p->pRight);

==> (in sqlite3BinaryCompareCollSeq) pColl = sqlite3ExprCollSeq(pParse, pLeft);

==> (in sqlite3ExprCollSeq) pColl = sqlite3FindCollSeq(db, ENC(db), zColl, 0);

==> (in sqlite3FindCollSeq) pColl = findCollSeqEntry(db, zName, create);

==> (in findCollSeqEntry) pColl = sqlite3HashFind(&db->aCollSeq, zName);

==> (in findCollSeqEntry) return pColl;

==> (in sqlite3FindCollSeq) return pColl;

==> (in sqlite3ExprCollSeq) return pColl;

==> (in sqlite3BinaryCompareCollSeq) return pColl;

==> (in sqlite3ExprCompareCollSeq) return sqlite3BinaryCompareCollSeq(pParse, p->pLeft, p->pRight);

Then we get a pointer pColl that points to NULL but pParse->nErr is 0.

More Information

It's interesting that the issue seems to be related to the name of the second column of t0, which will not be triggered when the length of the eventtype is changed to be less than 9. This testcase is really hard to simplify, when we slightly simplify a part, the problem can no longer occur.

We initially analyzed the cause of the problem and concluded that there might be a problem with the automatic index constructing process for WhereItem ra6.eventtype='klmnopqrs'.

Currently, we do not know the exact cause of the problem, nor is it clear if there is a testcase that triggers the problem when no optimization is off.

We found this issue when we turned off optimization SQLITE_QueryFlattener via .testctrl command and turned on DEBUG. We also tried adjusting the statements after turning on the optimization, but failed to trigger the problem, so this should be a low priority issue that does not affect reality. Nevertheless, please try to find the cause of the problem when you have time.

Thank you a lot for reading this.

Your reply will be appreciated!

(2) By Richard Hipp (drh) on 2021-08-24 14:56:26 in reply to 1.1 [source]

Thanks for the report.

I cannot reproduce the problem as you describe it, however I did get a valgrind error on trunk using your repro script. The problem I see does not appear to be in any official release. Bisecting shows that the problem originates on this check-in: 832ac4c1ee384be0.

Does that agree with what you are seeing?

(3) By Yoda (lsy641) on 2021-08-24 15:22:15 in reply to 2 [link] [source]

Sorry, I forgot to say which version I'm on.

I am on the version of recent trunk, which is check-in 1e2dcc2d.

I also think it should be introduced recently, because we didn't find this problem on the stable version.

(4) By Yoda (lsy641) on 2021-08-25 10:27:04 in reply to 2 [link] [source]

It looks like the problem has been fixed(check-in c7f0813cabf9d8ab), thanks for the quick fix.