On Nov 19, 2009, at 6:40 AM, Vasu Nori wrote:
> I am noticing a behavior change in processing of "collate" from
> 3.6.2 to
> versions 3.6.16 & beyond.
> it *could* make sense but I find some inconsistencies.. let me
> explain.
>
> 1. create a simple collation function which compares two input
> strings based
> on just the second char (code is at the end of this email)
> 2. call this function "twocase"
> 3. create a table and populate with this sample data
>
> sqlite>create table foo(i text);
> sqlite>insert into foo values("a2");
> sqlite>insert into foo values("b1");
> sqlite>insert into foo values("c5");
> sqlite>insert into foo values("d4");
> sqlite>insert into foo values("e3");
>
> 4. select from the table using orderby and collate
>
> sqlite> select * from foo order by i collate twocase;
>
> b1
>
> a2
> e3
> d4
> c5
>
> 5. notice that the above are sorted on the 2nd char
> 6. create a view on the above table and select from the view with
> order by
> and collate
>
> sqlite>create view foo_view as select i from foo;
> sqlite>select * from foo_view;
> sqlite>select * from foo_view order by i collate twocase;
> a2
> b1
> c5
> d4
> e3
Could not reproduce this with 3.6.20 or 3.6.18 (see below). Do you
have a C program you can post that demonstrates the problem?
Dan.
~/sqlite/tipbld$ ./sqlite3
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE foo(i text);
sqlite> INSERT INTO foo VALUES('a');
sqlite> INSERT INTO foo VALUES('B');
sqlite> INSERT INTO foo VALUES('c');
sqlite> CREATE VIEW foo_view AS SELECT i FROM foo;
sqlite> SELECT i FROM foo ORDER BY i COLLATE nocase;
a
B
c
sqlite> SELECT i FROM foo_view ORDER BY i COLLATE nocase;
a
B
c
sqlite> SELECT i FROM foo ORDER BY i;
B
a
c
sqlite> SELECT i FROM foo_view ORDER BY i;
B
a
c
sqlite>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
