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]

## Testcase

```sqlite
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

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](src:/timeline?c=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]

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

I am on the version of recent trunk, which is [check-in 1e2dcc2d](https://sqlite.org/src/timeline?c=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]

It looks like the problem has been fixed([check-in c7f0813cabf9d8ab](https://sqlite.org/src/info/c7f0813cabf9d8ab)), thanks for the quick fix.