From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: robert.j.greig@gmail.com
Subject: BUG #18583: jsonb_populate_record return values cannot be queried correctly in subselects
Date: Wed, 14 Aug 2024 14:53:42 +0000
Message-ID: <18583-b8f0198539f29ccb@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 18583
Logged by: Robert Greig
Email address: robert.j.greig@gmail.com
PostgreSQL version: 16.4
Operating system: Windows
Description:
I have created a small example that illustrates this issue, but before
describing that let me provide a little more context which would not be
obvious from the example. The real use case here is that we have a number of
tables containing some columns with type jsonb. There are views that select
from those tables and use jsonb_populate_record to populate custom types
from the JSON. For example:
CREATE TABLE t1 AS (id varchar(20), some_data jsonb);
CREATE VIEW v1 AS SELECT id, jsonb_populate(null::some_type, somedata) AS
data FROM t1;
However this was not working in some circumstances where rows that contained
non-null values in the data column of the view were not being returned by a
query of the form SELECT * FROM v1 WHERE data IS NOT NULL. However just
running the SELECT statement without any WHERE clause you could see the data
was being returned.
Further testing showed that this was happening with any subselect not just
views, and that the issue was occurring when there were any null or missing
elements in the JSON. The type was still constructed as expected but for
some reason the query on it was failing.
I am sure this is as clear as mud so I have put together a very simple test
case that illustrates the issue.
create type test_simple_type as (
f1 varchar(20),
f2 int
);
select
*
from (
values
(10, (jsonb_populate_record(null::test_simple_type, '{"f1": "banana",
"f2": 44}')::test_simple_type)),
(11, (jsonb_populate_record(null::test_simple_type, '{"f1": "pear", "f2":
null}')::test_simple_type)),
(12, (jsonb_populate_record(null::test_simple_type, '{"f1":
"strawberry"}')::test_simple_type)),
(13, null)
) x(key, val);
select
*
from (
values
(10, (jsonb_populate_record(null::test_simple_type, '{"f1": "banana",
"f2": 44}')::test_simple_type)),
(11, (jsonb_populate_record(null::test_simple_type, '{"f1": "pear", "f2":
null}')::test_simple_type)),
(12, (jsonb_populate_record(null::test_simple_type, '{"f1":
"strawberry"}')::test_simple_type)),
(13, null)
) x(key, val)
where val is not null;
If you run the first query above, you can see that the val column is
correctly populated for the rows with key 10, 11 and 12. However if you run
the second query only one row is returned whereas the expected output is
three rows (with keys 10, 11 and 12).
It is not obvious to me why it is not working but I believe this is a defect
and I can't find a workaround.
Thanks,
Robert
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, robert.j.greig@gmail.com
Subject: Re: BUG #18583: jsonb_populate_record return values cannot be queried correctly in subselects
In-Reply-To: <18583-b8f0198539f29ccb@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox