On Thu, Sep 22, 2011 at 7:53 PM, David Garfield < garfi...@irving.iisd.sra.com> wrote:
> As far as I am concerned, this is a SERIOUS bug in sqlite. SQLite does whatever you ask it to do. It makes no attempt to enforce good string hygiene. If you hand it well-formed strings, it gives the expected results and always returning well-formed strings. If, on the other hand, you give it malformed strings with embedded NULs and/or illegal UTF characters, SQLite will muddle through as best it can using whatever you gave it, without complaining, and without overflowing buffers or segfaulting or leaking memory or otherwise failing. This is an intentional feature, not a bug. > 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 > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
