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.
