Thread: BUG #18583: jsonb_populate_record return values cannot be queried correctly in subselects

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

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:       

create type test_simple_type as (
        f1 varchar(20),
        f2 int
);

where val is not null;

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.