Documentation Source Text

View Ticket
Login
Ticket Hash: 02fe49582e2b0e183ed38f191464606929c781b6
Title: Partial index gives incorrect query result
Status: Closed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Resolution: External_Bug
Last Modified: 2014-08-27 17:29:28
Version Found In: 3.8.6
User Comments:
drh added on 2014-08-27 17:28:30: (text/x-fossil-wiki)
The SELECT statement at the end of the following block of SQL outputs no
rows, but it should output one row.

<blockquote><verbatim>
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';
</verbatim></blockquote>

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.

drh added on 2014-08-27 17:29:28: (text/x-fossil-wiki)
This ticket was suppose to go into the source-code repo, not the documentation
repo.