Thanks, Dan. I grabbed the fix and tried it out - works great, and a much better fix than what I suggested (not shocking). It really cleans up xBestIndex. In my simple cases, it's like the usable flag is now superfluous; xBestIndex is only getting passed usable constraints (and not getting called it all when there are no usable constraints). But perhaps there are still scenarios where an unusable constraint will show up.
Eric -----Original Message----- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Dan Kennedy Sent: Thursday, June 11, 2015 1:14 AM To: sqlite-users at mailinglists.sqlite.org Subject: Re: [sqlite] Virtual tables/xBestIndex: Is this a bug? On 06/11/2015 03:49 AM, Eric Hill wrote: > Is it a bug that SQLite changes the order of the constraints passed to > xBestIndex based merely on the order of the ON clause, when SQL (AFAIK) says > nothing about what the order of the ON clause should be? No. The order is undefined. However, it is less than perfect that the constraints on "film_id=" are marked usable when they are really not. That's the root of your problem I think - SQLite is asking for the wrong thing. When it eventually figures out that it can't actually use the plan it requested from xBestIndex (because the film_id= constraint is not actually usable) it falls back to a linear scan. There is now a change on the trunk that should fix this: http://www.sqlite.org/src/info/7b446771cadedafb Dan. _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
