SQLite

View Ticket
Login
Plaintext
2014-08-27
17:41 • Fixed ticket [98d973b8]: Partial index gives incorrect query result plus 5 other changes (artifact: a75d6bfc user: drh)
17:37
When determining whether or not a partial index is usable, do not assume that the cursor number assigned to each table in the query is the same as its index in the FROM clause. Fix for ticket [98d973b8f5]. (check-in: fcebca16 user: dan tags: trunk)
17:31 • New ticket [98d973b8] Partial index gives incorrect query result. (artifact: a441e2a9 user: drh)
Ticket Hash: 98d973b8f5a2ee195e69e949c880e08ac254f4f5
Title: Partial index gives incorrect query result
Status: Fixed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2014-08-27 17:41:38
Version Found In: 3.8.6
User Comments:
drh added on 2014-08-27 17:31:04:

The SELECT statement at the end of the following block of SQL outputs no rows, but it should output one row. 

CREATE TABLE t1(a, b);
CREATE TABLE t2(c, d);
INSERT INTO t1 VALUES(1, 'xyz');
INSERT INTO t2 VALUES('abc', 'not xyz');
CREATE INDEX i2 ON t2(c) WHERE d='xyz'; 
SELECT * FROM (SELECT * FROM t1 WHERE a=1 AND b='xyz'), t2 WHERE c='abc';\

A correct result is obtained if the index created on the penultimate line is convert into a complete index or if the index is omitted entirely.

This bug appears to have been in the implementation of partial indexes from their introduction in SQLite version 3.8.0, 2013-08-26.