SQLite Forum

Assertion Failure in constructAutomaticIndex()
## Testcase

/* 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!