ON CONFLICT with partial indexes

classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

ON CONFLICT with partial indexes

Charles Leifer
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;

INSERT INTO ukvp (key, value, extra) VALUES ('k1', 1, 1);
INSERT INTO ukvp (key, value, extra) VALUES ('k2', 2, 2);

INSERT INTO ukvp (key, value, extra) VALUES ('k1', 1, 2), ('k2', 2, 3)
  ON CONFLICT (key, value) WHERE extra > 1
  DO UPDATE set extra=EXCLUDED.extra;

This works OK in the SQLite CLI, but when attempting to execute such a
query using a prepared query I get the exception: "ON CONFLICT clause does
not match any PRIMARY KEY or UNIQUE constraint". I believe it is because
the "extra > 1" must be exactly and literal -- the "1" cannot be a
parameter in the query.

For example, using a Python SQLite library, this fails with the above error
message.

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?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ON CONFLICT with partial indexes

Simon Slavin-3
On 17 Feb 2019, at 8:51pm, Charles Leifer <[hidden email]> wrote:

> Is this a bug?

Just to make things easier, which version of SQLite are you using in your Python SQLite library ?  If you don't know, you can find this out using

    SELECT sqlite_version();
    SELECT sqlite_source_id();

The second may give a blank result or an error, which is fine.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ON CONFLICT with partial indexes

Charles Leifer
I run against the latest and greatest.

Python:

In [1]: import sqlite3

In [2]: sqlite3.sqlite_version
Out[2]: '3.28.0'


Sqlite:

sqlite> select sqlite_version(), sqlite_source_id();
3.28.0|2019-02-12 22:58:32
167b91df77fff1a84791f6ab5f72239b90475475be690a838248119b6dd312f0


On Sun, Feb 17, 2019 at 2:58 PM Simon Slavin <[hidden email]> wrote:

> On 17 Feb 2019, at 8:51pm, Charles Leifer <[hidden email]> wrote:
>
> > Is this a bug?
>
> Just to make things easier, which version of SQLite are you using in your
> Python SQLite library ?  If you don't know, you can find this out using
>
>     SELECT sqlite_version();
>     SELECT sqlite_source_id();
>
> The second may give a blank result or an error, which is fine.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ON CONFLICT with partial indexes

Olivier Mascia
> Le 17 févr. 2019 à 22:05, Charles Leifer <[hidden email]> a écrit :
>
> I run against the latest and greatest.
>
> Python:
>
> In [1]: import sqlite3
>
> In [2]: sqlite3.sqlite_version
> Out[2]: '3.28.0'
>
>
> Sqlite:
>
> sqlite> select sqlite_version(), sqlite_source_id();
> 3.28.0|2019-02-12 22:58:32
> 167b91df77fff1a84791f6ab5f72239b90475475be690a838248119b6dd312f0

