> 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 <slav...@bigfraud.org> wrote: > On 18 Feb 2019, at 4:41pm, Charles Leifer <colei...@gmail.com> 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 > 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
