As far as I am concerned, this is a SERIOUS bug in sqlite. When you
have counted strings, which is all sqlite has (except for some API
functions), NULs have no special meaning. This is fairly easily seen
in the command line interface, where the code ignores the length and
uses strlen forms.
Testing various functions:
hex(), relational operators, and group by get it right. max() and
min() seem to get it right (but I may not have tested enough).
length(), quote(), upper(), lower(), like(), and glob() get it wrong.
group_concat() gets it wrong returning the result, but looks like it
may have built the right thing internally.
replace(), trim(), ltrim(), rtrim() get it right in the first
parameter, but not in the second parameter. replace() gets it right
in the third parameter.
I'm not sure what else would be expected to allow NULs, so I didn't
try numeric and date/time functions.
Testing can be a bit of a pain. Things like:
BEGIN TRANSACTION;
CREATE TABLE t(a);
insert into t values (cast(x'6F6E65202020' as text));
insert into t values (cast(x'6F6E6520202030' as text));
insert into t values (cast(x'6F6E6520202000' as text));
insert into t values (cast(x'6F6E6520202078' as text));
insert into t values (cast(x'6F6E650074776F20' as text));
insert into t values (cast(x'20006F6E6520' as text));
insert into t values (cast(x'00206F6E6520' as text));
COMMIT;
.mode column
.width 10 20 20 20
.header on
select a, hex(a), hex(trim(a)), hex(trim(a,'one'||x'2000')) from t;
Yields:
a hex(a) hex(trim(a)) hex(trim(a,'one'||x'
---------- -------------------- -------------------- --------------------
one 6F6E65202020 6F6E65
one 0 6F6E6520202030 6F6E6520202030 30
one 6F6E6520202000 6F6E6520202000 00
one x 6F6E6520202078 6F6E6520202078 78
one 6F6E650074776F20 6F6E650074776F 007477
20006F6E6520 006F6E65 00
00206F6E6520 00206F6E65 00
What I expect (including column screw-ups because NUL is zero width in
xterm) is:
a hex(a) hex(trim(a)) hex(trim(a,'one'||x'
---------- -------------------- -------------------- --------------------
one 6F6E65202020 6F6E65
one 0 6F6E6520202030 6F6E6520202030 30
one 6F6E6520202000 6F6E6520202000
one x 6F6E6520202078 6F6E6520202078 78
onetwo 6F6E650074776F20 6F6E650074776F 7477
two 20006F6E6520 006F6E65
two 00206F6E6520 00206F6E65
Without the hex() calls, you can't even tell what worked and what didn't.
--David Garfield
Mira Suk writes:
> On 9/21/2011 21:22 Igor Tandetnik wrote:
>
> > You can include the NUL terminator, if you want it to actually be stored
> > in the database.
>
> > Igor Tandetnik
>
> Actually you can't - if you do all SQL string functions will not work.
> to be clear -
> SELECT TRIM(what ever text column you stored with including null on end of
> string)
> will not trim that string.
>
> found out the hard way.
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
