agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19695: JSON_VALUE ... RETURNING jsonb returns NULL for later evaluation once one evaluation returns NULL
3+ messages / 2 participants
[nested] [flat]
* BUG #19695: JSON_VALUE ... RETURNING jsonb returns NULL for later evaluation once one evaluation returns NULL
@ 2026-09-18 02:45 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2026-09-18 02:45 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: chaitanyyachoudhary@gmail.com
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19695: JSON_VALUE ... RETURNING jsonb returns NULL for later evaluation once one evaluation returns NULL
@ 2026-09-18 15:03 Srinath Reddy Sadipiralla <srinath2133@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: Srinath Reddy Sadipiralla @ 2026-09-18 15:03 UTC (permalink / raw)
To: chaitanyyachoudhary@gmail.com; pgsql-bugs@lists.postgresql.org
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.
--
Thanks :)
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19695: JSON_VALUE ... RETURNING jsonb returns NULL for later evaluation once one evaluation returns NULL
@ 2026-09-19 00:58 Srinath Reddy Sadipiralla <srinath2133@gmail.com>
parent: Srinath Reddy Sadipiralla <srinath2133@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Srinath Reddy Sadipiralla @ 2026-09-19 00:58 UTC (permalink / raw)
To: chaitanyyachoudhary@gmail.com; pgsql-bugs@lists.postgresql.org
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/
Attachments:
[application/octet-stream] v1-0001-Fix-state-leakage-in-JSON_VALUE-returning-json-jsonb.patch (4.9K, ../../CAFC+b6p8Oo3_pLXPa=DVXx1XDQJ09b2W3U3WKPtkQYyh0z0z-A@mail.gmail.com/3-v1-0001-Fix-state-leakage-in-JSON_VALUE-returning-json-jsonb.patch)
download | inline diff:
From e6bccb501793daeaa4564d18b9168160aee83bc8 Mon Sep 17 00:00:00 2001
From: Srinath Reddy Sadipiralla <srinath2133@gmail.com>
Date: Sat, 19 Sep 2026 06:24:14 +0530
Subject: [PATCH 1/1] Fix state leakage in JSON_VALUE returning json/jsonb
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
If JSON_VALUE(... RETURNING json/jsonb) evaluated to NULL for a row
(e.g: due to a JSON null or missing key), all subsequent rows in the
same statement would incorrectly return NULL, even if they had valid data.
This happened because the expression executor reuses its state across rows.
In ExecEvalJsonExprPath(), the code extracted the valid string for
json and jsonb return types, but forgot to reset the *op->resnull
flag back to false. Because the stale true flag from an earlier row
was never cleared, the executor discarded the valid string and output a
SQL NULL instead.
Fix this by explicitly clearing the null flag when a valid JSON value
is processed.
---
src/backend/executor/execExprInterp.c | 1 +
.../regress/expected/sqljson_queryfuncs.out | 46 +++++++++++++++++++
src/test/regress/sql/sqljson_queryfuncs.sql | 23 ++++++++++
3 files changed, 70 insertions(+)
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)
{
diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out
index ff64dce0c59..51f7098cd5c 100644
--- a/src/test/regress/expected/sqljson_queryfuncs.out
+++ b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -554,6 +554,52 @@ select json_value('{"a": 1.234}', '$.a' returning int error on error);
ERROR: invalid input syntax for type integer: "1.234"
select json_value('{"a": "1.234"}', '$.a' returning int error on error);
ERROR: invalid input syntax for type integer: "1.234"
+-- Test state leakage of null flags in JSON_VALUE with RETURNING json/jsonb
+-- (Checks that a NULL evaluation doesn't poison subsequent evaluations in the same statement)
+-- 1. Same statement, separate columns (JSONB)
+SELECT JSON_VALUE('null', '$' RETURNING jsonb) AS col1,
+ JSON_VALUE('123', '$' RETURNING jsonb) AS col2;
+ col1 | col2
+------+------
+ | 123
+(1 row)
+
+-- 2. Across multiple rows (JSONB)
+SELECT JSON_VALUE(x, '$' RETURNING jsonb)
+FROM (VALUES ('1'::jsonb), ('null'), ('2')) v(x);
+ json_value
+------------
+ 1
+
+ 2
+(3 rows)
+
+-- 3. Same statement, separate columns (JSON)
+SELECT JSON_VALUE('null', '$' RETURNING json) AS col1,
+ JSON_VALUE('123', '$' RETURNING json) AS col2;
+ col1 | col2
+------+------
+ | 123
+(1 row)
+
+-- 4. Across multiple rows (JSON)
+SELECT JSON_VALUE(x, '$' RETURNING json)
+FROM (VALUES ('1'::json), ('null'), ('2')) v(x);
+ json_value
+------------
+ 1
+
+ 2
+(3 rows)
+
+-- 5. Triggering NULL via EMPTY/NO MATCH (JSONB)
+SELECT JSON_VALUE('{"a": 1}', '$.b' RETURNING jsonb) AS col1,
+ JSON_VALUE('{"a": 1}', '$.a' RETURNING jsonb) AS col2;
+ col1 | col2
+------+------
+ | 1
+(1 row)
+
-- JSON_QUERY
SELECT JSON_VALUE(NULL::jsonb, '$');
json_value
diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql
index a69ef253f66..e523ea78c63 100644
--- a/src/test/regress/sql/sqljson_queryfuncs.sql
+++ b/src/test/regress/sql/sqljson_queryfuncs.sql
@@ -149,6 +149,29 @@ SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +
select json_value('{"a": 1.234}', '$.a' returning int error on error);
select json_value('{"a": "1.234"}', '$.a' returning int error on error);
+-- Test state leakage of null flags in JSON_VALUE with RETURNING json/jsonb
+-- (Checks that a NULL evaluation doesn't poison subsequent evaluations in the same statement)
+
+-- 1. Same statement, separate columns (JSONB)
+SELECT JSON_VALUE('null', '$' RETURNING jsonb) AS col1,
+ JSON_VALUE('123', '$' RETURNING jsonb) AS col2;
+
+-- 2. Across multiple rows (JSONB)
+SELECT JSON_VALUE(x, '$' RETURNING jsonb)
+FROM (VALUES ('1'::jsonb), ('null'), ('2')) v(x);
+
+-- 3. Same statement, separate columns (JSON)
+SELECT JSON_VALUE('null', '$' RETURNING json) AS col1,
+ JSON_VALUE('123', '$' RETURNING json) AS col2;
+
+-- 4. Across multiple rows (JSON)
+SELECT JSON_VALUE(x, '$' RETURNING json)
+FROM (VALUES ('1'::json), ('null'), ('2')) v(x);
+
+-- 5. Triggering NULL via EMPTY/NO MATCH (JSONB)
+SELECT JSON_VALUE('{"a": 1}', '$.b' RETURNING jsonb) AS col1,
+ JSON_VALUE('{"a": 1}', '$.a' RETURNING jsonb) AS col2;
+
-- JSON_QUERY
SELECT JSON_VALUE(NULL::jsonb, '$');
--
2.43.0
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-09-19 00:58 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-18 02:45 BUG #19695: JSON_VALUE ... RETURNING jsonb returns NULL for later evaluation once one evaluation returns NULL PG Bug reporting form <noreply@postgresql.org>
2026-09-18 15:03 ` Srinath Reddy Sadipiralla <srinath2133@gmail.com>
2026-09-19 00:58 ` Srinath Reddy Sadipiralla <srinath2133@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox