jsonb subscript operator returns null when key is fetched from table

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.

"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