Thanks for the explanation. So does this mean that two identical queries
can have completely different query plans depending on whether they use a
parameters vs literal values embedded in the SQL string?
> But in this case with the WHERE clause is incompletely specified, there
is no way for the query planner to do that.
At some point before actually executing the query I'm calling
sqlite3_bind...() to set the value of the parameter placeholder. Presumably
SQLite would know at that point that a certain index is usable (or in this
case, that a certain constraint is relevant)?
This seems to me to have the effect that partial indexes are in many ways
ineffective when mixed with parameterized queries. I understand that they
might be applicable in cases where very general assumptions about
null/not-null can be inferred, but that's not what I would expect as an
end-user.
On Sun, Feb 17, 2019 at 5:26 PM Richard Hipp <d...@sqlite.org> wrote:
> On 2/17/19, Charles Leifer <colei...@gmail.com> wrote:
> > I'm having trouble executing an INSERT ... ON CONFLICT with a partial
> > index. It works fine in the SQLite shell, but it fails when I express the
> > conflict constraint using a parameterized query.
> >
> > For example:
> >
> > CREATE TABLE ukvp ("id" integer primary key, "key" text not null, "value"
> > int not null, "extra" int not null);
> > CREATE UNIQUE INDEX ukvp_partial ON ukvp (key, value) WHERE extra > 1;
> >
> > sql = 'INSERT INTO "ukvp" ("key", "value", "extra") VALUES (?, ?, ?), (?,
> > ?, ?) ON CONFLICT ("key", "value") WHERE ("extra" > ?) DO UPDATE SET
> > "extra" = EXCLUDED."extra"''
> > db.execute(sql, ('k1', 1, 2, 'k2', 2, 3, 1))
> >
> > But the following works:
> >
> > sql = 'INSERT INTO "ukvp" ("key", "value", "extra") VALUES (?, ?, ?), (?,
> > ?, ?) ON CONFLICT ("key", "value") WHERE ("extra" > 1) DO UPDATE SET
> > "extra" = EXCLUDED."extra"''
> > db.execute(sql, ('k1', 1, 2, 'k2', 2, 3))
> >
> > Is this a bug?
>
> No, this is not a bug.
>
> The purpose of the WHERE clause in an upsert is to match the
> constraint against a particular UNIQUE index. The query planner must
> know which index constraint is failing in order to generate correct
> code for the upsert. But in this case with the WHERE clause is
> incompletely specified, there is no way for the query planner to do
> that.
>
> For the same reason, this query:
>
> SELECT value FROM ukvp WHERE key=?1 AND extra>?2;
>
> will *not* use the partial index, but this query:
>
> SELECT value FROM ukvp WHERE key=?1 AND extra>1;
>
> *will* use the partial index. (Run each of the above with EXPLAIN
> QUERY PLAN to see for yourself.)
>
> The query planner will never use a partial index unless it can prove
> at SQL-statement compile-time that the WHERE clause of the query
> implies that the WHERE clause of the partial index. With your partial
> index, you can never prove anything about the truth of the condition
> if the query contains "extra>?1". However, if your partial index had
> said "WHERE extra IS NOT NULL", then the partial index would be usable
> in all of the above situations, since "extra>?1" does indeed imply
> that "extra IS NOT NULL".
> --
> D. Richard Hipp
> d...@sqlite.org
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
