I've found some unexpected result with "COLLATE" operator.
I don't know what if it is a bug or my misunderstanding.
Please, check below description.
CREATE TABLE mytable (data);
INSERT INTO "mytable" VALUES('abc');
INSERT INTO "mytable" VALUES('ABC');
INSERT INTO "mytable" VALUES('abc/');
INSERT INTO "mytable" VALUES('ABC/');
INSERT INTO "mytable" VALUES('abc/a');
INSERT INTO "mytable" VALUES('ABC/A');
INSERT INTO "mytable" VALUES('abc/b');
INSERT INTO "mytable" VALUES('ABC/B');
INSERT INTO "mytable" VALUES('abc0');
INSERT INTO "mytable" VALUES('ABC0');
CREATE INDEX data_idx on mytable(data);
CREATE INDEX data_idx_nocase on mytable(data COLLATE nocase);
As you can see here I defined two indices that can be used both case and
nocase ordering.
It works very well when I use "COLLATE nocase" with '=' unary operator.
sqlite> select * from mytable where data = 'abc' COLLATE nocase;
abc
ABC
But, in case of using BETWEEN operator with "COLLATE nocase", it returns
unexpected result as follows.
sqlite> select * from mytable where data between 'abc/' and 'ABC0' COLLATE
nocase;
*abc/*
*abc/a*
*abc/b*
*abc0*
sqlite> select * from mytable where data between 'ABC/' and 'abc0' COLLATE
nocase;
*abc*
*abc/*
*ABC/*
*abc/a*
*ABC/A*
*abc/b*
*ABC/B*
*abc0*
*ABC0*
As you can see below ordered list,
first result set of between query doesn't have upper case data.
And, second result set contains unexpected data as like as 'abc', 'ABC'.
sqlite> select * from mytable order by data COLLATE nocase;
abc
ABC
abc/
ABC/
abc/a
ABC/A
abc/b
ABC/B
abc0
ABC0
Regards,
Yongil.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
