try using a user-defined collation function I included in the post. here it
is again.
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;
}
add this code to src/main.c.
in the same file, don't forget to add createCollation() line for "TWOCASE" -
just like the line for "NOCASE".
I tried it on 3.6.2 (works) and 3.6.16 (fails).
thanks for looking into this.
On Wed, Nov 18, 2009 at 9:31 PM, Dan Kennedy <danielk1...@gmail.com> wrote:
>
>
>
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
