Yes, I am referring to functions in the SQL, not functions in the
API. And I suppose the alternative to fixing them in SQLite is to
document them.
I suppose if it were to be fixed, it could break people depending on
the bad behavior. On the other hand, the existing behavior means that
some unexpected things are broken. For instance:
create table t(a unique);
insert into t values ('one');
insert into t values ('two');
insert into t values ('two' || x'00');
.dump
Yields:
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t(a unique);
INSERT INTO "t" VALUES('one');
INSERT INTO "t" VALUES('two');
INSERT INTO "t" VALUES('two');
COMMIT;
Which will not even load back in.
Having said that, yes, it could break existing usage, so any fix
should probably include a compatibility mode.
An alternative variant might be to enforce that NUL cannot occur in
text (i.e. makes it error out). This should be orthogonal to making
NULs well-behaved in SQL functions. I wouldn't recommend it, but it
might be a useful transition tool, or a useful third variant.
One interesting thought: It might actually make the functions faster
to not have to count the length of strings.
--David
Roger Binns writes:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 09/26/2011 01:13 PM, David Garfield wrote:
> > In documented one case
> > (prepare), NULs are string terminators even with the count, and there
> > is an extra optimization you may apply. All this works (I think).
>
> Agreed, ugly but documented. BTW in my wrapper the code leading up to a
> call to prepare was a bottleneck that I spent a lot of time profiling
> and optimising since it involved cache lookups, UTF8 conversion,
> multiple statements in one string, memory copies etc.
>
> > SQLite has a number of string manipulation functions.
>
> To be clear all the instances you are talking about are not the C API
> (which kept being mentioned) but rather user defined functions (in the
> sense of a SQL syntax entity).
>
> Some of the functions work just fine.
>
> Others have issues including stopping at the first NUL instead of the
> string length, working on blobs which makes no sense for string
> operations etc.
>
> They have been "broken" for many years and this breakage has been
> mentioned here over the years, but rarely. (ie doesn't affect many people).
>
> The workaround is to register your own functions of the same name with
> your own desired behaviour.
>
> It is conceivable that fixing the functions could break some existing
> code somewhere where someone has abused embedded NUL support (eg storing
> a list of NUL separated strings where only the first entry matters for
> user defined functions).
>
> Generally the team are very concerned about breaking even one deployment
> which is why this kind of thing tends not to get changed. They'll have
> to chime in this time, but I wouldn't get my hopes up.
>
> I'll create an entry in the bug tracker when I get home.
>
> Roger
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.11 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAk6A9pgACgkQmOOfHg372QQADQCferKj6ud1csg9aSjzVetXs5mo
> imsAnjWDgwD863PxAXJVbDdYh/qzGY7/
> =fJnR
> -----END PGP SIGNATURE-----
> _______________________________________________
> 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
