SQLite

View Ticket
Login
Plaintext
2015-07-23
16:41 • Fixed ticket [8fd39115]: assertion fault on WITHOUT ROWID query using the OR optimization plus 7 other changes (artifact: 4bd51eb9 user: drh)
16:39
The sqlite3ExprCodeGetColumn() is not guaranteed to put the result in the register requested. Fix the skip-scan code generator for WITHOUT ROWID tables so that it always checks the register and copies the result if it lands in the wrong register. Fix for ticket [8fd39115d8f46ece70e7d4b3]. (check-in: 793e206f user: drh tags: trunk)
14:27 • New ticket [8fd39115] assertion fault on WITHOUT ROWID query using the OR optimization. (artifact: d63541a5 user: drh)
Ticket Hash: 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.