|Title:||assertion fault on WITHOUT ROWID query using the OR optimization|
|Last Modified:||2015-07-23 16:41:38|
|Version Found In:||220.127.116.11|
drh added on 2015-07-23 14:27:01:
The following SQL generates an assertion fault while processing the final SELECT statement. <blockquote><verbatim> 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); </verbatim></blockquote> 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: (text/x-fossil-wiki)
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 18.104.22.168 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.