Re: [sqlite] Case Insensitive Defect with New Query Planner

2014-01-16 Thread Richard Hipp
On Wed, Jan 15, 2014 at 6:13 PM, Chris Swinefurth wrote: > Guys, > I’ve identified a detrimental query planner change between 3.7.12 > and 3.8.1. It appears to be a change from the 3.8 NGQP... This is completely unrelated to NGQP. The inefficiency was introduced into SQLite version 3.7

Re: [sqlite] Case Insensitive Defect with New Query Planner

2014-01-16 Thread Chris Swinefurth
How about the database itself? https://dl.dropboxusercontent.com/u/4781945/like_case_example.tgz The more I have thought about this, it seems 3.8.1 is doing the right thing. The query with “collate nocase” has implications for unicode that I wasn’t considering. I do wonder if the explain plan

Re: [sqlite] Case Insensitive Defect with New Query Planner

2014-01-15 Thread Richard Hipp
On Wed, Jan 15, 2014 at 6:13 PM, Chris Swinefurth wrote: > The query: > SELECT terms.term, terms.id, terms.type, terms.product_id FROM terms > JOIN products ON terms.product_id = products.id JOIN > sub_categories ON products.category_id = sub_categories.id WHERE > terms.term LIKE 's%

[sqlite] Case Insensitive Defect with New Query Planner

2014-01-15 Thread Chris Swinefurth
Guys, I’ve identified a detrimental query planner change between 3.7.12 and 3.8.1. It appears to be a change from the 3.8 NGQP. A query with a like where clause with “collate nocase” now uses a full table scan instead of a covering index. This occurs even if the case_sensitive_like pr