SQLite
View Ticket
Not logged in
Ticket UUID: a5c8ed66cae16243be61e2b1eddfd43749847ba6
Title: Incorrect count(*) when partial indices exist
Status: Fixed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2013-10-04 00:07:13
Version Found In: 3.8.0.2
User Comments:
drh added on 2013-10-03 23:45:45:

The final select in the following SQL code returns 11 instead of 128 because it is counting the entries in the partial index rather than the entries in the table. The count(*) optimization does not know to ignore partial indices.

CREATE TABLE t1(a,b,c,d);
INSERT INTO t1(a) VALUES(1),(2);
INSERT INTO t1(a) SELECT a+2 FROM t1;
INSERT INTO t1(a) SELECT a+4 FROM t1;
INSERT INTO t1(a) SELECT a+8 FROM t1;
INSERT INTO t1(a) SELECT a+16 FROM t1;
INSERT INTO t1(a) SELECT a+32 FROM t1;
INSERT INTO t1(a) SELECT a+64 FROM t1;
CREATE INDEX t1a ON t1(a) WHERE a BETWEEN 10 AND 20;
SELECT count(*) FROM t1;

This defect was discovered by the developers during code review.