(text/x-fossil-wiki)
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.
<blockquote><verbatim>
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;
</verbatim></blockquote>
This defect was discovered by the developers during code review.
|