SQLite Forum

Timeline
Login

6 forum posts by user lsy641

2021-08-25
10:27 Reply: Assertion Failure in constructAutomaticIndex() (artifact: 88fba97bbd user: lsy641)

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

2021-08-24
15:22 Reply: Assertion Failure in constructAutomaticIndex() (artifact: e5c76b738e user: lsy641)

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.

14:43 Edit: Assertion Failure in constructAutomaticIndex() (artifact: 36be3d3a08 user: lsy641)

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!

13:56 Post: Assertion Failure in constructAutomaticIndex() (artifact: fad71a626d user: lsy641)

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.

Thanks you a lot for reading this.

Your reply will be appreciated!

2021-08-18
13:22 Edit: (Deleted) (artifact: 249753c7b6 user: lsy641)
Deleted
12:21 Post: (Deleted) (artifact: 3650e8e839 user: lsy641)

I am confused about how SQLite handles integer overflow.

For example, as far as I know, SQLite uses long long(-2^63-2^63-1) to store data of type INT.

So in this query:

SELECT 9223372036854775807+1;

the boundary value plus one almost becomes -9223372036854775808, but SQLite makes it a REAL type value to avoid overflow

So for this query:

SELECT 9223372036854775807 >= 9223372036854775807+1;

It's reasonable that the result is 0.

Presumably, SQLite developers tried to make the results more realistic, i.e., to make users feel as little as possible the limitations of the INT range.

But in this post, it does seem like the developers didn't consider the columns that use PRIMARY KEY, so the unbelievable behavior happens, which I think is a bug because the two additional SELECT statements not listed in the quoted post:

CREATE TABLE v0(c0 INTEGER PRIMARY KEY);
CREATE TABLE v1(c1 INTEGER);
INSERT INTO v0 VALUES(9223372036854775807);
INSERT INTO v1 VALUES(9223372036854775807);

SELECT * FROM v0 WHERE v0.c0>=(v0.c0 + 1); --nothing 
SELECT * FROM v1 WHERE v1.c1>=(v1.c1 + 1); --nothing
SELECT * FROM v0 WHERE v0.c0>=(9223372036854775807 + 1); --9223372036854775807 
SELECT * FROM v1 WHERE v1.c1>=(9223372036854775807 + 1); --nothing

Since v0.c0 and the literal value have the same value and the same type, the results of queries 1 and 3 should be same, but they are not.