Thanks - I've stepped through some of where.c to try and understand exactly what's going on, but in vain.
I'm looking around line 1272 of where.c, but I can't find where the system decides not to call xBestIndex because of a collation mismatch. By collation mismatch, I mean the column's collation sequence is not 'NOCASE', whereas the LT/GT expressions are NOCASE. I assume that's the condition that decides whether or not xBestIndex is invoked? To be clear, when you say "the collating sequence is NOCASE", do you mean that the column named 'field' has a collating sequence of 'NOCASE'? If so, I don't understand how this comes to be. The docs clearly state that the default is BINARY, and I've tried explicitly creating my vtable with (field COLLATE BINARY)... so that seems unlikely to me. But I'll have to dig further. Ben -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: 23 September 2010 01:57 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] How does xBestIndex know that a LIKE query is case insensitive? On Thu, Sep 23, 2010 at 7:16 AM, Ben Harper <b...@imqs.co.za> wrote: > I have a virtual table implementation that implements the > xBestIndex/xFilter functions. > Problem: > A LIKE query (for eg. field LIKE "prefix%") gets sets to xBestIndex as a > GT/LT pair. > However, I can't tell from the sqlite3_index_info whether that GT/LT should > be NOCASE collation or BINARY collation. I want the default LIKE behaviour, > which is NOCASE, but I can't figure out where to glean this information from > inside xBestIndex. > > Am I missing something? > LIKE will only get converted to a GT/LT pair if the collating sequence is NOCASE, or if you have specified PRAGMA case_sensitive_like=ON and the collating sequence is BINARY. So if you have a GT/LT pair in xBestIndex and you have not missed with case_sensitive_like, then you can be sure that the collating sequence is NOCASE. > > Thanks, > Ben > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