The latest release is 3.27.1 (https://www.sqlite.org/releaselog/3_27_1.html).

The code you use comes from a snapshot of the code (https://sqlite.org/src/info/167b91df77fff1a8) right in the middle of work in progress after 3.27.1 was released and (probably) long before 3.28 is.

That may not be related to the issue you raised, but you'd probably be well advised to make sure you run the latest *released* code, because the releases go through a huge test/validation procedure.

--
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ON CONFLICT with partial indexes

Charles Leifer
Olivier, what do you mean "snapshot"? What is a release? How can I find out
about the SQLite "releases" you are talking about?

On Sun, Feb 17, 2019 at 3:23 PM Olivier Mascia <[hidden email]> wrote:

> > Le 17 févr. 2019 à 22:05, Charles Leifer <[hidden email]> a écrit :
> >
> > I run against the latest and greatest.
> >
> > Python:
> >
> > In [1]: import sqlite3
> >
> > In [2]: sqlite3.sqlite_version
> > Out[2]: '3.28.0'
> >
> >
> > Sqlite:
> >
> > sqlite> select sqlite_version(), sqlite_source_id();
> > 3.28.0|2019-02-12 22:58:32
> > 167b91df77fff1a84791f6ab5f72239b90475475be690a838248119b6dd312f0
>
> The latest release is 3.27.1 (
> https://www.sqlite.org/releaselog/3_27_1.html).
>
> The code you use comes from a snapshot of the code (
> https://sqlite.org/src/info/167b91df77fff1a8) right in the middle of work
> in progress after 3.27.1 was released and (probably) long before 3.28 is.
>
> That may not be related to the issue you raised, but you'd probably be
> well advised to make sure you run the latest *released* code, because the
> releases go through a huge test/validation procedure.
>
> --
> Best Regards, Meilleures salutations, Met vriendelijke groeten,
> Olivier Mascia
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ON CONFLICT with partial indexes

Richard Hipp-3
In reply to this post by Charles Leifer
On 2/17/19, Charles Leifer <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ON CONFLICT with partial indexes

Charles Leifer
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 <[hidden email]> wrote:

> On 2/17/19, Charles Leifer <[hidden email]> 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
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ON CONFLICT with partial indexes

Richard Damon
> On Feb 18, 2019, at 10:23 AM, Charles Leifer <[hidden email]> wrote:
>
> 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?
>

Remember the query plan is determined when that statement is compiled, which is BEFORE you do the binding of the parameters, so the plan can not depend on the value of parameters. There is no later attempt to optimize once the values are known, as in general, this isn’t apt to help (you have one case where it could, but to help that case, you would need to hurt a lot of other more common cases).
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ON CONFLICT with partial indexes

Dominique Devienne
On Mon, Feb 18, 2019 at 4:38 PM Richard Damon <[hidden email]>
wrote:

> Remember the query plan is determined when that statement is compiled,
> which is BEFORE you do the binding of the parameters, so the plan can not
> depend on the value of parameters. There is no later attempt to optimize
> once the values are known, as in general, this isn’t apt to help (you have
> one case where it could, but to help that case, you would need to hurt a
> lot of other more common cases).
>

Actually, "bind peeking" to find a more optimal plan does often help.
Otherwise "non-lite" RDBMS wouldn't do it.
The fact SQLite does not implement, does not mean it's not a useful thing
to do. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ON CONFLICT with partial indexes

Simon Slavin-3
In reply to this post by Charles Leifer
On 18 Feb 2019, at 3:23pm, Charles Leifer <[hidden email]> wrote:

> 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)?

SQLite knows before then.  The SQLite query optimizer does not do algebra.  It does its job without knowing what values you will plug into the parameters.

Indexes are chosen by looking at how you are comparing certain columns: whether you compare a column using "<" or ">" or "=" or some other comparator.  The values you're comparing them with are ignored.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ON CONFLICT with partial indexes

Charles Leifer
Simon, I appreciate that, but your comment seems to contradict the example
I provided, as well as the example Dr. Hipp provided. Am I misunderstanding?

On Mon, Feb 18, 2019, 9:44 AM Simon Slavin <[hidden email] wrote:

> On 18 Feb 2019, at 3:23pm, Charles Leifer <[hidden email]> wrote:
>
> > 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)?
>
> SQLite knows before then.  The SQLite query optimizer does not do
> algebra.  It does its job without knowing what values you will plug into
> the parameters.
>
> Indexes are chosen by looking at how you are comparing certain columns:
> whether you compare a column using "<" or ">" or "=" or some other
> comparator.  The values you're comparing them with are ignored.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ON CONFLICT with partial indexes

Simon Slavin-3
On 18 Feb 2019, at 4:41pm, Charles Leifer <[hidden email]> wrote:

> Simon, I appreciate that, but your comment seems to contradict the example I provided, as well as the example Dr. Hipp provided. Am I misunderstanding?

SQLite can compare two comparators.  In Dr. Hipp's example the index says

"extra IS NOT NULL"

and the WHERE clause says

"WHERE extra>?1"

SQLite knows that for extra to be bigger than a number -- any number -- extra cannot be NULL.  So it can deduce that it can use that partial index.  That's what Dr. Hipp wrote.

However, suppose you had a partial index that keyed on

"extra > 4"

and your SELECT had the clause

"WHERE extra > ?1"

The parameter might be set to 7.  You and I know that this means SQLite could use the partial index.  But SQLite will not compare the 7 and the 4 and decide it can use the partial index.  It does not know that 7 is greater than 4, because it doesn't look at the parameter's value before doing its optimization.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ON CONFLICT with partial indexes

Keith Medcalf
In reply to this post by Charles Leifer

The only thing which MIGHT (note that I said MIGHT, you will have to try and see whether it does or not) make a difference is if you have STAT4 enabled and have generated statistics on your database.  When STAT4 has been enabled AND you have statistics THEN the query plan is re-prepared after a BIND which changes information that may be relevant to the selection of INDEXES used by the planner.  This obviously means that the WHERE clause is now fully specified (because the plan is regenerated after the bind) and the planner MAYHAPS SO or MAYHAPS NOT make a different choice.

The difference is that with STAT4 enabled, every query that has bound parameters that could affect plan generation will be reprepared on the first step at execution time taking the value of the bindings into account.  If the time taken to "double prepare" the plan on every statement execution is greater than the time taken to execute the statement using the generic (first prepared plan), then the overall result will (wall clock elapsed time from GO to DONE) will be greater than if you did not use STAT4.  You will only know whether that is effective in your case is by giving it the old college try.  You may find that the use of the regenerated "particular" plan is significantly more performant to be worthwhile ... or you may find that the overhead of the re-prepare reduces performance over just using the generic plan.

Of course, I do not know if having the appropriate STAT4 statistics and the re-prepare after the BIND will result in using your partial index in this particular instance without going and testing it.  

Richard may know off-hand, but I do not.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Charles Leifer
>Sent: Monday, 18 February, 2019 08:23
>To: SQLite mailing list
>Subject: Re: [sqlite] ON CONFLICT with partial indexes
>
>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 <[hidden email]> wrote:
>
>> On 2/17/19, Charles Leifer <[hidden email]> 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
>> [hidden email]
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ON CONFLICT with partial indexes

Charles Leifer
In reply to this post by Simon Slavin-3
> However, suppose you had a partial index that keyed on
>
> "extra > 4"
>
> and your SELECT had the clause
>
> "WHERE extra > ?1"
>
> The parameter might be set to 7.  You and I know that this means SQLite
could use the partial index.  But SQLite will not compare the 7 and the 4
and decide it can use the partial index.  It does not know that 7 is
greater than 4, because it doesn't look at the parameter's value before
doing its optimization.

Right -- I definitely understand the implications. My confusion is a result
of not having realized that parameterized queries are somewhat of a
second-class-citizen. That is, the same query will be treated differently
depending on whether it uses literal values vs bind parameters. I'm not
trying to debate or even question the rationale for these things -- I just
am noting that I found this surprising, since using bind parameters is
*always* what I do and suggest others to do.

This is a bummer because in multiple apps I have created some partial
indexes and did all my EXPLAIN analysis using the CLI and literal values,
not realizing that once I started using the DB from my application these
indexes would be unusable.

On Mon, Feb 18, 2019 at 10:54 AM Simon Slavin <[hidden email]> wrote:

> On 18 Feb 2019, at 4:41pm, Charles Leifer <[hidden email]> wrote:
>
> > Simon, I appreciate that, but your comment seems to contradict the
> example I provided, as well as the example Dr. Hipp provided. Am I
> misunderstanding?
>
> SQLite can compare two comparators.  In Dr. Hipp's example the index says
>
> "extra IS NOT NULL"
>
> and the WHERE clause says
>
> "WHERE extra>?1"
>
> SQLite knows that for extra to be bigger than a number -- any number --
> extra cannot be NULL.  So it can deduce that it can use that partial
> index.  That's what Dr. Hipp wrote.
>
> However, suppose you had a partial index that keyed on
>
> "extra > 4"
>
> and your SELECT had the clause
>
> "WHERE extra > ?1"
>
> The parameter might be set to 7.  You and I know that this means SQLite
> could use the partial index.  But SQLite will not compare the 7 and the 4
> and decide it can use the partial index.  It does not know that 7 is
> greater than 4, because it doesn't look at the parameter's value before
> doing its optimization.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users