On 7/31/15, Allen <allenpmd at gmail.com> wrote:
> 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.
A "bug" means that SQLite computes the wrong answer. Are you saying
that SQLite is computing an incorrect result in this case? Or are you
saying that SQLite is not using the partial index in the manner that
you think it ought? The latter may (or may not) be a a new
optimization opportunity, but it is not a 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.
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
drh at sqlite.org
