Postgres Pro
Downloads
Home   >   mailing lists

Thread: Re: jsonb subscript operator returns null when key is fetched from table

Re: jsonb subscript operator returns null when key is fetched from table

From
"David G. Johnston"
Date:
On Fri, Dec 9, 2022 at 1:35 PM exe-dealer <exe-dealer@yandex.ru> wrote:
jsonb subscript operator returns null when key is fetched from table.
 
PostgreSQL 15.1 on x86_64-pc-linux-musl, compiled by gcc (Alpine 12.2.1_git20220924-r4) 12.2.1 20220924, 64-bit
 
WITH cte AS MATERIALIZED (SELECT 'foo' k, '{"foo":"bar"}'::jsonb j)
SELECT j[k], j[k || ''] FROM cte;
 
-- returns
-- null, "bar"
 
-- expected
-- "bar", "bar"

Thanks for the report.  I can confirm that this is a problem in both v14 and v15.

To clarify the subject line - the issue manifests only if the CTE is MATERIALIZED.

Someone more experienced than I will need to actually do the debugging work though; hoping this reply will be seen by one of them.

David J.

Re: jsonb subscript operator returns null when key is fetched from table

From
Tom Lane
Date:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> Someone more experienced than I will need to actually do the debugging work
> though; hoping this reply will be seen by one of them.

Meh.  Somebody was cavalier about detoasting the subscript:

            jbvp = getKeyJsonValueFromContainer(container,
                                                VARDATA(path[i]),
                                                VARSIZE(path[i]) - VARHDRSZ,
                                                NULL);

Neither VARDATA nor VARSIZE are valid to apply to an arbitrary datum.
Apparently, nobody has ever tested this with a subscript value fetched
straight from disk :-(.  The given example accidentally works with
a NOT MATERIALIZED CTE because the whole thing gets stepwise folded
to constants.  Likewise, the expression with || accidentally works
because concatenation always yields a non-toasted result.

            regards, tom lane