But that is the point. Strings are generally defined in two ways. Either: 1) a pointer, and count every byte up to but not including a NUL.
2) a pointer and a length, and count every byte in the specified length. If you have a specified length, the length matters, and NULs do not. NUL is a perfectly valid character in such a string. Internally, SQLite uses counted strings. It should treat NUL as just one more character, since it is just one more character. If a USER decides to use a negative length in sqlite3_bind_text() and to use sqlite3_column_text() without using sqlite3_column_bytes(), then that user's database doesn't have NULs in the strings, and everything just works for him. For the database as a whole to ASSUME this breaks SQLite for users who do want NULs in their text data. Actually, I guess I am more concerned with the "shell". I use blob columns that are partly text and would like to have the text portions at least be visible. I suspect that at least part of this problem came from SQLite's history as a TCL add-on. I suspect TCL, at least in it's early days, didn't allow NUL in a string. --David Garfield Richard Hipp writes: > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
