I have a table, a partial index and a query, which (somewhat simplified)
are:
create table Objs (Seqnum int primary key, Status, Timeout int) without
rowid;
create index DIndex on Objs (Seqnum) where Status == 0;
select Seqnum from Objs where Status == 0 and strftime('%s','now') >=
Timeout order by Seqnum limit ?1;
The Objs table will generally have many objects with Status <> 0, and only a
handful with Status == 0, so the intention is for the select statement to
use DIndex to rapidly find the Objs with Status == 0.
By default, this does not work. It does however work correctly if I enable
SQLITE_ENABLE_STAT4, populate Objs with typical data, and then run Analyze.
After this process, EXPLAIN QUERY PLAN shows:
SEARCH TABLE Objs USING INDEX DIndex (Seqnum=?)
However, if there are zero Objs in the table with Status == 0, then this
process fails with EXPLAIN QUERY PLAN showing:
SEARCH TABLE Objs USING PRIMARY KEY (Seqnum=?)
That is the bug. If there are a small number of Objs in the table with
Status == 0, then the select uses DIndex, which is the correct behavior. In
the limit, with zero Objs in the table with Status == 0, the select should
still use DIndex, but instead, it reverts to a full table scan. The query
optimizer should be able to figure out that zero Objs with Status == 0 is
roughly the same as one Objs with Status == 0, and handle them the same.
I tested this under sqlite-amalgamation-3081002 and
sqlite-amalgamation-3081101 and simply populated Objs with 20 rows, with
either zero or one of these rows having Status == 0, and the remainder
having Status == 1.
Please cc me directly on any replies.
