On 03/18/16 00:24 , Keith Medcalf wrote:
> The column data is case sensitive and the comparison is case
> sensitive. There is no case sensitive index.
>
> You either need (a) to make the column collate nocase (in which case
> any index on the column is also in the same nocase collation and thus
> you do not need it in the create index), or (b) specify that the
> comparison is using collation nocase.
I guess, OP suggested: following queries:
SELECT * FROM t WHERE a = b;
and
SELECT * FROM t WHERE (a COLLATE NOCASE = b) AND a = b;
gives same result (if (a = b) is true, then (a COLLATE NOCASE = b) is
true too).
If only NOCASE index is present, SQLite could've converted first query to
second automatically (probably, unless ANALYZE says NOCASE index is
"bad"/unspecific?)
I'm not *totally* sure, but I think this maybe apply to any collation that
obey rules from create_collation.html (`a COLLATE BINARY = b` always
implies `a COLLATE WHATEVER = b`).
Anyway, it is certainly true for SQLite's NOCASE.
Note this works with equality, but does not work with other comparisons:
('b' COLLATE BINARY > 'B') is true, but ('b' COLLATE NOCASE > 'B') is
false, so you cannot use non-BINARY collation to accelerate BINARY
BETWEEN/ORDER/etc.
Of course, it works with BINARY equality and non-BINARY collation
index, but does not work with two arbitrary non-binary collations (you
cannot use `COLLATE foo` index to accelerate `a COLLATE bar = b`).
(BTW, for `BINARY` `GROUP BY`, you, of course, cannot directly use
`COLLATE foobar` index, but it may be still advantageous to walk by this
index, instead of original table order, as it splits large table into
smaller definitely-non-equal subgroups; it may require adding some
[non-trivial] code to sqlite3 to take advantage of this property,
though; not low-hanging fruit).
>> -----Original Message-----
>> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
>> bounces at mailinglists.sqlite.org] On Behalf Of Domingo Alvarez Duarte
>> Sent: Thursday, 17 March, 2016 12:06
>> To: SQLite mailing list
>> Subject: [sqlite] Sqlite do not use index collate nocase
>>
>> Hello !
>>
>> In one database I created an index with collate nocase but it seems that
>> sqlite do not recognize it as a candidate index for a select.
>>
>> Bellow is the schema, the real database has more than a million rows. If I
>> alter the table to include the collation or remove the collation from the
>> index then sqlite does use the index.
>>
>> I expect an index collate nocase to be valid to queries like the one
>> bellow.
>>
>>
>> Cheers !
>>
>> Schema:
>>
>> PRAGMA foreign_keys=OFF;
>> BEGIN TRANSACTION;
>> CREATE TABLE 'items' (
>> ??? 'id' integer PRIMARY KEY,
>> ??? 'parent' INTEGER,
>> ??? 'by' text,
>> ??? 'score' integer DEFAULT 0,
>> ??? 'title' text,
>> ??? 'type' text,
>> ??? 'url' text,
>> ??? 'deleted' BOOLEAN DEFAULT 0,
>> ??? 'dead' BOOLEAN DEFAULT 0,
>> ??? 'comment' TEXT DEFAULT null,
>> ??? 'time' integer NOT NULL
>> );
>> CREATE INDEX "items_user_idx" ON "items"("by" COLLATE NOCASE);
>> COMMIT;
>>
>> explain query plan select * from items? where by='doppp';
>>
>> SCAN TABLE items
