Hello.
I've coded up a custom collation method, and am seeing
what looks like strange behaviour. The method is
intended to sort IPv4 addresses before IPv6 addresses,
and IPv6 addresses before other text strings. It's
just a little wrapping around inet_ptoa and memcmp.
I'm running a back version of sqlite3, which is
apparently the latest bundled version known to the
standard software maintenance utility on the platform
I'm using (apt-get on Ubuntu precise).
I'ld like some advice, please.
First, does what follows appear strange to anyone else?
.version
SQLite 3.7.9 2011-11-01 00:52:41 c7c6050ef060877ebe77b41d959e9df13f8c9b5e
select load_extension('./ip-extension.so');
create table foo (x collate ipaddress);
insert into foo values('::1');
insert into foo values('127.0.0.1');
select rowid, * from foo;
1|::1
2|127.0.0.1
select rowid, * from foo order by x;
2|127.0.0.1
1|::1
insert into foo values('100A');
insert into foo values('128A');
insert into foo values(' ABCD');
insert into foo values('');
select rowid, * from foo;
1|::1
2|127.0.0.1
3|100A
4|128A
5| ABCD
6|
select rowid, * from foo where x < '' order by x;
2|127.0.0.1
1|::1
select rowid, * from foo where x > '' order by x;
5| ABCD
3|100A
4|128A
select rowid, * from foo order by x;
6|
5| ABCD
3|100A
2|127.0.0.1
4|128A
1|::1
select rowid, * from foo order by x collate ipaddress;
6|
5| ABCD
3|100A
2|127.0.0.1
4|128A
1|::1
What I find strange is that comparisons against the
empty string behave as expected, but ORDER BY sorts
the values as if no custom collation had been specified,
even where this is explicit in the SELECT statement.
Next, should I best just download the 3.7.14 tarball
and build an up-to-date library before anything else?
I feel that sending my code at this stage would be
to presume too much on people's interest.
Thanks in advance.
Niall O'Reilly
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
