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
7. collate "twocase" func DOES NOT get called in the above and the result
ordering is not the same as the data in step#4
8. the above "bug" doesn't appear if I declare the the original table like
this
create table foo(i text collate twocase); <-- column has collation
declaration
9. both the following sql statements return data collated by "twocase" func
select * from foo_view order by i collate twocase;
select * from foo_view order by i ;
this almost makes sense because collation is tied to the columns.
is this an intentional behavior change from 3.6.2 to 3.6.16?
*this is not backwards compatible change though. apps based on 3.6.2 and
earlier now break when they run into this.*
twocase collate func code is here
static int twocaseCollatingFunc(void *NotUsed, int n1, const void *v1, int
n2, const void *v2) {
if (n1 < 2 || n2 < 2) {
printf ("lengths are smaller than 2: n1 = %d, n2 = %d\n", n1, n2);
return 0;
}
char c1 = ((char *)v1)[1];
char c2 = ((char *)v2)[1];
int rslt;
if (c1 < c2)
rslt = -1;
else if (c1 > c2)
rslt = 1;
else rslt = 0;
return rslt;
}
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
