Horton

Thread

Commits

  1. Fix jsonb subscripting to cope with toasted subscript values.

  1. jsonb subscript operator returns null when key is fetched from table

    exe-dealer <exe-dealer@yandex.ru> — 2022-12-09T20:06:51Z

    <div><div><div>jsonb subscript operator returns null when key is fetched from table.</div><div> </div></div><div>PostgreSQL 15.1 on x86_64-pc-linux-musl, compiled by gcc (Alpine 12.2.1_git20220924-r4) 12.2.1 20220924, 64-bit</div><div> </div><div><span style="font-family:'courier new' , monospace">WITH cte AS MATERIALIZED (SELECT 'foo' k, '{"foo":"bar"}'::jsonb j)</span></div><div><span style="font-family:'courier new' , monospace">SELECT j[k], j[k || ''] FROM cte;</span></div><div> </div><div>-- returns</div><div>-- null, "bar"</div><div> </div><div>-- expected</div><div>-- "bar", "bar"</div><div> </div></div>

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

    David G. Johnston <david.g.johnston@gmail.com> — 2022-12-12T18:51:19Z

    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.

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2022-12-12T19:09:21Z

    "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