BUG #19695: JSON_VALUE ... RETURNING jsonb returns NULL for later evaluation once one evaluation returns NULL
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253842psql -h localhost -U postgresBuilt from patchset v3 (message #3), September 20, 2026 at 01:11 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253842_3 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253842_3 && git checkout t253842_3Patchset v3 (message #3) is on t253842_3
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.
Hi,
On Fri, Sep 18, 2026 at 6:47 PM PG Bug reporting form <
noreply@postgresql.org> wrote:
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) correctSELECT JSON_VALUE('null', '$' RETURNING jsonb),
JSON_VALUE('123', '$' RETURNING jsonb);
-- (null) | (null) expected (null) | 123The 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), 2SELECT JSON_VALUE(x, '$' RETURNING jsonb)
FROM (VALUES ('1'::jsonb), ('2')) v(x);
-- 1, 2 correct: no NULL came firstRETURNING int is not affected:
SELECT JSON_VALUE('null', '$' RETURNING int),
JSON_VALUE('123', '$' RETURNING int);
-- (null) | 123A 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/
On Fri, Sep 18, 2026 at 8:33 PM Srinath Reddy Sadipiralla <
srinath2133@gmail.com> wrote:
If this approach makes sense, I can write up a formal patch along with the
appropriate regression test.
Here's the patch with tests.
--
Thanks :)
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/