Re: BUG #19695: JSON_VALUE ... RETURNING jsonb returns NULL for later evaluation once one evaluation returns NULL

Hi,

On Fri, Sep 18, 2026 at 6:47 PM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 19695
> Logged by: Chaitanya Choudhary
> Email address: chaitanyyachoudhary(at)gmail(dot)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.
>

Thanks for the detailed report. I looked into this and the fix is actually
quite straightforward,
hough the root cause is slightly different from the initial analysis.
Regarding the notes on the cause: the evaluation doesn't actually run
through ExecEvalJsonCoercion().
Because json and jsonb are base types rather than domain types,
use_json_coercion evaluates to false.
The actual state leakage occurs inside ExecEvalJsonExprPath(). Because the
ExprEvalStep *op structure
is initialized once per statement and reused across rows, its memory slots
persist. When an earlier row
yields a SQL NULL (setting *op->resnull = true), that flag stays true for
the next row. The block handling
JSONOID and JSONBOID computes the correct string but simply forgets to
reset *op->resnull = false,
causing the executor to treat the valid result as NULL.

Applying the below diff fixes the issue by explicitly clearing the null
flag:

diff --git a/src/backend/executor/execExprInterp.c
b/src/backend/executor/execExprInterp.c
index 397219f7a3a..bfcf13769ca 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -4979,6 +4979,7 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep
*op,
{
val_string =
DatumGetCString(DirectFunctionCall1(jsonb_out,

JsonbPGetDatum(JsonbValueToJsonb(jbv))));
+ *op->resnull = false;
}
else if (jsexpr->use_json_coercion)
{

If this approach makes sense, I can write up a formal patch along with the
appropriate regression test.

--
Thanks :)
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/