Re: [sqlite] Unused partial index

[Richard Hipp]

On Wed, Oct 30, 2013 at 9:47 AM, Filip Navara wrote:

> Hi,
>
> I tried to convert our custom tables and triggers into partial indexes.
> Unfortunately it seems that the query planner is unable to recognize
> identical terms. Could this be fixed or is there any workaround?
>
> Thanks,
> Filip Navara
>
> SQLite version 3.8.1 2013-10-17 12:57:35
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> CREATE TABLE "a" ("id" INTEGER PRIMARY KEY, "flags" INTEGER);
> sqlite> CREATE INDEX "b" ON "a" ("flags") WHERE "flags" & 16;
> sqlite> EXPLAIN QUERY PLAN SELECT * FROM "a" WHERE "flags" & 16;
>

Workaround: Add an "ORDER BY flags" to the query above.



> 0|0|0|SCAN TABLE a
> sqlite> EXPLAIN QUERY PLAN SELECT * FROM "a" INDEXED BY "b" WHERE "flags" &
> 16;
> Error: no query solution
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Unused partial index

[Filip Navara]

Hi,

I tried to convert our custom tables and triggers into partial indexes.
Unfortunately it seems that the query planner is unable to recognize
identical terms. Could this be fixed or is there any workaround?

Thanks,
Filip Navara

SQLite version 3.8.1 2013-10-17 12:57:35
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE "a" ("id" INTEGER PRIMARY KEY, "flags" INTEGER);
sqlite> CREATE INDEX "b" ON "a" ("flags") WHERE "flags" & 16;
sqlite> EXPLAIN QUERY PLAN SELECT * FROM "a" WHERE "flags" & 16;
0|0|0|SCAN TABLE a
sqlite> EXPLAIN QUERY PLAN SELECT * FROM "a" INDEXED BY "b" WHERE "flags" &
16;
Error: no query solution
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users