SQLite
View Ticket
Not logged in
Ticket UUID: 8fd39115d8f46ece70e7d4b3c481d1bd86194746
Title: assertion fault on WITHOUT ROWID query using the OR optimization
Status: Fixed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2015-07-23 16:41:38
Version Found In: 3.8.10.2
User Comments:
drh added on 2015-07-23 14:27:01:

The following SQL generates an assertion fault while processing the final SELECT statement.

CREATE TABLE t1(x, y, PRIMARY KEY(x,y)) WITHOUT ROWID;
INSERT INTO t1(x,y) VALUES(1,'AB');
INSERT INTO t1(x,y) VALUES(2,'CD');
ANALYZE;
DROP TABLE IF EXISTS sqlite_stat4;
DELETE FROM sqlite_stat1;
INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t1','t1','1000000 100 1');
ANALYZE sqlite_master;
SELECT * FROM t1
 WHERE (y = 'AB' AND x <= 4)
    OR (y = 'EF' AND x = 5);

The problem appears to have been introduced when the OR optimization was added to WITHOUT ROWID tables in version 3.8.5.


drh added on 2015-07-23 16:41:38:

Turns out the problem also involves the skip-scan optimization, which is why the ANALYZE was necessary to express the problem.

So to summarize, versions of SQLite between 3.8.5 and 3.8.10.2 might get an incorrect result on queries against a WITHOUT ROWID table that contain OR terms in the WHERE clause and that employ the skip-scan optimization.