json() (and possibly all json functions) loses first character when parsing blob #4195
Description
turso> select cast(x'48656C6C6F' as text);
┌─────────────────────────────┐
│ CAST (X'48656C6C6F'AS text) │
├─────────────────────────────┤
│ Hello │
└─────────────────────────────┘
turso> select json(x'48656C6C6F');
┌──────────────────────┐
│ json (X'48656C6C6F') │
├──────────────────────┤
│ "ello" │
└──────────────────────┘
This is possibly just because json just isn't meant to hold binary data:
CREATE TABLE t(a);
INSERT INTO t VALUES (x'74727565');
SELECT json_object('value', a) FROM t;
Runtime error: JSON cannot hold BLOB values
Funnily enough, SQLite has the same bug/quirk:
sqlite> select json (X'48656C6C6F');
"ello"
There's a few weird things that happen when trying to serialize blobs into json, one of them is that the string "true" becomes a boolean:
turso> CREATE TABLE t(a);
INSERT INTO t VALUES (x'74727565'); -- ASCII "true"
SELECT json_object('a', a) FROM t;
┌────────────────────────┐
│ json_object ('a', t.a) │
├────────────────────────┤
│ {"a":true} │
└────────────────────────┘