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 > 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