Documentation Source Text

Ticket Change Details

Artifact ID: 257393dc712d89e210559bbceb6ae47c5e53a586
Ticket: 02fe49582e2b0e183ed38f191464606929c781b6
Partial index gives incorrect query result
User & Date: drh 2014-08-27 17:28:30

  1. Change foundin to "3.8.6"
  2. Change icomment to:

    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.

  3. Change login to "drh"
  4. Change mimetype to "text/x-fossil-wiki"
  5. Change severity to "Severe"
  6. Change status to "Open"
  7. Change title to "Partial index gives incorrect query result"
  8. Change type to "Code_Defect"