.mode list
.once |od -A n -t x1
SELECT x'610062';
 61 0a

It's been discussed before: https://sqlite.org/forum/forumpost/5a67c4e3f3e31ffe

In short, you're asking the SQLite CLI to print a BLOB, which requires first converting it to a UTF-8 TEXT value, which in turn treats null bytes as string terminators.

Treating a zero byte as terminating the blob means you are interpreting the blob as UTF-8 (or a C-string in a non-Unicode format), actually converting it to UTF-8 would do things like render the zeros as U+2400 characters. Random binary data is generally an invalid UTF-8 stream (.i.e. bytes greater than 340 don't form into valid multi-byte sequences).

SELECT x'610062';
61 0a

If for the blob x'6100..' it prints '61', then by the same token it should also print '00'. We're already in the domain of hex representation of byte values, so NULL byte is '00'.

No, you're asking the CLI to print the equivalent of a\0b, so it dutifully prints the a, stops further processing when it sees the null byte, then prints a newline (0x0a) to end the output line.

You're seeing 61 0a because you're transforming the CLI's output into its hex equivalent (.once |od -A n -t x1).

...so it dutifully prints the a, stops further processing when it sees the null byte

In case of a BLOB, the expectation is that the BLOB's size should determine the number of bytes to output, not the null-byte occurrence.

In this particular example the column type is BLOB, not TEXT:

sqlite> select typeof(x'610062');
blob

 the expectation is that ...

That is your expectation. It is not my expectation. My expectation is that the CLI will render query results in an human-readable format, and an ASCII NUL character is not normally considered human readable. The CLI will happily format your BLOB as hex or base64 or a host of other formats. But it won't output a zero byte.

Perhaps if you explain what it is you are actually trying to accomplish, we could suggest an alternative approach, that doesn't involve rewriting the entire CLI so that it outputs zero-blobs from BLOBs? Maybe the writefile() SQL function might be a better approach to achieving your goal.

If, however, CLI is allowed to output raw bytes of BLOB column values (as it currently is), then it should...

You are beating a dead horse here. This topic has been revisited many times before and the summary is that if one insists on emitting binary, and writefile() isn't suitable, they should write some C code to do it. The sqlite shell's maintainers have long expressed disinterest in adding special handling of nul bytes to the shell, which they consider to primarily be a tool for human interaction, not binary data.

So. What do you do if the value stored in the database was the text 'blob(3)' then?

Nothing prevents an explicit

select hex(x'610062');

Or base 64 or whatever.

But how is the cli meant to KNOW what YOU want unless you are explicit.

There is no universal blob to text mapping that works always. There are some that can be useful.

You're not winning this one, I think.

you're asking the SQLite CLI to print a BLOB, which requires first converting it to a UTF-8 TEXT value

I'm explicitly asking SQLite for 3 bytes with a blob literal syntax to demonstrate a bug in the output. In some other scenario this could be an actual column with binary data say JPEG image that gets truncated right in the middle. I don't see how any of this would require UTF-8 conversion is this the way it's implemented right now? Also byte zero is still valid UTF-8

printf 'a\0b' | isutf8 && echo yes

which requires first converting it to a UTF-8 TEXT value

The CLI doesn't do any text conversion. It is just using a C function to output a zero terminated string. In this case the string has an embedded null so it terminates early when it see it.

If the CLI used the length of the string, instead of zero termination, then the entire blob could be output without any problem.

It is just using a C function to output a zero terminated string.

Well then it shouldn't :)

Imagine if /bin/cat worked this way.

With the latest trunk check-in, you can get BLOB output sent to stdout, preserving zero bytes, using:

.mode list --escape off --rowsep ''

So, for example, if the script x1.sql is:

CREATE TABLE t1(id INTEGER PRIMARY KEY, data);
INSERT INTO t1 VALUES(1, x'6162630078797a');
.mode list --escape off --rowsep ''
SELECT data FROM t1 WHERE id=1;

And you run: "sqlite3 x1.sql | od -c", the output will be:

0000000   a   b   c  \0   x   y   z
0000007

Thank you for the fix. Now it works in other modes as well and even allows mixing with strings as long as final result is blob:

.mode markdown --escape symbol
SELECT CAST('a' || x'00' || 'b' AS BLOB) AS res;
| res |
|-----|
| a␀b |

Very cool!