Re: BUG #10728: json_to_recordset with nested json objects NULLs columns

From: Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
To: matti(dot)hameister(at)technologygroup(dot)de
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #10728: json_to_recordset with nested json objects NULLs columns
Date: 2014-06-23 05:13:28
Message-ID: CAB7nPqQ2hLVt3o-d8-5=h5MEfPr1sS9jT4CK0GpQXew8PeY=yw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

On Sun, Jun 22, 2014 at 8:34 AM, <matti(dot)hameister(at)technologygroup(dot)de>
wrote:
> The following bug has been logged on the website:
>
> Bug reference: 10728
> Logged by: Matti Hameister
> Email address: matti(dot)hameister(at)technologygroup(dot)de
> PostgreSQL version: 9.4beta1
> Operating system: Linux
> Description:
>
> This query:
>
> --
> SELECT X.* FROM
> json_to_record(
> '
> {"a":2,"c":3,"b":{"z":4}, "d":6}
> ',true
> ) AS X(a int, b json, c int, d int);
> --
>
> returns as expected
> a: 2
> b: {"z":4}
> c: 3
> d: 6
>
>
> Now I changed the query a bit (using recordset):
>
> --
> SELECT X.* FROM
> json_to_recordset(
> '[
> {"a":2,"c":3,"b":{"z":4}, "d":6}
> ]
> ',true
> ) AS X(a int, b json, c int, d int);
> --
>
> the result is surprising:
> a: NULL
> b: {"z":4}
> c: NULL
> d: 6
Interesting. I would have expected the same result as well. It is worth
noticing that jsonb_to_recordset works as expected:
=# SELECT X.* FROM json_to_recordset('[{"a":2,"c":3,"b":{"z":4}, "d":6}]',
true)
AS X(a int, b json, c int, d int);
a | b | c | d
------+---------+------+---
null | {"z":4} | null | 6
(1 row)
=# SELECT X.* FROM jsonb_to_recordset('[{"a":2,"c":3,"b":{"z":4}, "d":6}]',
true)
AS X(a int, b json, c int, d int);
a | b | c | d
---+----------+---+---
2 | {"z": 4} | 3 | 6
(1 row)

Digging more into it, you can see this error happens iff only one of the
fields is a json itself, and that it deletes all the values prior to it.
For example in this case a json value is set as the 3rd return element,
note that the two ones prior to it get deleted:
=# SELECT X.* FROM json_to_recordset('[{"a":2,"b":3,"c":{"z":4}, "d":6}]',
true) as X(a int, b int, c json, d int);
a | b | c | d
------+------+----------+---
null | null | {"z": 4} | 6
(1 row)
The error is as well independent on the order of the elements in the alias
clause, but in their order in the json field:
=# SELECT X.* FROM json_to_recordset('[{"a":2,"b":3,"c":{"z":4}, "d":6}]',
true)
AS X(a int, c json, b int, d int);
a | c | b | d
------+---------+------+---
null | {"z":4} | null | 6
(1 row)

Finally, the last json value deletes all the prior values, even other json:
=# SELECT X.* FROM
json_to_recordset('[{"a":2,"b":{"v":4},"c":6,"d":{"x":6},"e":7}]', true)
AS X(a int, b json, c int, d json, e int);
a | b | c | d | e
------+------+------+---------+---
null | null | null | {"x":6} | 7
(1 row)

I am guessing that the bug origin is in pg_parse_json in the way nested
json is managed, it is the only code path of populate_recordset_worker
where a switch on JSON[B]OID is used.
Regards,
--
Michael

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message digoal 2014-06-23 07:17:59 BUG #10734: PostgreSQL 9.3.4 shutdown forever in zfsonlinux 0.6.3-1 filesystem
Previous Message matti.hameister 2014-06-22 15:34:09 BUG #10728: json_to_recordset with nested json objects NULLs columns

Browse pgsql-hackers by date

  From Date Subject
Next Message Abhijit Menon-Sen 2014-06-23 05:26:28 Re: Request for Patch Feedback: Lag & Lead Window Functions Can Ignore Nulls
Previous Message Abhijit Menon-Sen 2014-06-23 05:08:09 Re: 9.5 CF1