/ View Ticket
Login
2013-10-04
00:07 Fixed ticket [a5c8ed66]: Incorrect count(*) when partial indices exist plus 5 other changes (artifact: ec7c3f4c user: drh)
00:00
Make sure the count(*) optimization works correctly even when partial indices are present. Ticket [a5c8ed66cae]. (check-in: 9f2f4c0a user: drh tags: trunk)
2013-10-03
23:45 New ticket [a5c8ed66] Incorrect count(*) when partial indices exist. (artifact: e97050ab user: drh)

Ticket Hash: 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.