Обсуждение: BUG #14354: Wrong interpretation of JSON 'null'
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDM1NApMb2dnZWQgYnk6ICAg ICAgICAgIEtvdWJlciBTYXBhcmV2CkVtYWlsIGFkZHJlc3M6ICAgICAga291 YmVyQGdtYWlsLmNvbQpQb3N0Z3JlU1FMIHZlcnNpb246IDkuNC41Ck9wZXJh dGluZyBzeXN0ZW06ICAgRmVkb3JhCkRlc2NyaXB0aW9uOiAgICAgICAgCgpU cnlpbmcgdG8gcGFzcyAnbnVsbCcgdG8ganNvbmJfZWFjaF90ZXh0KCkgcmVz dWx0cyBpbiBhbiBFUlJPUiwgd2hpbGUgYXQgdGhlCnNhbWUgdGltZSBpdCBp cyBjb25zaWRlcmVkIGEgdmFsaWQgSlNPTiB2YWx1ZS4gVGhpcyBiZWhhdmlv dXIgc2VlbXMgcXVpdGUKaW5jb25zaXN0ZW50IC0gZWl0aGVyIHN1Y2ggYSB2 YWx1ZSBzaG91bGQgYmUgY29uc2lkZXJlZCBpbnZhbGlkIGluIGdlbmVyYWws CmVpdGhlciB0aGUgZnVuY3Rpb24gc2hvdWxkIHRyZWF0IGl0IGFzIGEgbm9y bWFsIE5VTEwgaW5zdGVhZC4NCg0KZGI9IyBzZWxlY3QgJ251bGwnOjpqc29u YjsNCiBqc29uYg0KLS0tLS0tLQ0KIG51bGwNCigxIHJvdykNCg0KZGI9IyBz ZWxlY3QganNvbmJfZWFjaF90ZXh0KCdudWxsJzo6anNvbmIpOw0KRVJST1I6 ICBjYW5ub3QgY2FsbCBqc29uYl9lYWNoX3RleHQgb24gYSBub24tb2JqZWN0 DQoNCmRiPSMgc2VsZWN0IGpzb25iX2VhY2hfdGV4dChOVUxMKTsNCiBqc29u Yl9lYWNoX3RleHQNCi0tLS0tLS0tLS0tLS0tLS0tDQooMCByb3dzKQoK
On 10/5/16, kouber@gmail.com <kouber@gmail.com> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 14354
> Logged by: Kouber Saparev
> Email address: kouber@gmail.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?
[1] https://www.postgresql.org/docs/9.6/static/datatype-json.html#JSON-TYPE-MAPPING-TABLE
[2] https://www.postgresql.org/docs/9.6/static/datatype-json.html#JSON-KEYS-ELEMENTS
--
Best regards,
Vitaly Burovoy
Okay, I also saw in the source code that it is looking for an _json-object_
(whatever that means) and is throwing that error otherwise. The thing is -
in my perception the string 'null' , being valid json(b), should also be
treated as valid _json-object_, isn't it? 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?
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.
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.
2016-10-05 17:08 GMT+03:00 Vitaly Burovoy <vitaly.burovoy@gmail.com>:
> On 10/5/16, kouber@gmail.com <kouber@gmail.com> wrote:
> > The following bug has been logged on the website:
> >
> > Bug reference: 14354
> > Logged by: Kouber Saparev
> > Email address: kouber@gmail.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?
>
> [1] https://www.postgresql.org/docs/9.6/static/datatype-json.
> html#JSON-TYPE-MAPPING-TABLE
> [2] https://www.postgresql.org/docs/9.6/static/datatype-json.
> html#JSON-KEYS-ELEMENTS
> --
> Best regards,
> Vitaly Burovoy
>
Kouber Saparev <kouber@gmail.com> writes:
> Okay, I also saw in the source code that it is looking for an _json-object_
> (whatever that means) and is throwing that error otherwise. The thing is -
> in my perception the string 'null' , being valid json(b), should also be
> treated as valid _json-object_, isn't it?
No. A JSON "object" is something with field names and values, like a
Perl hash. JSON also has scalars and arrays; those are JSON values,
but not JSON objects. 'null' is a scalar, I think, although for some
purposes it might be better to view it as a fourth primitive kind of
JSON value.
jsonb_each_text() needs to work on a JSON object because otherwise its
return convention of returning a set of field names and values makes
no sense. What would you imagine jsonb_each_text('2'::jsonb) ought
to do? Similarly, there's not really any sensible interpretation
of jsonb_each_text('null'::jsonb).
> ... I felt its an
> inconsistency in PostgreSQL itself.
The distinction between objects and other kinds of JSON values is
drawn in the JSON standard; we did not make it up. See
http://rfc7159.net/rfc7159
regards, tom lane
On 10/6/16, Kouber Saparev <kouber@gmail.com> wrote:
> 2016-10-05 17:08 GMT+03:00 Vitaly Burovoy <vitaly.burovoy@gmail.com>:
>>On 10/5/16, kouber@gmail.com <kouber@gmail.com> wrote:
>>> The following bug has been logged on the website:
>>>
>>> Bug reference: 14354
>>> Logged by: Kouber Saparev
>>> Email address: kouber@gmail.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
It looks like I do not know enough about the JSON type and I was treating
it always like an object with keys and values. You are absolutely correct,
now I see that I can really:
db=# select '2'::jsonb;
jsonb
-------
2
(1 row)
Which explains everything.
Cheers,
2016-10-06 15:47 GMT+03:00 Tom Lane <tgl@sss.pgh.pa.us>:
> Kouber Saparev <kouber@gmail.com> writes:
> > Okay, I also saw in the source code that it is looking for an
> _json-object_
> > (whatever that means) and is throwing that error otherwise. The thing is
> -
> > in my perception the string 'null' , being valid json(b), should also be
> > treated as valid _json-object_, isn't it?
>
> No. A JSON "object" is something with field names and values, like a
> Perl hash. JSON also has scalars and arrays; those are JSON values,
> but not JSON objects. 'null' is a scalar, I think, although for some
> purposes it might be better to view it as a fourth primitive kind of
> JSON value.
>
> jsonb_each_text() needs to work on a JSON object because otherwise its
> return convention of returning a set of field names and values makes
> no sense. What would you imagine jsonb_each_text('2'::jsonb) ought
> to do? Similarly, there's not really any sensible interpretation
> of jsonb_each_text('null'::jsonb).
>
> > ... I felt its an
> > inconsistency in PostgreSQL itself.
>
> The distinction between objects and other kinds of JSON values is
> drawn in the JSON standard; we did not make it up. See
> http://rfc7159.net/rfc7159
>
> regards, tom lane
>