[sqlite] Custom collation method works for comparison, not for ORDER BY
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
Re: [sqlite] Custom collation method works for comparison, not for ORDER BY
Niall O'Reilly wrote: > 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. > > select rowid, * from foo order by x collate ipaddress; > 6| > 5| ABCD > 3|100A > 2|127.0.0.1 > 4|128A > 1|::1 Do these queries give the correct result? select '100A' collate ipaddress < '127.0.0.1'; select '100A' collate ipaddress < ' ABCD'; I.e., does the collation function actually work? Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Custom collation method works for comparison, not for ORDER BY
On 05/12/12 21:12, Clemens Ladisch wrote: > Do these queries give the correct result? > > select '100A' collate ipaddress < '127.0.0.1'; > select '100A' collate ipaddress < ' ABCD'; > > I.e., does the collation function actually work? Thanks for the helpful suggestions. I wish I had thought of something so simple. Here's what I get: sqlite> select '100A' collate ipaddress < '127.0.0.1'; 0 sqlite> select '100A' collate ipaddress < ' ABCD'; 0 sqlite> select '127.0.0.1' collate ipaddress < '::1'; 1 sqlite> select '::1' collate ipaddress = '0::1'; 1 sqlite> select 'dead:beef::' collate ipaddress = 'DEAD:BEEF::'; 1 sqlite> These results match what I intended the collation function to do. It appears to work, including recognizing alternative notations for the same IPv6 address as equivalent. Similar tests using BETWEEN also work as intended. Thanks again. /Niall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Custom collation method works for comparison, not for ORDER BY
On 12/06/2012 06:11 AM, Niall O'Reilly wrote: On 05/12/12 21:12, Clemens Ladisch wrote: Do these queries give the correct result? select '100A' collate ipaddress< '127.0.0.1'; select '100A' collate ipaddress< ' ABCD'; I.e., does the collation function actually work? Thanks for the helpful suggestions. I wish I had thought of something so simple. Here's what I get: sqlite> select '100A' collate ipaddress< '127.0.0.1'; 0 sqlite> select '100A' collate ipaddress< ' ABCD'; 0 sqlite> select '127.0.0.1' collate ipaddress< '::1'; 1 sqlite> select '::1' collate ipaddress = '0::1'; 1 sqlite> select 'dead:beef::' collate ipaddress = 'DEAD:BEEF::'; 1 sqlite> These results match what I intended the collation function to do. It appears to work, including recognizing alternative notations for the same IPv6 address as equivalent. Similar tests using BETWEEN also work as intended. It still seems likely that the collation returns the wrong result some circumstances. Posting the code for it is probably the right thing to do. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Custom collation method works for comparison, not for ORDER BY
On 6 Dec 2012, at 05:21, Dan Kennedy wrote: > It still seems likely that the collation returns the wrong result > some circumstances. Posting the code for it is probably the right > thing to do. Thanks for the encouragement! I've used conditionally compiled sections so that the same code can be used to generate either an SQLite3 extension or a stand-alone executable for testing and demonstration. This demo compiles and runs under Ubuntu and OSX, and presents the sorted strings in the expected order. So far, I haven't tried to compile the extension under OSX. This and my Makefile follow below, as I understand attachments are not supported on this list. /Niall -- Makefile -- beware conversion of TABs -- # inlude for sqlite3 # replace by the directory that contains sqlite3ext.h INCLUDE = -I/usr/include ARCH= SOEXT = so CFLAGS = so_files = libsqliteipv6.$(SOEXT) all: $(so_files) .PHONY: extend demo clean sqlite3-ipv6-ext.o : sqlite3-ipv6-ext.c demo: ip-extension-demo ip-extension-demo: ip-extension.c $(CC) $< -o $@ -lsqlite3 extend: ip-extension.so ip-extension.so: CFLAGS=$(INCLUDE) -DEXTEND_SQLITE3 -fPIC -fno-stack-protector $(ARCH) ip-extension.so: ip-extension.o $(LD) -shared -o $@ $< clean : rm -f *.o a.out core core.* *% *~ *.$(SOEXT) -- ip-extension.c -- /* ** Parse an IP address, prefix, or range */ #include #include #include #include #include #ifdef EXTEND_SQLITE3 #include SQLITE_EXTENSION_INIT1 #endif #define WO_DATA_SZ 256 struct work_object { size_t size; struct {size_t length; unsigned char *data;} string; struct {size_t length; unsigned char *data;} source; unsigned char wire[sizeof(struct in6_addr)]; unsigned char type; }; struct work_object* new_work_object() { struct work_object *this; unsigned char *p; p = sqlite3_malloc(WO_DATA_SZ + sizeof(struct work_object)); this = p; this->type = 255; this->size = WO_DATA_SZ; this->string.length = 0; this->string.data = p + sizeof(struct work_object); this->string.data[0] = '\0'; this->string.data[WO_DATA_SZ - 1] = '\0'; this->source.length = 0; this->source.data = NULL; return this; } struct work_object* prime_work_object(struct work_object *this, const int n, const void *source ) { struct work_object *p; size_t sz; p = this ? this : new_work_object(); p->source.data = source; p->source.length = n; p->type = 255; if (inet_pton(AF_INET6, p->source.data, p->wire) > 0) { p->type = 6; } else if (inet_pton(AF_INET, p->source.data, p->wire) > 0) { p->type = 4; } return p; } void stringify_work_object(struct work_object *this) { this->string.length = 0; this->string.data[0] = '\0'; this->string.data[WO_DATA_SZ - 1] = '\0'; if (this->type == 6) { inet_ntop(AF_INET6, this->wire, this->string.data, this->size - 1); this->string.length = strlen(this->string.data); } else if (this->type == 4) { inet_ntop(AF_INET, this->wire, this->string.data, this->size - 1); this->string.length = strlen(this->string.data); } } int compare_work_objects(struct work_object *this, struct work_object *that) { if (this->type != that->type) return this->type - that->type; if (this->type == 6) return memcmp(this->wire, that->wire, sizeof(struct in6_addr)); if (this->type == 4) return memcmp(this->wire, that->wire, sizeof(struct in_addr)); return strncmp(this->source.data, that->source.data, 1 + (this->source.length < that->source.length) ? this->source.length : that->source.length); } #ifdef EXTEND_SQLITE3 /* SQLite3 extension interface here */ int compare_ipaddrs (void *q, /* required by API: not used */ int na, const void *pa,/* a: length, string */ int nb, const void *pb /* b: length, string */ ) { struct work_object *a, *b; int v; a = prime_work_object(NULL, na, pa); b = prime_work_object(NULL, nb, pb); v = compare_work_objects(a, b); sqlite3_free(a); sqlite3_free(b); return v; } /* SQLite invokes this routine once when it loads the extension. ** Create new functions, collating sequences, and virtual table ** modules here. This is usually the only exported symbol in ** the shared library. */ int sqlite3_extension_init ( sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi) { SQLITE_EXTENSION_INIT2(pApi) ; /* sqlite3_create_function(db, "displayip", 1, SQLITE_UTF8, 0, display1, 0, 0); */ sqlite3_create_collation( db, /* sqlite3* */ "ipaddress", /* const char *zName */ SQLITE_UTF8, /* int eTextRep */ NULL,/* void *pArg -- not used */ compare_ipaddrs
Re: [sqlite] Custom collation method works for comparison, not for ORDER BY
Niall O'Reilly wrote: > This and my Makefile follow below, as I understand attachments > are not supported on this list. Your code assumes, in several places, that strings passed to collation function are NUL-terminated. They don't have to be - that's why lengths are also passed. I think this may be causing the problem you are seeing: when the string comes from a literal (as in x < '' ) it just may happen to be NUL-terminated, but when it comes straight from the database, it may not be, and you are cheerfully reading garbage past the end of buffer. Your test program, of course, always happens to pass NUL-terminated strings. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Custom collation method works for comparison, not for ORDER BY
On 6 Dec 2012, at 14:14, Igor Tandetnik wrote: > Your code assumes, in several places, that strings passed to collation > function are NUL-terminated. They don't have to be - that's why lengths are > also passed. I think this may be causing the problem you are seeing: when the > string comes from a literal (as in x < '' ) it just may happen to be > NUL-terminated, but when it comes straight from the database, it may not be, > and you are cheerfully reading garbage past the end of buffer. Thanks for your analysis and helpful comments. I'll need to take care to make a NUL-terminated copy of each source string, as inet_pton doesn't take a count argument. > Your test program, of course, always happens to pass NUL-terminated strings. Doh! 8-) /Niall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users