Re: BUG #14354: Wrong interpretation of JSON 'null'

On 10/6/16, Kouber Saparev <kouber(at)gmail(dot)com> wrote:
> 2016-10-05 17:08 GMT+03:00 Vitaly Burovoy <vitaly(dot)burovoy(at)gmail(dot)com>:
>>On 10/5/16, kouber(at)gmail(dot)com <kouber(at)gmail(dot)com> wrote:
>>> The following bug has been logged on the website:
>>>
>>> Bug reference: 14354
>>> Logged by: Kouber Saparev
>>> Email address: kouber(at)gmail(dot)com
>>> PostgreSQL version: 9.4.5
>>> Operating system: Fedora
>>> Description:
>>>
>>> Trying to pass 'null' to jsonb_each_text() results in an ERROR, while
>>> at the same time it is considered a valid JSON value. This behaviour
>>> seems quite inconsistent - either such a value should be considered
>>> invalid in general, either the function should treat it as a normal NULL
>>> instead.
>>>
>>> db=# select 'null'::jsonb;
>>> jsonb
>>> -------
>>> null
>>> (1 row)
>>>
>>> db=# select jsonb_each_text('null'::jsonb);
>>> ERROR: cannot call jsonb_each_text on a non-object
>>>
>>> db=# select jsonb_each_text(NULL);
>>> jsonb_each_text
>>> -----------------
>>> (0 rows)
>>
>> It is not a bug. It works as expected.
>>
>> 1. NULL::jsonb is not the same as 'null'::jsonb
>> PG's NULL (not jsonb's 'null'!) as input returns NULL output.
>>
>> 2. Argument for jsonb_each_text should be a jsonb with an
>> _json-object_ at top-level (see types of primitives by [1] and [2]),
>> e.g. '{"key1":"value1", "key2":"value2"}' to produce pairs (key,
>> value).
>>
>> Your example has jsonb value with a null-value at top-level. The same
>> exception is raised if you send a json-string as an input:
>> db=# select jsonb_each_text('"str"'::jsonb);
>> ERROR: cannot call jsonb_each_text on a non-object
>>
>> P.S.: what you're expecting from the "select
>> jsonb_each_text('null'::jsonb)" call?
>
> Okay, I also saw in the source code that it is looking for an _json-object_
> (whatever that means)

It means mapping "key-value".

> and is throwing that error otherwise. The thing is -
> in my perception the string 'null' , being valid json(b),

Yes, it is. But JSON primitive 'null' is not a mapping "key-value".

> should also be treated as valid _json-object_, isn't it?

No. Unfortunately, JavaSctipt (its 2 letters are in the acronym
"JSON") uses the word "object" instead of "mapping" or "dictionary"
that leads to misunderstanding.
The string 'null' is a valid JSON object in meaning it can be parsed
according to its rules, but it is not JSON-object in meaning of
"mapping".

> Otherwise we are ending up with
> valid json's and valid json-object's, and then perhaps it is a
> documentation issue to clarify the difference between the two?

The table by [1] mentions it.
The second note from the bottom in [2] pays your attention to it.

> I would expect from select "jsonb_each_text('null'::jsonb)" to return an
> empty result set (just the same as an SQL NULL), as indeed this is the
> meaning - 'null' is an empty, but still a valid json object.

But your expectation is wrong since JSON value is not empty, it has
the single primitive of 'nulltype'.

> I am using jsonb_each_text() in another stored procedure I wrote myself to
> make json_diff(jsonb, jsonb), and it is failing for the entire multimillion
> table because of a few rows that had this 'null' string value. So I had to
> explicitly alter its invokation to jsonb_each_text(nullif($1,
> 'null')::jsonb), which is a work-around, but yet I felt its an
> inconsistency in PostgreSQL itself.

It is confusing, but it is the same as if someone asks you to:
1) count letters in an unknown phrase,
2) count letters in the 'unknown phrase'.

In the first case your answer is "I don't know" -- it is SQL's NULL, thereas
in the second case your answer is "13 without a space".

The only difference between those cases are quotes which defines or
not "objects" (strings).

[1] https://www.postgresql.org/docs/current/static/datatype-json.html#JSON-TYPE-MAPPING-TABLE
[2] https://www.postgresql.org/docs/current/static/functions-json.html

--
Best regards,
Vitaly Burovoy