Thanks! I'll be willing to test this beta once it's available.
One more thing - I think there is a bug here in the query planner if there is
an indexed expression in an index. It doesn't seem to think an index is ever
covering if the inputs to the expression isn't in the index as well.
E.g.
CREATE TABLE Foo
(
col1 int,
col2 int
);
CREATE INDEX FOOINDEX on FOO(abs(col1), col2);
SELECT col2 from Foo indexed by FOOINDEX where abs(col1) = 42;
In my opinion the index is covering the query above - even without col1 in the
index. col1 isn't used in the select - only abs(col1) is used. It can't
possibly be using col1 anywhere because it would hit a breakpoint I have.
However, the query isn't executed as covered:
explain query plan SELECT col2 from Foo indexed by FOOINDEX where abs(col1) =
42 ;
RecNo selectid order from detail
----- -------- ----- ---- ------------------------------------------------
1 0 0 0 SEARCH TABLE Foo USING INDEX FOOINDEX (<expr>=?)
-- NOTE: You can also see in the opcodes it's actually doing an OpenRead cursor
to the main table as well, but then not ever using it:
explain SELECT col2 from Foo indexed by FOOINDEX where abs(col1) = 42;
RecNo addr opcode p1 p2 p3 p4 p5 comment
----- ---- ----------- -- -- -- ---------------- -- -------
1 0 Init 0 14 0 00 (null)
2 1 OpenRead 0 2 0 2 00 (null)
3 2 OpenRead 1 3 0 k(3,nil,nil,nil) 02 (null)
4 3 Integer 42 1 0 00 (null)
5 4 SeekGE 1 11 1 1 00 (null)
6 5 IdxGT 1 11 1 1 00 (null)
7 6 IdxRowid 1 2 0 00 (null)
8 7 Seek 0 2 0 00 (null)
9 8 Column 1 1 3 00 (null)
10 9 ResultRow 3 1 0 00 (null)
11 10 Next 1 5 0 00 (null)
12 11 Close 0 0 0 00 (null)
13 12 Close 1 0 0 00 (null)
14 13 Halt 0 0 0 00 (null)
15 14 Transaction 0 0 16 0 01 (null)
16 15 TableLock 0 2 0 Foo 00 (null)
17 16 Goto 0 1 0 00 (null)
If I however change the index to:
CREATE INDEX FOOINDEX on FOO(abs(col1), col1, col2)
Then it becomes a covering index:
explain query plan SELECT col2 from Foo indexed by FOOINDEX where abs(col1) = 42
RecNo selectid order from detail
----- -------- ----- ----
---------------------------------------------------------
1 0 0 0 SEARCH TABLE Foo USING COVERING INDEX FOOINDEX
(<expr>=?)
- Deon
-----Original Message-----
From: sqlite-users [mailto:[email protected]] On
Behalf Of Richard Hipp
Sent: Wednesday, August 31, 2016 7:34 AM
To: SQLite mailing list <[email protected]>
Subject: Re: [sqlite] Expression Indexes - can I project the expression value
from the index?
On 8/31/16, Deon Brewis <[email protected]> wrote:
>
> CREATE INDEX FOOINDEX on FOO(func(col))
>
> HOWEVER, if I run:
> SELECT func(col) FROM FOO INDEXED BY FOOINDEX WHERE func(col) = 42;
>
> It will execute the breakpoint on func(col) for every row in the
> table, even though FOOINDEX is a covered index wrt. that query.
We will take this as an enhancement request.
--
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
