Re: BUG #19695: JSON_VALUE ... RETURNING jsonb returns NULL for later evaluation once one evaluation returns NULL - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      19695
Logged by:          Chaitanya Choudhary
Email address:      chaitanyyachoudhary@gmail.com
PostgreSQL version: 18.6
Operating system:   macOS 26 (aarch64), Homebrew build of 18.6
Description:       

Within one statement, after a JSON_VALUE(... RETURNING jsonb) or
RETURNING json evaluation yields SQL NULL, every later evaluation of that
expression in the same statement also yields NULL, even when the input has
a value. Other RETURNING types are not affected.

Steps to reproduce:

  SELECT JSON_VALUE('123', '$' RETURNING jsonb),
         JSON_VALUE('null', '$' RETURNING jsonb);
  -- 123 | (null)     correct

  SELECT JSON_VALUE('null', '$' RETURNING jsonb),
         JSON_VALUE('123', '$' RETURNING jsonb);
  -- (null) | (null)  expected (null) | 123

The same across rows of a scan:

  SELECT JSON_VALUE(x, '$' RETURNING jsonb)
  FROM (VALUES ('1'::jsonb), ('null'), ('2')) v(x);
  -- 1, (null), (null)   expected 1, (null), 2

  SELECT JSON_VALUE(x, '$' RETURNING jsonb)
  FROM (VALUES ('1'::jsonb), ('2')) v(x);
  -- 1, 2                correct: no NULL came first

RETURNING int is not affected:

  SELECT JSON_VALUE('null', '$' RETURNING int),
         JSON_VALUE('123', '$' RETURNING int);
  -- (null) | 123

A NULL produced by a JSON null item, by EMPTY (no match), or by an error
converted to NULL under NULL ON ERROR all trigger it. RETURNING json
behaves like RETURNING jsonb. JSON_QUERY and JSON_EXISTS are not affected.

Notes on the cause:

The result depends on what earlier rows or earlier calls in the same
statement returned, so some state persists across evaluations of the
expression. The RETURNING json/jsonb coercion runs through
ExecEvalJsonCoercion() in src/backend/executor/execExprInterp.c, which
calls json_populate_type() with a per-expression cache
(op->d.jsonexpr_coercion.json_coercion_cache) and with op->resnull passed
by pointer as the isnull argument. That cache lives for the statement and
is the only state shared between the evaluations. I have not traced the
exact line where the null is retained.

The code involved is unchanged between 18.6 and master as of 2026-09-17.