cli/sql: \u0000 in JSON strings doesn't render properly (compared to psql)

[rolandcrosby]

Tried the following in cockroach demo on my Mac, with CockroachDB 2.1.1:

root@127.0.0.1:56202/defaultdb> create table foo (body jsonb);
CREATE TABLE

Time: 3.24ms

root@127.0.0.1:56202/defaultdb> insert into foo (body) values ('{"name": "roland", "favorite_charcters": ["\u0000", "\u0041", "\u26a3", "\ud83e\udd37"]}');
INSERT 1

Time: 4.081ms

root@127.0.0.1:56202/defaultdb> select * from foo;
                                               body
+------------------------------------------------------------------------------------------------+
  {"favorite_charcters": ["\\u0000", "A", "\342\232\243", "\360\237\244\267"], "name": "roland"}
(1 row)

Time: 3.763ms

While most Unicode escapes in JSON strings get encoded into UTF-8, it looks like "\u0000" isn't recognized that way. I would expect to see \u0000 show up as \x00 or something, as it does in other CockroachDB strings:

root@127.0.0.1:56202/defaultdb> create table bar (foo string);
CREATE TABLE

Time: 4.08ms

root@127.0.0.1:56202/defaultdb> insert into bar (foo) values (e'abcd\000\040efg');
INSERT 1

Time: 2.797ms

root@127.0.0.1:56202/defaultdb> select * from bar;
      foo
+--------------+
  abcd\x00 efg
(1 row)

Time: 2.266ms

[rolandcrosby]

cc @knz

[knz]

Yep it does indeed look like \u0000 is not parsed properly in a json context.

[maddyblue]

This is surprisingly not a bug. Printing as \u0001 is what postgres does. (Testing with \u0000 reveals that postgres thinks it's an invalid string.) Opened a PR to inscribe the current behavior.

[knz]

@mjibson if pg reports an error on \u0000 but not crdb, and the crdb behavior doesn't appear to be consistent (we handle NUL bytes properly in other places), I think we should change crdb to produce an error on \u0000 too.

[maddyblue]

Where do we handle that properly? Go's utf8.ValidString function says these are valid utf8 strings, so I'm not sure we're doing the wrong thing here.

> select e'\u0000';
  ?column?  
+----------+
  \x00      
(1 row)

[knz]

@mjibson unfortunately I think you have misunderstood the nature of the problem here.

There are two layers of complexity: the conversion from the string the user enters to a SQL string. This unescapes \uxxxx properly, as you have found.

Then there is a conversion of a SQL string to a JSON string. This re-escapes NUL characters to the JSON string \u0000, which seems to be incorrect. Meanwhile other unicode characters (including \u0001) are converted to JSON strings containing that unicode character, properly.

I do think we need further investigation of the handling of the SQL -> JSON string conversion.

[knz]

I have investigated this further. The problem is in cockroach sql actually.

Using the psql command using PostgreSQL:

kena=> create table foo (body jsonb);
CREATE TABLE
kena=> insert into foo (body) values ('{"name": "roland", "favorite_charcters": ["\u0001", "\u0041", "\u26a3", "\ud83e\udd37"]}');
INSERT 0 1
kena=> select * from foo;
                                body
---------------------------------------------------------------------
 {"name": "roland", "favorite_charcters": ["\u0001", "A", "⚣", "🤷"]}

kena=> select body->'favorite_charcters'->0 from foo;
 ?column?
----------
 "\u0001"

Then again psql, but this time with CockroachDB:

root=# create table foo (body jsonb);
CREATE TABLE
root=# insert into foo (body) values ('{"name": "roland", "favorite_charcters": ["\u0000", "\u0041", "\u26a3", "\ud83e\udd37"]}');
INSERT 0 1
root=# select * from foo;
                                body
---------------------------------------------------------------------
 {"favorite_charcters": ["\u0000", "A", "⚣", "🤷"], "name": "roland"}
root=# select body->'favorite_charcters'->0 from foo;
 ?column?
----------
 "\u0000"

So the behavior is consistent between PostgreSQL and CockroachDB.

However, the cockroach sql command is unhappy.

This is likely related to #31872.

[knz]

I have finished investigating this: the feature works as intended, and the weird display is a current (mis)feature of the SQL shell - one covered by #28756 and perhaps #31872.

Long story short: both PostgreSQL and our JSONB parser accept to process and retain (in memory) special characters with a code < 32 (including \u0001). We go the extra mile and accept to process and retain \u0000, which pg rejects outright.

On input special characters are accepted even if not escaped, for example select to_json(e'\x01'::text)::jsonb works.

The special chars are stored in-memory, as intended, as a single characters.

However every time the JSONB is printed out - either via a direct conversion to string, or a conversion to byte array towards pgwire, any character with a code lower than 32 will get escaped using the \uNNNN escape format.

I verified this to be true in both pg and CockroachDb. So far, so good.

The final step is printing out the string to the screen/terminal.

• psql receives the bytes (result of json->string conversion) and prints them as-is to the screen/terminal

• cockroach sql will first convert the bytes to a Go string using the standard Go escaping algorithm, and only then print the result to the screen/terminal. This additional conversion step will double any \ character, escape some non-printable characters, etc.

I still have to figure out if this is an instance of a problem in lib/pq (that would be #31872), which we don't have control over, or the cockroach sql code itself trying to be too clever (that would be #28756).

In any case, both possible causes of the original symptoms are tracked by these separate issues, so I am going to close this one which has become redundant.