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!