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
