BUG #19695: JSON_VALUE ... RETURNING jsonb returns NULL for later evaluation once one evaluation returns NULL
BUG #19695: JSON_VALUE ... RETURNING jsonb returns NULL for later evaluation once one evaluation returns NULL
От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
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.
Re: BUG #19695: JSON_VALUE ... RETURNING jsonb returns NULL for later evaluation once one evaluation returns NULL
От:
Srinath Reddy Sadipiralla <srinath2133@gmail.com>
Дата:
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) 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.
Re: BUG #19695: JSON_VALUE ... RETURNING jsonb returns NULL for later evaluation once one evaluation returns NULL
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.
Re: BUG #19695: JSON_VALUE ... RETURNING jsonb returns NULL for later evaluation once one evaluation returns NULL
От:
Manu <manuelreyesbravo@gmail.com>
Дата:
Hi Srinath, I tested your v1 on master, REL_18_STABLE and REL_17_STABLE, together with Andrey's v2 for #19621 [1], which touches the same function. Both apply cleanly on all three branches, build without warnings, and the main regression suite passes. On all three, JSON_VALUE(x, '$' RETURNING jsonb) over '1', 'null', '2' now returns 1, NULL, 2 (it was 1, NULL, NULL). The fix looks right to me: that branch fills val_string but never cleared the resnull left by an earlier row. A small thing: git apply warns about trailing whitespace on four lines of the new test in sqljson_queryfuncs.sql. [1] https://postgr.es/m/CAB8bMitawD=ERVLwYuDXvMT_OJqf+qAKx=3GEH6V_WHT7jnq7A@mail.gmail.com Regards, Manu
Re: BUG #19695: JSON_VALUE ... RETURNING jsonb returns NULL for later evaluation once one evaluation returns NULL
Hi Manu,
On Thu, Sep 24, 2026 at 1:42 AM Manu <manuelreyesbravo@gmail.com> wrote:
Hi Srinath,
I tested your v1 on master, REL_18_STABLE and REL_17_STABLE, together
with Andrey's v2 for #19621 [1], which touches the same function. Both
apply cleanly on all three branches, build without warnings, and the
main regression suite passes. On all three,
JSON_VALUE(x, '$' RETURNING jsonb) over '1', 'null', '2' now returns
1, NULL, 2 (it was 1, NULL, NULL).
The fix looks right to me: that branch fills val_string but never
cleared the resnull left by an earlier row.
A small thing: git apply warns about trailing whitespace on four lines
of the new test in sqljson_queryfuncs.sql.
[1]
https://postgr.es/m/CAB8bMitawD=ERVLwYuDXvMT_OJqf+qAKx=3GEH6V_WHT7jnq7A@mail.gmail.com
Thanks for the review; I also looked into the pointed thread
and shared my feedback there, and I fixed the whitespace errors
in this v2 patch.
Re: BUG #19695: JSON_VALUE ... RETURNING jsonb returns NULL for later evaluation once one evaluation returns NULL
Hi Srinath, > I fixed the whitespace errors in this v2 patch. Thanks. v2 removes the trailing spaces from the four queries in sqljson_queryfuncs.sql, but not from the same four lines in expected/sqljson_queryfuncs.out, which echoes them, so on master (89829354de1) the test now fails on whitespace alone. The regression.diffs is attached; the query results themselves are right. With the trailing spaces removed from those four lines of the .out as well, the test passes. Sorry for the extra round, it was my nit. Regards, Manu
Re: BUG #19695: JSON_VALUE ... RETURNING jsonb returns NULL for later evaluation once one evaluation returns NULL
On Fri, Sep 25, 2026 at 5:49 AM Manu wrote: > > Hi Srinath, > > > I fixed the whitespace errors in this v2 patch. > > Thanks. v2 removes the trailing spaces from the four queries in > sqljson_queryfuncs.sql, but not from the same four lines in > expected/sqljson_queryfuncs.out, which echoes them, so on master > (89829354de1) the test now fails on whitespace alone. The > regression.diffs is attached; the query results themselves are right. > > With the trailing spaces removed from those four lines of the .out as > well, the test passes. Sorry for the extra round, it was my nit. Thank you all for working on this. That is indeed an oversight on my part. I'll push the attached patch in a bit. It’s mostly Srinath’s v2 with some test adjustments and some commit message polishing. -- Thanks, Amit Langote