pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19621: Unexpected results of JSON_VALUE with DEFAULT ON EMPTY
11+ messages / 6 participants
[nested] [flat]
* BUG #19621: Unexpected results of JSON_VALUE with DEFAULT ON EMPTY
@ 2026-08-15 03:25 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 11+ messages in thread
From: PG Bug reporting form @ 2026-08-15 03:25 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: syzhong16@gmail.com
The following bug has been logged on the website:
Bug reference: 19621
Logged by: Suyang Zhong
Email address: syzhong16@gmail.com
PostgreSQL version: 19beta3
Operating system: Ubuntu 22.04
Description:
Consider the following test case:
```sql
CREATE TABLE t0(x text);
INSERT INTO t0 VALUES ('{}'), (NULL);
CREATE VIEW v0 AS
SELECT x, json_value(x, '$.a' RETURNING int DEFAULT 42 ON EMPTY) AS jv
FROM t0;
SELECT x IS NULL AS is_null, jv FROM v0;
-- f | 42
-- t | 42
SELECT jv FROM v0 WHERE x IS NULL;
-- NULL
```
The second query selects exactly the row the first query shows as `t | 42`,
yet returns NULL for its `jv`.
A further reduction shows that the result depends on the input row order.
```sql
SELECT json_value(x, '$.a' RETURNING int DEFAULT 42 ON EMPTY) FROM (VALUES
('{}'), (NULL)) v(x);
-- 42
-- 42
SELECT json_value(x, '$.a' RETURNING int DEFAULT 42 ON EMPTY) FROM (VALUES
(NULL), ('{}')) v(x);
-- NULL
-- 42
```
Reproduced on 20devel, and on 17.11, 18.1, 18.4, and 19beta3; on 17rc1 only
the `ON ERROR` variants misbehave.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19621: Unexpected results of JSON_VALUE with DEFAULT ON EMPTY
@ 2026-08-16 14:29 Andrey Rachitskiy <pl0h0yp1@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 11+ messages in thread
From: Andrey Rachitskiy @ 2026-08-16 14:29 UTC (permalink / raw)
To: syzhong16@gmail.com; pgsql-bugs@lists.postgresql.org; +Cc: Amit Langote <amitlangote09@gmail.com>
вс, 16 авг. 2026 г. в 17:36, PG Bug reporting form <noreply@postgresql.org>:
> Consider the following test case:
>
> ```sql
> CREATE TABLE t0(x text);
> INSERT INTO t0 VALUES ('{}'), (NULL);
> CREATE VIEW v0 AS
> SELECT x, json_value(x, '$.a' RETURNING int DEFAULT 42 ON EMPTY) AS jv
> FROM t0;
>
> SELECT x IS NULL AS is_null, jv FROM v0;
> -- f | 42
> -- t | 42
>
> SELECT jv FROM v0 WHERE x IS NULL;
> -- NULL
> ```
>
> The second query selects exactly the row the first query shows as `t | 42`,
> yet returns NULL for its `jv`.
>
> A further reduction shows that the result depends on the input row order.
>
> ```sql
> SELECT json_value(x, '$.a' RETURNING int DEFAULT 42 ON EMPTY) FROM (VALUES
> ('{}'), (NULL)) v(x);
> -- 42
> -- 42
> SELECT json_value(x, '$.a' RETURNING int DEFAULT 42 ON EMPTY) FROM (VALUES
> (NULL), ('{}')) v(x);
> -- NULL
> -- 42
> ```
>
> Reproduced on 20devel, and on 17.11, 18.1, 18.4, and 19beta3; on 17rc1 only
> the `ON ERROR` variants misbehave.
>
> Hi, Suyang!
Thanks for the report!
On NULL input, jsonpath is deliberately not run: there is nothing to
search, so the result is NULL (a NOT NULL domain still checks the NULL).
The "found empty" / "had an error" flags were cleared only in the step
that runs jsonpath. NULL skips that step, so the previous row's flags
remain. The next check is "empty? then DEFAULT" — and it fires for the
wrong row.
This dates to SQL/JSON itself (6185c973, March 2024): NULL skips path
evaluation, the reset lived inside that evaluation. Later (dd8bea88abf)
the extra steps were omitted for the default "just return NULL", so the
bug stayed hidden. A non-NULL DEFAULT (42, TRUE ON ERROR) keeps those
steps — the bug shows. On 17rc1 the reporter mostly saw ON ERROR: ON
EMPTY DEFAULT did not always go through the same path yet.
Proposal fix
-----------
Clear "empty/error" at the start of each row, before deciding not to
run the path on NULL. NULL still does not run the path. The only change
is that another row's DEFAULT no longer sticks.
CC Amit Langote (JSON).
Attachments:
[text/x-patch] 0001-Reset-JsonExpr-empty-error-flags-before-NULL-short-circuit.patch (10.0K, ../../CAB8bMisC9N06fQbJ0ie02Qzv5mO4K8rA=_Gb0UFzMZLCwft4QQ@mail.gmail.com/3-0001-Reset-JsonExpr-empty-error-flags-before-NULL-short-circuit.patch)
download | inline diff:
From: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Date: Sun, 16 Aug 2026 19:24:00 +0500
Subject: [PATCH] Reset JsonExpr empty/error flags before NULL short-circuit
SQL NULL context/path skips EEOP_JSONEXPR_PATH (JUMP_IF_NULL to CONST NULL)
so jsonpath is not evaluated, then falls through to domain coercion and
ON EMPTY / ON ERROR checks. empty/error were cleared only inside PATH,
so a previous row's EMPTY/ERROR leaked onto the NULL row when DEFAULT was
not NULL.
Clear the flags in a new EEOP_JSONEXPR_RESET at the start of each JsonExpr
evaluation.
BUG #19621
Reported-by: Suyang Zhong <syzhong16@gmail.com>
Author: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Discussion: https://www.postgresql.org/message-id/19621-0a480d2dc74e6bd5%40postgresql.org
Backpatch-through: 17
---
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index cfea7e160c2..a3050576cb3 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -4759,6 +4759,11 @@ ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
jsestate->jsexpr = jsexpr;
+ /* Clear empty/error here. SQL NULL skips PATH. */
+ scratch->opcode = EEOP_JSONEXPR_RESET;
+ scratch->d.jsonexpr.jsestate = jsestate;
+ ExprEvalPushStep(state, scratch);
+
/*
* Evaluate formatted_expr storing the result into
* jsestate->formatted_expr.
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 9bc23cb16fa..a82f9ee11a5 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -578,6 +578,7 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
&&CASE_EEOP_XMLEXPR,
&&CASE_EEOP_JSON_CONSTRUCTOR,
&&CASE_EEOP_IS_JSON,
+ &&CASE_EEOP_JSONEXPR_RESET,
&&CASE_EEOP_JSONEXPR_PATH,
&&CASE_EEOP_JSONEXPR_COERCION,
&&CASE_EEOP_JSONEXPR_COERCION_FINISH,
@@ -1936,6 +1937,13 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
EEO_NEXT();
}
+ EEO_CASE(EEOP_JSONEXPR_RESET)
+ {
+ ExecEvalJsonExprReset(state, op);
+
+ EEO_NEXT();
+ }
+
EEO_CASE(EEOP_JSONEXPR_PATH)
{
/* too complex for an inline implementation */
@@ -4890,6 +4898,28 @@ ExecEvalJsonIsPredicate(ExprState *state, ExprEvalStep *op)
*op->resvalue = BoolGetDatum(res);
}
+/*
+ * ExecEvalJsonExprReset
+ * Clear empty/error and ErrorSaveContext for this evaluation.
+ *
+ * SQL NULL skips EEOP_JSONEXPR_PATH, so this cannot live there.
+ */
+void
+ExecEvalJsonExprReset(ExprState *state, ExprEvalStep *op)
+{
+ JsonExprState *jsestate = op->d.jsonexpr.jsestate;
+
+ memset(&jsestate->error, 0, sizeof(NullableDatum));
+ memset(&jsestate->empty, 0, sizeof(NullableDatum));
+
+ if (jsestate->escontext.details_wanted)
+ {
+ jsestate->escontext.error_data = NULL;
+ jsestate->escontext.details_wanted = false;
+ }
+ jsestate->escontext.error_occurred = false;
+}
+
/*
* Evaluate a jsonpath against a document, both of which must have been
* evaluated and their values saved in op->d.jsonexpr.jsestate.
@@ -4922,18 +4952,6 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
item = jsestate->formatted_expr.value;
path = DatumGetJsonPathP(jsestate->pathspec.value);
- /* Set error/empty to false. */
- memset(&jsestate->error, 0, sizeof(NullableDatum));
- memset(&jsestate->empty, 0, sizeof(NullableDatum));
-
- /* Also reset ErrorSaveContext contents for the next row. */
- if (jsestate->escontext.details_wanted)
- {
- jsestate->escontext.error_data = NULL;
- jsestate->escontext.details_wanted = false;
- }
- jsestate->escontext.error_occurred = false;
-
switch (jsexpr->op)
{
case JSON_EXISTS_OP:
diff --git a/src/backend/jit/llvm/llvmjit_expr.c b/src/backend/jit/llvm/llvmjit_expr.c
index 29617437477..a96c09afe23 100644
--- a/src/backend/jit/llvm/llvmjit_expr.c
+++ b/src/backend/jit/llvm/llvmjit_expr.c
@@ -2259,6 +2259,12 @@ llvm_compile_expr(ExprState *state)
LLVMBuildBr(b, opblocks[opno + 1]);
break;
+ case EEOP_JSONEXPR_RESET:
+ build_EvalXFunc(b, mod, "ExecEvalJsonExprReset",
+ v_state, op);
+ LLVMBuildBr(b, opblocks[opno + 1]);
+ break;
+
case EEOP_JSONEXPR_PATH:
{
JsonExprState *jsestate = op->d.jsonexpr.jsestate;
diff --git a/src/backend/jit/llvm/llvmjit_types.c b/src/backend/jit/llvm/llvmjit_types.c
index c8a1f841293..654600da5b0 100644
--- a/src/backend/jit/llvm/llvmjit_types.c
+++ b/src/backend/jit/llvm/llvmjit_types.c
@@ -173,6 +173,7 @@ void *referenced_functions[] =
ExecEvalXmlExpr,
ExecEvalJsonConstructor,
ExecEvalJsonIsPredicate,
+ ExecEvalJsonExprReset,
ExecEvalJsonCoercion,
ExecEvalJsonCoercionFinish,
ExecEvalJsonExprPath,
diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h
index c61b3d624d5..8afc09d5dfa 100644
--- a/src/include/executor/execExpr.h
+++ b/src/include/executor/execExpr.h
@@ -265,6 +265,7 @@ typedef enum ExprEvalOp
EEOP_XMLEXPR,
EEOP_JSON_CONSTRUCTOR,
EEOP_IS_JSON,
+ EEOP_JSONEXPR_RESET,
EEOP_JSONEXPR_PATH,
EEOP_JSONEXPR_COERCION,
EEOP_JSONEXPR_COERCION_FINISH,
@@ -754,7 +755,7 @@ typedef struct ExprEvalStep
JsonIsPredicate *pred; /* original expression node */
} is_json;
- /* for EEOP_JSONEXPR_PATH */
+ /* for EEOP_JSONEXPR_RESET, EEOP_JSONEXPR_PATH, EEOP_JSONEXPR_COERCION_FINISH */
struct
{
struct JsonExprState *jsestate;
@@ -892,6 +893,7 @@ extern void ExecEvalXmlExpr(ExprState *state, ExprEvalStep *op);
extern void ExecEvalJsonConstructor(ExprState *state, ExprEvalStep *op,
ExprContext *econtext);
extern void ExecEvalJsonIsPredicate(ExprState *state, ExprEvalStep *op);
+extern void ExecEvalJsonExprReset(ExprState *state, ExprEvalStep *op);
extern int ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
ExprContext *econtext);
extern void ExecEvalJsonCoercion(ExprState *state, ExprEvalStep *op,
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index e95ac3eda35..fc8c6e21ad2 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -1099,7 +1099,7 @@ typedef struct DomainConstraintState
* State for JsonExpr evaluation, too big to inline.
*
* This contains the information going into and coming out of the
- * EEOP_JSONEXPR_PATH eval step.
+ * EEOP_JSONEXPR_RESET / EEOP_JSONEXPR_PATH eval steps.
*/
typedef struct JsonExprState
{
@@ -1119,7 +1119,7 @@ typedef struct JsonExprState
* Output variables that drive the EEOP_JUMP_IF_NOT_TRUE steps that are
* added for ON ERROR and ON EMPTY expressions, if any.
*
- * Reset for each evaluation of EEOP_JSONEXPR_PATH.
+ * Cleared by EEOP_JSONEXPR_RESET at the start of each evaluation.
*/
/* Set to true if jsonpath evaluation cause an error. */
@@ -1161,7 +1161,7 @@ typedef struct JsonExprState
* not ERROR, a pointer to this is passed to ExecInitExprRec() when
* initializing the coercion expressions or to ExecInitJsonCoercion().
*
- * Reset for each evaluation of EEOP_JSONEXPR_PATH.
+ * Reset by EEOP_JSONEXPR_RESET at the start of each evaluation.
*/
ErrorSaveContext escontext;
} JsonExprState;
diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out
index ff64dce0c59..b5209bae5e7 100644
--- a/src/test/regress/expected/sqljson_queryfuncs.out
+++ b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -475,6 +475,57 @@ FROM
2 | -1
(3 rows)
+-- SQL NULL must not reuse a previous row's ON EMPTY / ON ERROR result.
+SELECT x IS NULL AS is_null,
+ json_value(x, '$.a' RETURNING int DEFAULT 42 ON EMPTY) AS jv
+FROM (VALUES ('{}'), (NULL), ('{}')) v(x);
+ is_null | jv
+---------+----
+ f | 42
+ t |
+ f | 42
+(3 rows)
+
+SELECT x IS NULL AS is_null,
+ json_value(x, 'strict $.a' RETURNING int DEFAULT 42 ON ERROR) AS jv
+FROM (VALUES ('1'), (NULL), ('1')) v(x);
+ is_null | jv
+---------+----
+ f | 42
+ t |
+ f | 42
+(3 rows)
+
+SELECT p IS NULL AS is_null,
+ json_value('{}', p RETURNING int DEFAULT 42 ON EMPTY) AS jv
+FROM (VALUES ('$.a'::jsonpath), (NULL), ('$.a'::jsonpath)) v(p);
+ is_null | jv
+---------+----
+ f | 42
+ t |
+ f | 42
+(3 rows)
+
+SELECT x IS NULL AS is_null,
+ json_query(x, '$.a' DEFAULT '"empty"' ON EMPTY) AS jq
+FROM (VALUES ('{}'::jsonb), (NULL), ('{}'::jsonb)) v(x);
+ is_null | jq
+---------+---------
+ f | "empty"
+ t |
+ f | "empty"
+(3 rows)
+
+SELECT x IS NULL AS is_null,
+ json_exists(x, 'strict $.a' TRUE ON ERROR) AS je
+FROM (VALUES ('1'::jsonb), (NULL), ('1'::jsonb)) v(x);
+ is_null | je
+---------+----
+ f | t
+ t |
+ f | t
+(3 rows)
+
SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a);
json_value
------------
diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql
index a69ef253f66..03f6a932b9c 100644
--- a/src/test/regress/sql/sqljson_queryfuncs.sql
+++ b/src/test/regress/sql/sqljson_queryfuncs.sql
@@ -128,6 +128,27 @@ SELECT
FROM
generate_series(0, 2) x;
+-- SQL NULL must not reuse a previous row's ON EMPTY / ON ERROR result.
+SELECT x IS NULL AS is_null,
+ json_value(x, '$.a' RETURNING int DEFAULT 42 ON EMPTY) AS jv
+FROM (VALUES ('{}'), (NULL), ('{}')) v(x);
+
+SELECT x IS NULL AS is_null,
+ json_value(x, 'strict $.a' RETURNING int DEFAULT 42 ON ERROR) AS jv
+FROM (VALUES ('1'), (NULL), ('1')) v(x);
+
+SELECT p IS NULL AS is_null,
+ json_value('{}', p RETURNING int DEFAULT 42 ON EMPTY) AS jv
+FROM (VALUES ('$.a'::jsonpath), (NULL), ('$.a'::jsonpath)) v(p);
+
+SELECT x IS NULL AS is_null,
+ json_query(x, '$.a' DEFAULT '"empty"' ON EMPTY) AS jq
+FROM (VALUES ('{}'::jsonb), (NULL), ('{}'::jsonb)) v(x);
+
+SELECT x IS NULL AS is_null,
+ json_exists(x, 'strict $.a' TRUE ON ERROR) AS je
+FROM (VALUES ('1'::jsonb), (NULL), ('1'::jsonb)) v(x);
+
SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a);
SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a RETURNING point);
SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a RETURNING point ERROR ON ERROR);
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19621: Unexpected results of JSON_VALUE with DEFAULT ON EMPTY
@ 2026-08-18 02:06 =?ISO-8859-1?B?emVuZ21hbg==?= <zengman@halodbtech.com>
parent: Andrey Rachitskiy <pl0h0yp1@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: zengman @ 2026-08-18 02:06 UTC (permalink / raw)
To: Andrey Rachitskiy <pl0h0yp1@gmail.com>; syzhong16 <syzhong16@gmail.com>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>; +Cc: Amit Langote <amitlangote09@gmail.com>
Hi everyone,
I have another question related to JSON functions. Although it is a different issue, I wonder whether it would make sense to handle both cases in the same patch.
I tested the current patch, but it does not seem to address the problem I reported here:
```
https://www.postgresql.org/message-id/19625-683b498c92087bc8%40postgresql.org
```
--
Regards,
Man Zeng
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19621: Unexpected results of JSON_VALUE with DEFAULT ON EMPTY
@ 2026-08-18 08:56 Andrey Rachitskiy <pl0h0yp1@gmail.com>
parent: =?ISO-8859-1?B?emVuZ21hbg==?= <zengman@halodbtech.com>
0 siblings, 1 reply; 11+ messages in thread
From: Andrey Rachitskiy @ 2026-08-18 08:56 UTC (permalink / raw)
To: zengman <zengman@halodbtech.com>; +Cc: syzhong16 <syzhong16@gmail.com>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>; Amit Langote <amitlangote09@gmail.com>
вт, 18 авг. 2026 г. в 07:06, zengman <zengman@halodbtech.com>:
> I tested the current patch, but it does not seem to address the problem I
> reported here:
> ```
>
> https://www.postgresql.org/message-id/19625-683b498c92087bc8%40postgresql.org
> ```
>
Dear Zeng,
One is per-row executor state. The other is a wrong parse-time rewrite of a
boolean DEFAULT. They share only that both involve SQL/JSON DEFAULT.
Combining them would mix an executor opcode change with a parser coercion
change, and it would make review and back-patching harder.
--
Regards,
Rachitskiy Andrey
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19621: Unexpected results of JSON_VALUE with DEFAULT ON EMPTY
@ 2026-09-04 10:38 Andrey Rachitskiy <pl0h0yp1@gmail.com>
parent: Andrey Rachitskiy <pl0h0yp1@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: Andrey Rachitskiy @ 2026-09-04 10:38 UTC (permalink / raw)
To: zengman <zengman@halodbtech.com>; +Cc: syzhong16 <syzhong16@gmail.com>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>; Amit Langote <amitlangote09@gmail.com>
вт, 18 авг. 2026 г. в 13:56, Andrey Rachitskiy <pl0h0yp1@gmail.com>:
>
> вт, 18 авг. 2026 г. в 07:06, zengman <zengman@halodbtech.com>:
>
>> I tested the current patch, but it does not seem to address the problem I
>> reported here:
>> ```
>>
>> https://www.postgresql.org/message-id/19625-683b498c92087bc8%40postgresql.org
>> ```
>>
> Dear Zeng,
>
> One is per-row executor state. The other is a wrong parse-time rewrite of
> a boolean DEFAULT. They share only that both involve SQL/JSON DEFAULT.
> Combining them would mix an executor opcode change with a parser coercion
> change, and it would make review and back-patching harder.
>
>
Dear Amit,
Attached is v2 of the patch.
v1 was a malformed unified diff: three context lines after the
ExecEvalJsonIsPredicate hunk were missing the leading space, so
git apply rejected the file as corrupt. There is no code change
versus v1.
I also re-checked the reporter's JSON_EXISTS / JSON_VALUE /
JSON_QUERY examples from the later duplicate report [0]; they pass
with this patch.
[0]
https://www.postgresql.org/message-id/19654-3acd06154d027634@postgresql.org
--
Regards,
Rachitskiy Andrey
Attachments:
[text/x-patch] v2-0001-Reset-JsonExpr-empty-error-flags-before-NULL-short-circuit.patch (10.2K, ../../CAB8bMitawD=ERVLwYuDXvMT_OJqf+qAKx=3GEH6V_WHT7jnq7A@mail.gmail.com/3-v2-0001-Reset-JsonExpr-empty-error-flags-before-NULL-short-circuit.patch)
download | inline diff:
From: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Date: Sun, 16 Aug 2026 19:24:00 +0500
Subject: [PATCH v2] Reset JsonExpr empty/error flags before NULL short-circuit
SQL NULL context/path skips EEOP_JSONEXPR_PATH (JUMP_IF_NULL to CONST NULL)
so jsonpath is not evaluated, then falls through to domain coercion and
ON EMPTY / ON ERROR checks. empty/error were cleared only inside PATH,
so a previous row's EMPTY/ERROR leaked onto the NULL row when DEFAULT was
not NULL.
Clear the flags in a new EEOP_JSONEXPR_RESET at the start of each JsonExpr
evaluation.
BUG #19621
Reported-by: Suyang Zhong <syzhong16@gmail.com>
Author: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Discussion: https://www.postgresql.org/message-id/19621-0a480d2dc74e6bd5%40postgresql.org
Backpatch-through: 17
---
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index cfea7e160c2..a3050576cb3 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -4759,6 +4759,11 @@ ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
jsestate->jsexpr = jsexpr;
+ /* Clear empty/error here. SQL NULL skips PATH. */
+ scratch->opcode = EEOP_JSONEXPR_RESET;
+ scratch->d.jsonexpr.jsestate = jsestate;
+ ExprEvalPushStep(state, scratch);
+
/*
* Evaluate formatted_expr storing the result into
* jsestate->formatted_expr.
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 9bc23cb16fa..a82f9ee11a5 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -578,6 +578,7 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
&&CASE_EEOP_XMLEXPR,
&&CASE_EEOP_JSON_CONSTRUCTOR,
&&CASE_EEOP_IS_JSON,
+ &&CASE_EEOP_JSONEXPR_RESET,
&&CASE_EEOP_JSONEXPR_PATH,
&&CASE_EEOP_JSONEXPR_COERCION,
&&CASE_EEOP_JSONEXPR_COERCION_FINISH,
@@ -1936,6 +1937,13 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
EEO_NEXT();
}
+ EEO_CASE(EEOP_JSONEXPR_RESET)
+ {
+ ExecEvalJsonExprReset(state, op);
+
+ EEO_NEXT();
+ }
+
EEO_CASE(EEOP_JSONEXPR_PATH)
{
/* too complex for an inline implementation */
@@ -4890,8 +4898,34 @@ ExecEvalJsonIsPredicate(ExprState *state, ExprEvalStep *op)
*op->resvalue = BoolGetDatum(res);
}
+/*
+ * ExecEvalJsonExprReset
+ * Clear empty/error and ErrorSaveContext for this evaluation.
+ *
+ * SQL NULL skips EEOP_JSONEXPR_PATH, so this cannot live there.
+ */
+void
+ExecEvalJsonExprReset(ExprState *state, ExprEvalStep *op)
+{
+ JsonExprState *jsestate = op->d.jsonexpr.jsestate;
+
+ memset(&jsestate->error, 0, sizeof(NullableDatum));
+ memset(&jsestate->empty, 0, sizeof(NullableDatum));
+
+ if (jsestate->escontext.details_wanted)
+ {
+ jsestate->escontext.error_data = NULL;
+ jsestate->escontext.details_wanted = false;
+ }
+ jsestate->escontext.error_occurred = false;
+}
+
/*
* Evaluate a jsonpath against a document, both of which must have been
* evaluated and their values saved in op->d.jsonexpr.jsestate.
*
+ * JsonExprState.empty/error and ErrorSaveContext are already cleared by
+ * EEOP_JSONEXPR_RESET. This step only sets them. SQL NULL skips this
+ * step, so they must not be cleared here.
+ *
* If an error occurs during JsonPath* evaluation or when coercing its result
@@ -4922,18 +4956,6 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
item = jsestate->formatted_expr.value;
path = DatumGetJsonPathP(jsestate->pathspec.value);
- /* Set error/empty to false. */
- memset(&jsestate->error, 0, sizeof(NullableDatum));
- memset(&jsestate->empty, 0, sizeof(NullableDatum));
-
- /* Also reset ErrorSaveContext contents for the next row. */
- if (jsestate->escontext.details_wanted)
- {
- jsestate->escontext.error_data = NULL;
- jsestate->escontext.details_wanted = false;
- }
- jsestate->escontext.error_occurred = false;
-
switch (jsexpr->op)
{
case JSON_EXISTS_OP:
diff --git a/src/backend/jit/llvm/llvmjit_expr.c b/src/backend/jit/llvm/llvmjit_expr.c
index 29617437477..a96c09afe23 100644
--- a/src/backend/jit/llvm/llvmjit_expr.c
+++ b/src/backend/jit/llvm/llvmjit_expr.c
@@ -2259,6 +2259,12 @@ llvm_compile_expr(ExprState *state)
LLVMBuildBr(b, opblocks[opno + 1]);
break;
+ case EEOP_JSONEXPR_RESET:
+ build_EvalXFunc(b, mod, "ExecEvalJsonExprReset",
+ v_state, op);
+ LLVMBuildBr(b, opblocks[opno + 1]);
+ break;
+
case EEOP_JSONEXPR_PATH:
{
JsonExprState *jsestate = op->d.jsonexpr.jsestate;
diff --git a/src/backend/jit/llvm/llvmjit_types.c b/src/backend/jit/llvm/llvmjit_types.c
index c8a1f841293..654600da5b0 100644
--- a/src/backend/jit/llvm/llvmjit_types.c
+++ b/src/backend/jit/llvm/llvmjit_types.c
@@ -173,6 +173,7 @@ void *referenced_functions[] =
ExecEvalXmlExpr,
ExecEvalJsonConstructor,
ExecEvalJsonIsPredicate,
+ ExecEvalJsonExprReset,
ExecEvalJsonCoercion,
ExecEvalJsonCoercionFinish,
ExecEvalJsonExprPath,
diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h
index c61b3d624d5..8afc09d5dfa 100644
--- a/src/include/executor/execExpr.h
+++ b/src/include/executor/execExpr.h
@@ -265,6 +265,7 @@ typedef enum ExprEvalOp
EEOP_XMLEXPR,
EEOP_JSON_CONSTRUCTOR,
EEOP_IS_JSON,
+ EEOP_JSONEXPR_RESET,
EEOP_JSONEXPR_PATH,
EEOP_JSONEXPR_COERCION,
EEOP_JSONEXPR_COERCION_FINISH,
@@ -754,7 +755,7 @@ typedef struct ExprEvalStep
JsonIsPredicate *pred; /* original expression node */
} is_json;
- /* for EEOP_JSONEXPR_PATH */
+ /* for EEOP_JSONEXPR_RESET, EEOP_JSONEXPR_PATH, EEOP_JSONEXPR_COERCION_FINISH */
struct
{
struct JsonExprState *jsestate;
@@ -892,6 +893,7 @@ extern void ExecEvalXmlExpr(ExprState *state, ExprEvalStep *op);
extern void ExecEvalJsonConstructor(ExprState *state, ExprEvalStep *op,
ExprContext *econtext);
extern void ExecEvalJsonIsPredicate(ExprState *state, ExprEvalStep *op);
+extern void ExecEvalJsonExprReset(ExprState *state, ExprEvalStep *op);
extern int ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
ExprContext *econtext);
extern void ExecEvalJsonCoercion(ExprState *state, ExprEvalStep *op,
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index e95ac3eda35..fc8c6e21ad2 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -1099,7 +1099,7 @@ typedef struct DomainConstraintState
* State for JsonExpr evaluation, too big to inline.
*
* This contains the information going into and coming out of the
- * EEOP_JSONEXPR_PATH eval step.
+ * EEOP_JSONEXPR_RESET / EEOP_JSONEXPR_PATH eval steps.
*/
typedef struct JsonExprState
{
@@ -1119,7 +1119,7 @@ typedef struct JsonExprState
* Output variables that drive the EEOP_JUMP_IF_NOT_TRUE steps that are
* added for ON ERROR and ON EMPTY expressions, if any.
*
- * Reset for each evaluation of EEOP_JSONEXPR_PATH.
+ * Cleared by EEOP_JSONEXPR_RESET at the start of each evaluation.
*/
/* Set to true if jsonpath evaluation cause an error. */
@@ -1161,7 +1161,7 @@ typedef struct JsonExprState
* not ERROR, a pointer to this is passed to ExecInitExprRec() when
* initializing the coercion expressions or to ExecInitJsonCoercion().
*
- * Reset for each evaluation of EEOP_JSONEXPR_PATH.
+ * Reset by EEOP_JSONEXPR_RESET at the start of each evaluation.
*/
ErrorSaveContext escontext;
} JsonExprState;
diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out
index ff64dce0c59..b5209bae5e7 100644
--- a/src/test/regress/expected/sqljson_queryfuncs.out
+++ b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -475,6 +475,57 @@ FROM
2 | -1
(3 rows)
+-- SQL NULL must not reuse a previous row's ON EMPTY / ON ERROR result.
+SELECT x IS NULL AS is_null,
+ json_value(x, '$.a' RETURNING int DEFAULT 42 ON EMPTY) AS jv
+FROM (VALUES ('{}'), (NULL), ('{}')) v(x);
+ is_null | jv
+---------+----
+ f | 42
+ t |
+ f | 42
+(3 rows)
+
+SELECT x IS NULL AS is_null,
+ json_value(x, 'strict $.a' RETURNING int DEFAULT 42 ON ERROR) AS jv
+FROM (VALUES ('1'), (NULL), ('1')) v(x);
+ is_null | jv
+---------+----
+ f | 42
+ t |
+ f | 42
+(3 rows)
+
+SELECT p IS NULL AS is_null,
+ json_value('{}', p RETURNING int DEFAULT 42 ON EMPTY) AS jv
+FROM (VALUES ('$.a'::jsonpath), (NULL), ('$.a'::jsonpath)) v(p);
+ is_null | jv
+---------+----
+ f | 42
+ t |
+ f | 42
+(3 rows)
+
+SELECT x IS NULL AS is_null,
+ json_query(x, '$.a' DEFAULT '"empty"' ON EMPTY) AS jq
+FROM (VALUES ('{}'::jsonb), (NULL), ('{}'::jsonb)) v(x);
+ is_null | jq
+---------+---------
+ f | "empty"
+ t |
+ f | "empty"
+(3 rows)
+
+SELECT x IS NULL AS is_null,
+ json_exists(x, 'strict $.a' TRUE ON ERROR) AS je
+FROM (VALUES ('1'::jsonb), (NULL), ('1'::jsonb)) v(x);
+ is_null | je
+---------+----
+ f | t
+ t |
+ f | t
+(3 rows)
+
SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a);
json_value
------------
diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql
index a69ef253f66..03f6a932b9c 100644
--- a/src/test/regress/sql/sqljson_queryfuncs.sql
+++ b/src/test/regress/sql/sqljson_queryfuncs.sql
@@ -128,6 +128,27 @@ SELECT
FROM
generate_series(0, 2) x;
+-- SQL NULL must not reuse a previous row's ON EMPTY / ON ERROR result.
+SELECT x IS NULL AS is_null,
+ json_value(x, '$.a' RETURNING int DEFAULT 42 ON EMPTY) AS jv
+FROM (VALUES ('{}'), (NULL), ('{}')) v(x);
+
+SELECT x IS NULL AS is_null,
+ json_value(x, 'strict $.a' RETURNING int DEFAULT 42 ON ERROR) AS jv
+FROM (VALUES ('1'), (NULL), ('1')) v(x);
+
+SELECT p IS NULL AS is_null,
+ json_value('{}', p RETURNING int DEFAULT 42 ON EMPTY) AS jv
+FROM (VALUES ('$.a'::jsonpath), (NULL), ('$.a'::jsonpath)) v(p);
+
+SELECT x IS NULL AS is_null,
+ json_query(x, '$.a' DEFAULT '"empty"' ON EMPTY) AS jq
+FROM (VALUES ('{}'::jsonb), (NULL), ('{}'::jsonb)) v(x);
+
+SELECT x IS NULL AS is_null,
+ json_exists(x, 'strict $.a' TRUE ON ERROR) AS je
+FROM (VALUES ('1'::jsonb), (NULL), ('1'::jsonb)) v(x);
+
SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a);
SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a RETURNING point);
SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a RETURNING point ERROR ON ERROR);
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19621: Unexpected results of JSON_VALUE with DEFAULT ON EMPTY
@ 2026-09-23 20:12 Manu <manuelreyesbravo@gmail.com>
parent: Andrey Rachitskiy <pl0h0yp1@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Manu @ 2026-09-23 20:12 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: Andrey Rachitskiy <pl0h0yp1@gmail.com>; Amit Langote <amitlangote09@gmail.com>; syzhong16@gmail.com
Hi Andrey,
I tested v2 on master, REL_18_STABLE and REL_17_STABLE, together with
Srinath's fix for #19695 [1], which touches the same function. Both
apply cleanly on all three branches, build without warnings, and the
main regression suite passes. The reported cases now give the right
answer on the three branches:
- #19621: json_value(x, '$.a' RETURNING int DEFAULT 42 ON EMPTY) over
'{}' then NULL returns 42, NULL (it was 42, 42)
- #19654: json_exists(j, 'strict $.a' FALSE ON ERROR) over '{}' then
NULL returns false, NULL (it was false, false)
I could not exercise the llvmjit_expr.c part: this machine has no LLVM
build.
One thing for the back-patch. EEOP_JSONEXPR_RESET is inserted before
EEOP_JSONEXPR_PATH, so every opcode from there to EEOP_LAST changes its
value: 24 of them on REL_17_STABLE and 25 on REL_18_STABLE. On master
that is fine, but the back branches are under the buildfarm's ABI
compliance check (.abi-compliance-history), and anything built against
a minor release that uses those values would be off by one after the
update. Putting the new opcode just before EEOP_LAST in the
back-branch versions, with the dispatch table and llvmjit_expr.c
following the same order, would leave the existing values alone.
If you post back-branch versions, I will rerun the same checks on
them.
[1]
https://postgr.es/m/CAFC+b6p8Oo3_pLXPa=DVXx1XDQJ09b2W3U3WKPtkQYyh0z0z-A@mail.gmail.com
Regards,
Manu
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19621: Unexpected results of JSON_VALUE with DEFAULT ON EMPTY
@ 2026-09-24 17:06 Srinath Reddy Sadipiralla <srinath2133@gmail.com>
parent: Manu <manuelreyesbravo@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Srinath Reddy Sadipiralla @ 2026-09-24 17:06 UTC (permalink / raw)
To: Manu <manuelreyesbravo@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org, Andrey Rachitskiy <pl0h0yp1@gmail.com>; Amit Langote <amitlangote09@gmail.com>; syzhong16@gmail.com
Hi,
Patch LGTM , I have tested with LLVM build as well , it works as intended.
--
Thanks :)
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19621: Unexpected results of JSON_VALUE with DEFAULT ON EMPTY
@ 2026-09-24 17:38 Andrey Rachitskiy <pl0h0yp1@gmail.com>
parent: Srinath Reddy Sadipiralla <srinath2133@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Andrey Rachitskiy @ 2026-09-24 17:38 UTC (permalink / raw)
To: Srinath Reddy Sadipiralla <srinath2133@gmail.com>; +Cc: Manu <manuelreyesbravo@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; Amit Langote <amitlangote09@gmail.com>; syzhong16 <syzhong16@gmail.com>
чт, 24 сент. 2026 г., 22:07 Srinath Reddy Sadipiralla <srinath2133@gmail.com
>:
> Hi,
>
> Patch LGTM , I have tested with LLVM build as well , it works as intended.
>
> --
> Thanks :)
> Srinath Reddy Sadipiralla
> EDB: https://www.enterprisedb.com/
>
Hi, Srinath!
Thnx for the review!
Manu, thnx for the testing.
---
Regards,
Rachitskiy Andrey
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19621: Unexpected results of JSON_VALUE with DEFAULT ON EMPTY
@ 2026-09-26 16:28 jian he <jian.universality@gmail.com>
parent: Andrey Rachitskiy <pl0h0yp1@gmail.com>
1 sibling, 2 replies; 11+ messages in thread
From: jian he @ 2026-09-26 16:28 UTC (permalink / raw)
To: Andrey Rachitskiy <pl0h0yp1@gmail.com>; +Cc: zengman <zengman@halodbtech.com>; syzhong16 <syzhong16@gmail.com>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>; Amit Langote <amitlangote09@gmail.com>
On Fri, Sep 4, 2026 at 6:38 PM Andrey Rachitskiy <pl0h0yp1@gmail.com> wrote:
>
> Attached is v2 of the patch.
>
> --
> Regards,
> Rachitskiy Andrey
+ EEO_CASE(EEOP_JSONEXPR_RESET)
+ {
+ ExecEvalJsonExprReset(state, op);
+
+ EEO_NEXT();
+ }
+
--- a/src/include/executor/execExpr.h
+++ b/src/include/executor/execExpr.h
@@ -265,6 +265,7 @@ typedef enum ExprEvalOp
EEOP_XMLEXPR,
EEOP_JSON_CONSTRUCTOR,
EEOP_IS_JSON,
+ EEOP_JSONEXPR_RESET,
EEOP_JSONEXPR_PATH,
EEOP_JSONEXPR_COERCION,
EEOP_JSONEXPR_COERCION_FINISH,
This seems unnecessary.
In EEOP_JSONEXPR_PATH, we can
if document or jsonpath is NULL, we can just go to jump_end (return
NULL) or jump_eval_coercion (NULL need coerce to constrainted domain),
no need to worry about ON ERROR, ON EMPTY.
What do you think of the attachment?
--
jian
https://www.enterprisedb.com/
Attachments:
[text/x-patch] v3-0001-Reset-JsonExpr-empty-error-flags-before-NULL-short-circuit.patch (8.6K, ../../CACJufxFL-xzeD_GVVqbd1Jeab8unkbibM_=1cLBUASyi2bDx=g@mail.gmail.com/2-v3-0001-Reset-JsonExpr-empty-error-flags-before-NULL-short-circuit.patch)
download | inline diff:
From ab0e6db5fe8aa5aac591ed2c04a17023dad712f2 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Sun, 27 Sep 2026 00:04:56 +0800
Subject: [PATCH v3 1/1] Reset JsonExpr empty/error flags before NULL
short-circuit
SQL NULL context/path skips EEOP_JSONEXPR_PATH (JUMP_IF_NULL to CONST NULL)
so jsonpath is not evaluated, then falls through to domain coercion and
ON EMPTY / ON ERROR checks. empty/error were cleared only inside PATH,
so a previous row's EMPTY/ERROR leaked onto the NULL row when DEFAULT was
not NULL.
Clear the flags in a new EEOP_JSONEXPR_RESET at the start of each JsonExpr
evaluation.
BUG #19621
Reported-by: Suyang Zhong <syzhong16@gmail.com>
Author: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Discussion: https://www.postgresql.org/message-id/19621-0a480d2dc74e6bd5%40postgresql.org
Backpatch-through: 17
---
src/backend/executor/execExpr.c | 29 ++++-----
src/backend/executor/execExprInterp.c | 23 ++++++-
.../regress/expected/sqljson_queryfuncs.out | 61 +++++++++++++++++++
src/test/regress/sql/sqljson_queryfuncs.sql | 24 ++++++++
4 files changed, 116 insertions(+), 21 deletions(-)
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index 82e846a1f4f..18b33b24282 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -4807,6 +4807,17 @@ ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
jsestate->args = lappend(jsestate->args, var);
}
+ /*
+ * Adjust jump target addresses of JUMPs that we added above to point to
+ * the EEOP_JSONEXPR_PATH step, which returns NULL when either
+ * formatted_expr or pathspec is NULL.
+ */
+ foreach(lc, jumps_return_null)
+ {
+ ExprEvalStep *as = &state->steps[lfirst_int(lc)];
+
+ as->d.jump.jumpdone = state->steps_len;
+ }
/* Step for jsonpath evaluation; see ExecEvalJsonExprPath(). */
scratch->opcode = EEOP_JSONEXPR_PATH;
scratch->resvalue = resv;
@@ -4814,24 +4825,6 @@ ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
scratch->d.jsonexpr.jsestate = jsestate;
ExprEvalPushStep(state, scratch);
- /*
- * Step to return NULL after jumping to skip the EEOP_JSONEXPR_PATH step
- * when either formatted_expr or pathspec is NULL. Adjust jump target
- * addresses of JUMPs that we added above.
- */
- foreach(lc, jumps_return_null)
- {
- ExprEvalStep *as = &state->steps[lfirst_int(lc)];
-
- as->d.jump.jumpdone = state->steps_len;
- }
- scratch->opcode = EEOP_CONST;
- scratch->resvalue = resv;
- scratch->resnull = resnull;
- scratch->d.constval.value = (Datum) 0;
- scratch->d.constval.isnull = true;
- ExprEvalPushStep(state, scratch);
-
escontext = jsexpr->on_error->btype != JSON_BEHAVIOR_ERROR ?
&jsestate->escontext : NULL;
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 397219f7a3a..900946ae32e 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -4893,6 +4893,7 @@ ExecEvalJsonIsPredicate(ExprState *state, ExprEvalStep *op)
/*
* Evaluate a jsonpath against a document, both of which must have been
* evaluated and their values saved in op->d.jsonexpr.jsestate.
+ * (If the document is NULL, the jsonpath evaluation is skipped)
*
* If an error occurs during JsonPath* evaluation or when coercing its result
* to the RETURNING type, JsonExprState.error is set to true, provided the
@@ -4919,9 +4920,6 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
int jump_eval_coercion = jsestate->jump_eval_coercion;
char *val_string = NULL;
- item = jsestate->formatted_expr.value;
- path = DatumGetJsonPathP(jsestate->pathspec.value);
-
/* Set error/empty to false. */
memset(&jsestate->error, 0, sizeof(NullableDatum));
memset(&jsestate->empty, 0, sizeof(NullableDatum));
@@ -4934,6 +4932,25 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
}
jsestate->escontext.error_occurred = false;
+ /*
+ * Return NULL if formatted_expr or pathspec is NULL, skipping ON ERROR
+ * and ON EMPTY. If the RETURNING type is a domain with constraints, the
+ * NULL must still be coerced to it so that the constraints are checked.
+ */
+ if (jsestate->formatted_expr.isnull || jsestate->pathspec.isnull)
+ {
+ *op->resvalue = (Datum) 0;
+ *op->resnull = true;
+
+ if (jump_eval_coercion >= 0)
+ return jump_eval_coercion;
+ else
+ return jsestate->jump_end;
+ }
+
+ item = jsestate->formatted_expr.value;
+ path = DatumGetJsonPathP(jsestate->pathspec.value);
+
switch (jsexpr->op)
{
case JSON_EXISTS_OP:
diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out
index fe8aa927b5e..c24d86fc35f 100644
--- a/src/test/regress/expected/sqljson_queryfuncs.out
+++ b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -337,6 +337,16 @@ SELECT JSON_VALUE('"purple"'::jsonb, 'lax $[*]' RETURNING rgb);
SELECT JSON_VALUE('"purple"'::jsonb, 'lax $[*]' RETURNING rgb ERROR ON ERROR);
ERROR: value for domain rgb violates check constraint "rgb_check"
+SELECT x IS NULL AS is_null,
+ json_value(x, 'strict $.a' RETURNING sqljsonb_int_not_null DEFAULT 1 ON ERROR) AS jv
+FROM (VALUES ('1'::jsonb), (NULL), ('1'::jsonb)) v(x);
+ is_null | jv
+---------+----
+ f | 1
+ t | 1
+ f | 1
+(3 rows)
+
SELECT JSON_VALUE(jsonb '[]', '$');
json_value
------------
@@ -475,6 +485,57 @@ FROM
2 | -1
(3 rows)
+-- SQL NULL must not reuse a previous row's ON EMPTY / ON ERROR result.
+SELECT x IS NULL AS is_null,
+ json_value(x, '$.a' RETURNING int DEFAULT 42 ON EMPTY) AS jv
+FROM (VALUES ('{}'), (NULL), ('{}')) v(x);
+ is_null | jv
+---------+----
+ f | 42
+ t |
+ f | 42
+(3 rows)
+
+SELECT x IS NULL AS is_null,
+ json_value(x, 'strict $.a' RETURNING int DEFAULT 42 ON ERROR) AS jv
+FROM (VALUES ('1'), (NULL), ('1')) v(x);
+ is_null | jv
+---------+----
+ f | 42
+ t |
+ f | 42
+(3 rows)
+
+SELECT p IS NULL AS is_null,
+ json_value('{}', p RETURNING int DEFAULT 42 ON EMPTY) AS jv
+FROM (VALUES ('$.a'::jsonpath), (NULL), ('$.a'::jsonpath)) v(p);
+ is_null | jv
+---------+----
+ f | 42
+ t |
+ f | 42
+(3 rows)
+
+SELECT x IS NULL AS is_null,
+ json_query(x, '$.a' DEFAULT '"empty"' ON EMPTY) AS jq
+FROM (VALUES ('{}'::jsonb), (NULL), ('{}'::jsonb)) v(x);
+ is_null | jq
+---------+---------
+ f | "empty"
+ t |
+ f | "empty"
+(3 rows)
+
+SELECT x IS NULL AS is_null,
+ json_exists(x, 'strict $.a' TRUE ON ERROR) AS je
+FROM (VALUES ('1'::jsonb), (NULL), ('1'::jsonb)) v(x);
+ is_null | je
+---------+----
+ f | t
+ t |
+ f | t
+(3 rows)
+
SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a);
json_value
------------
diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql
index 0767db9cfff..80fdb805f0f 100644
--- a/src/test/regress/sql/sqljson_queryfuncs.sql
+++ b/src/test/regress/sql/sqljson_queryfuncs.sql
@@ -80,6 +80,9 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
CREATE DOMAIN rgb AS rainbow CHECK (VALUE IN ('red', 'green', 'blue'));
SELECT JSON_VALUE('"purple"'::jsonb, 'lax $[*]' RETURNING rgb);
SELECT JSON_VALUE('"purple"'::jsonb, 'lax $[*]' RETURNING rgb ERROR ON ERROR);
+SELECT x IS NULL AS is_null,
+ json_value(x, 'strict $.a' RETURNING sqljsonb_int_not_null DEFAULT 1 ON ERROR) AS jv
+FROM (VALUES ('1'::jsonb), (NULL), ('1'::jsonb)) v(x);
SELECT JSON_VALUE(jsonb '[]', '$');
SELECT JSON_VALUE(jsonb '[]', '$' ERROR ON ERROR);
@@ -128,6 +131,27 @@ SELECT
FROM
generate_series(0, 2) x;
+-- SQL NULL must not reuse a previous row's ON EMPTY / ON ERROR result.
+SELECT x IS NULL AS is_null,
+ json_value(x, '$.a' RETURNING int DEFAULT 42 ON EMPTY) AS jv
+FROM (VALUES ('{}'), (NULL), ('{}')) v(x);
+
+SELECT x IS NULL AS is_null,
+ json_value(x, 'strict $.a' RETURNING int DEFAULT 42 ON ERROR) AS jv
+FROM (VALUES ('1'), (NULL), ('1')) v(x);
+
+SELECT p IS NULL AS is_null,
+ json_value('{}', p RETURNING int DEFAULT 42 ON EMPTY) AS jv
+FROM (VALUES ('$.a'::jsonpath), (NULL), ('$.a'::jsonpath)) v(p);
+
+SELECT x IS NULL AS is_null,
+ json_query(x, '$.a' DEFAULT '"empty"' ON EMPTY) AS jq
+FROM (VALUES ('{}'::jsonb), (NULL), ('{}'::jsonb)) v(x);
+
+SELECT x IS NULL AS is_null,
+ json_exists(x, 'strict $.a' TRUE ON ERROR) AS je
+FROM (VALUES ('1'::jsonb), (NULL), ('1'::jsonb)) v(x);
+
SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a);
SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a RETURNING point);
SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a RETURNING point ERROR ON ERROR);
--
2.34.1
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19621: Unexpected results of JSON_VALUE with DEFAULT ON EMPTY
@ 2026-09-26 18:14 Manu <manuelreyesbravo@gmail.com>
parent: jian he <jian.universality@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Manu @ 2026-09-26 18:14 UTC (permalink / raw)
To: jian he <jian.universality@gmail.com>; +Cc: Andrey Rachitskiy <pl0h0yp1@gmail.com>; zengman <zengman@halodbtech.com>; syzhong16 <syzhong16@gmail.com>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>; Amit Langote <amitlangote09@gmail.com>
Hi jian,
> This seems unnecessary.
> In EEOP_JSONEXPR_PATH, we can
> if document or jsonpath is NULL, we can just go to jump_end (return
> NULL) or jump_eval_coercion (NULL need coerce to constrainted domain),
> no need to worry about ON ERROR, ON EMPTY.
That also takes care of the back-branch concern I raised on v2: with
no new opcode, v3 does not change any header, so the ExprEvalOp values
stay as they are.
v3 applies cleanly to master, REL_18_STABLE and REL_17_STABLE, builds
without warnings, and the main regression suite passes on all three.
Since the bug is state leaking from one row to the next, I also
checked it with a differential test (attached): every row of a
two-row query must give the same result as that row evaluated alone.
It covers json_value (RETURNING int, a NOT NULL domain and a CHECK
domain), json_query and json_exists, with every ON EMPTY / ON ERROR
combination, over every ordered pair of rows built from five
documents ('{}', '{"a":1}', '{"a":"x"}', '{"a":[1,2]}', NULL) and
three paths ('$.a', 'strict $.a', NULL). That is 9675 cases:
- unpatched master (09a579abaca): 567 mismatches (497 json_value,
56 json_query, 14 json_exists)
- v3 on master, REL_18_STABLE and REL_17_STABLE: 0
The single-row answers do not change: all 645 of them are the same
with and without v3, including NULL input into the NOT NULL domain.
With Srinath's fix for #19695 on top, its case is right too
(1, NULL, 2). As before, I could not exercise JIT here.
Regards,
Manu
-- Differential check for JsonExpr state leaking from one row to the next
-- (#19621, #19654). Oracle: in a multi-row query, the result for each row
-- must be the one that same row gives when evaluated alone. If any row
-- errors when alone, the multi-row query must fail with the first such
-- error (VALUES is scanned in order).
--
-- Matrix: json_value (RETURNING int, a NOT NULL domain, a CHECK domain) x
-- ON EMPTY x ON ERROR, json_query x ON EMPTY x ON ERROR, json_exists x
-- ON ERROR; over every ordered pair of rows, where a row is a document
-- ('{}', '{"a":1}', '{"a":"x"}', '{"a":[1,2]}', NULL) and a path
-- ('$.a', 'strict $.a', NULL).
\set QUIET on
SET client_min_messages = warning;
DROP DOMAIN IF EXISTS o_nn, o_pos CASCADE;
CREATE DOMAIN o_nn AS int NOT NULL;
CREATE DOMAIN o_pos AS int CHECK (VALUE > 0);
CREATE TEMP TABLE o_item (i int, d text, p text);
INSERT INTO o_item
SELECT row_number() OVER (), d, p
FROM unnest(ARRAY['''{}''', '''{"a":1}''', '''{"a":"x"}''', '''{"a":[1,2]}''', 'NULL']) d,
unnest(ARRAY['''$.a''', '''strict $.a''', 'NULL']) p;
CREATE TEMP TABLE o_expr (e text);
INSERT INTO o_expr
SELECT format('json_value(x, p RETURNING %s %s ON EMPTY %s ON ERROR)', t, oe, oer)
FROM unnest(ARRAY['int', 'o_nn', 'o_pos']) t,
unnest(ARRAY['NULL', 'DEFAULT 42', 'ERROR']) oe,
unnest(ARRAY['NULL', 'DEFAULT 7', 'ERROR']) oer
UNION ALL
SELECT format('json_query(x, p %s ON EMPTY %s ON ERROR)', oe, oer)
FROM unnest(ARRAY['NULL', 'EMPTY ARRAY', 'DEFAULT ''"d"''', 'ERROR']) oe,
unnest(ARRAY['NULL', 'EMPTY OBJECT', 'ERROR']) oer
UNION ALL
SELECT format('json_exists(x, p %s ON ERROR)', oer)
FROM unnest(ARRAY['FALSE', 'TRUE', 'UNKNOWN', 'ERROR']) oer;
CREATE TEMP TABLE o_result (e text, rows text, alone text, together text);
DO $$
DECLARE
ex record; a record; b record;
r1 text; r2 text; together text; alone text;
q text;
BEGIN
FOR ex IN SELECT e FROM o_expr LOOP
FOR a IN SELECT * FROM o_item LOOP
FOR b IN SELECT * FROM o_item LOOP
-- each row alone
BEGIN
EXECUTE format('SELECT coalesce((%s)::text, ''NULL'') FROM (VALUES (%s::jsonb, %s::jsonpath)) v(x, p)',
ex.e, a.d, a.p) INTO r1;
EXCEPTION WHEN OTHERS THEN r1 := 'ERROR: ' || SQLERRM;
END;
BEGIN
EXECUTE format('SELECT coalesce((%s)::text, ''NULL'') FROM (VALUES (%s::jsonb, %s::jsonpath)) v(x, p)',
ex.e, b.d, b.p) INTO r2;
EXCEPTION WHEN OTHERS THEN r2 := 'ERROR: ' || SQLERRM;
END;
alone := CASE WHEN r1 LIKE 'ERROR:%' THEN r1
WHEN r2 LIKE 'ERROR:%' THEN r2
ELSE r1 || ' | ' || r2 END;
-- both rows in one query, in order
q := format('SELECT string_agg(coalesce((%s)::text, ''NULL''), '' | '' ORDER BY o)
FROM (VALUES (1, %s::jsonb, %s::jsonpath), (2, %s::jsonb, %s::jsonpath)) v(o, x, p)',
ex.e, a.d, a.p, b.d, b.p);
BEGIN
EXECUTE q INTO together;
EXCEPTION WHEN OTHERS THEN together := 'ERROR: ' || SQLERRM;
END;
INSERT INTO o_result VALUES (ex.e, a.d || ',' || a.p || ' then ' || b.d || ',' || b.p, alone, together);
END LOOP;
END LOOP;
END LOOP;
END $$;
SELECT 'cases=' || count(*) || ' mismatches=' || count(*) FILTER (WHERE alone IS DISTINCT FROM together)
FROM o_result;
SELECT split_part(e, '(', 1) AS func, count(*) FILTER (WHERE alone IS DISTINCT FROM together) AS mismatches, count(*) AS cases
FROM o_result GROUP BY 1 ORDER BY 1;
SELECT e, rows, alone, together
FROM o_result WHERE alone IS DISTINCT FROM together
ORDER BY e, rows LIMIT 8;
Attachments:
[text/plain] rows_oracle.sql.txt (3.6K, ../../179044644489.113031.11832692369228086606@gmail.com/2-rows_oracle.sql.txt)
download | inline:
-- Differential check for JsonExpr state leaking from one row to the next
-- (#19621, #19654). Oracle: in a multi-row query, the result for each row
-- must be the one that same row gives when evaluated alone. If any row
-- errors when alone, the multi-row query must fail with the first such
-- error (VALUES is scanned in order).
--
-- Matrix: json_value (RETURNING int, a NOT NULL domain, a CHECK domain) x
-- ON EMPTY x ON ERROR, json_query x ON EMPTY x ON ERROR, json_exists x
-- ON ERROR; over every ordered pair of rows, where a row is a document
-- ('{}', '{"a":1}', '{"a":"x"}', '{"a":[1,2]}', NULL) and a path
-- ('$.a', 'strict $.a', NULL).
\set QUIET on
SET client_min_messages = warning;
DROP DOMAIN IF EXISTS o_nn, o_pos CASCADE;
CREATE DOMAIN o_nn AS int NOT NULL;
CREATE DOMAIN o_pos AS int CHECK (VALUE > 0);
CREATE TEMP TABLE o_item (i int, d text, p text);
INSERT INTO o_item
SELECT row_number() OVER (), d, p
FROM unnest(ARRAY['''{}''', '''{"a":1}''', '''{"a":"x"}''', '''{"a":[1,2]}''', 'NULL']) d,
unnest(ARRAY['''$.a''', '''strict $.a''', 'NULL']) p;
CREATE TEMP TABLE o_expr (e text);
INSERT INTO o_expr
SELECT format('json_value(x, p RETURNING %s %s ON EMPTY %s ON ERROR)', t, oe, oer)
FROM unnest(ARRAY['int', 'o_nn', 'o_pos']) t,
unnest(ARRAY['NULL', 'DEFAULT 42', 'ERROR']) oe,
unnest(ARRAY['NULL', 'DEFAULT 7', 'ERROR']) oer
UNION ALL
SELECT format('json_query(x, p %s ON EMPTY %s ON ERROR)', oe, oer)
FROM unnest(ARRAY['NULL', 'EMPTY ARRAY', 'DEFAULT ''"d"''', 'ERROR']) oe,
unnest(ARRAY['NULL', 'EMPTY OBJECT', 'ERROR']) oer
UNION ALL
SELECT format('json_exists(x, p %s ON ERROR)', oer)
FROM unnest(ARRAY['FALSE', 'TRUE', 'UNKNOWN', 'ERROR']) oer;
CREATE TEMP TABLE o_result (e text, rows text, alone text, together text);
DO $$
DECLARE
ex record; a record; b record;
r1 text; r2 text; together text; alone text;
q text;
BEGIN
FOR ex IN SELECT e FROM o_expr LOOP
FOR a IN SELECT * FROM o_item LOOP
FOR b IN SELECT * FROM o_item LOOP
-- each row alone
BEGIN
EXECUTE format('SELECT coalesce((%s)::text, ''NULL'') FROM (VALUES (%s::jsonb, %s::jsonpath)) v(x, p)',
ex.e, a.d, a.p) INTO r1;
EXCEPTION WHEN OTHERS THEN r1 := 'ERROR: ' || SQLERRM;
END;
BEGIN
EXECUTE format('SELECT coalesce((%s)::text, ''NULL'') FROM (VALUES (%s::jsonb, %s::jsonpath)) v(x, p)',
ex.e, b.d, b.p) INTO r2;
EXCEPTION WHEN OTHERS THEN r2 := 'ERROR: ' || SQLERRM;
END;
alone := CASE WHEN r1 LIKE 'ERROR:%' THEN r1
WHEN r2 LIKE 'ERROR:%' THEN r2
ELSE r1 || ' | ' || r2 END;
-- both rows in one query, in order
q := format('SELECT string_agg(coalesce((%s)::text, ''NULL''), '' | '' ORDER BY o)
FROM (VALUES (1, %s::jsonb, %s::jsonpath), (2, %s::jsonb, %s::jsonpath)) v(o, x, p)',
ex.e, a.d, a.p, b.d, b.p);
BEGIN
EXECUTE q INTO together;
EXCEPTION WHEN OTHERS THEN together := 'ERROR: ' || SQLERRM;
END;
INSERT INTO o_result VALUES (ex.e, a.d || ',' || a.p || ' then ' || b.d || ',' || b.p, alone, together);
END LOOP;
END LOOP;
END LOOP;
END $$;
SELECT 'cases=' || count(*) || ' mismatches=' || count(*) FILTER (WHERE alone IS DISTINCT FROM together)
FROM o_result;
SELECT split_part(e, '(', 1) AS func, count(*) FILTER (WHERE alone IS DISTINCT FROM together) AS mismatches, count(*) AS cases
FROM o_result GROUP BY 1 ORDER BY 1;
SELECT e, rows, alone, together
FROM o_result WHERE alone IS DISTINCT FROM together
ORDER BY e, rows LIMIT 8;
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19621: Unexpected results of JSON_VALUE with DEFAULT ON EMPTY
@ 2026-09-28 04:03 Andrey Rachitskiy <pl0h0yp1@gmail.com>
parent: jian he <jian.universality@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Andrey Rachitskiy @ 2026-09-28 04:03 UTC (permalink / raw)
To: jian he <jian.universality@gmail.com>; +Cc: zengman <zengman@halodbtech.com>; syzhong16 <syzhong16@gmail.com>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>; Amit Langote <amitlangote09@gmail.com>
сб, 26 сент. 2026 г. в 21:29, jian he <jian.universality@gmail.com>:
> On Fri, Sep 4, 2026 at 6:38 PM Andrey Rachitskiy <pl0h0yp1@gmail.com>
> wrote:
> >
> > Attached is v2 of the patch.
> >
> > --
> > Regards,
> > Rachitskiy Andrey
>
>
> + EEO_CASE(EEOP_JSONEXPR_RESET)
> + {
> + ExecEvalJsonExprReset(state, op);
> +
> + EEO_NEXT();
> + }
> +
>
> --- a/src/include/executor/execExpr.h
> +++ b/src/include/executor/execExpr.h
> @@ -265,6 +265,7 @@ typedef enum ExprEvalOp
> EEOP_XMLEXPR,
> EEOP_JSON_CONSTRUCTOR,
> EEOP_IS_JSON,
> + EEOP_JSONEXPR_RESET,
> EEOP_JSONEXPR_PATH,
> EEOP_JSONEXPR_COERCION,
> EEOP_JSONEXPR_COERCION_FINISH,
>
> This seems unnecessary.
> In EEOP_JSONEXPR_PATH, we can
> if document or jsonpath is NULL, we can just go to jump_end (return
> NULL) or jump_eval_coercion (NULL need coerce to constrainted domain),
> no need to worry about ON ERROR, ON EMPTY.
>
> What do you think of the attachment?
>
>
>
> --
> jian
> https://www.enterprisedb.com/
Hi, Jian!
Thanks for posting this alternative.
I considered the same shape earlier: keep the empty/error reset in
EEOP_JSONEXPR_PATH and stop skipping that step on SQL NULL. Your version is
simpler than adding EEOP_JSONEXPR_RESET. No new opcode, and no JIT or
back-branch ABI churn.
The commit message still described a RESET opcode that the diff does not
add. I adjusted the subject and body to match what the patch actually does
(retarget JUMP_IF_NULL at PATH, drop the CONST NULL pad, handle NULL inside
ExecEvalJsonExprPath).
Either approach fixes the reported cases. I am fine with whichever version
a committer prefers.
Attachments:
[text/x-patch] v3-0001-Handle-SQL-NULL-inside-EEOP-JSONEXPR-PATH.patch (8.8K, ../../CAB8bMiua3O8ZA1YHzQQYNLeDg+Pyom-XoiDx4_Z_QoA8WCu7Rw@mail.gmail.com/3-v3-0001-Handle-SQL-NULL-inside-EEOP-JSONEXPR-PATH.patch)
download | inline diff:
From ab0e6db5fe8aa5aac591ed2c04a17023dad712f2 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Sun, 27 Sep 2026 00:04:56 +0800
Subject: [PATCH v3 1/1] Handle SQL NULL inside EEOP_JSONEXPR_PATH
SQL NULL context/path used JUMP_IF_NULL to skip EEOP_JSONEXPR_PATH and
land on a CONST NULL step. empty/error were cleared only inside PATH,
so a previous row's EMPTY/ERROR leaked onto the NULL row when DEFAULT
was not NULL, because execution then fell through the ON EMPTY / ON
ERROR checks.
Point those JUMP_IF_NULL steps at EEOP_JSONEXPR_PATH instead, drop the
CONST NULL landing pad, and return NULL from ExecEvalJsonExprPath after
clearing the flags (still jumping to domain coercion when needed).
Jsonpath is still not evaluated for NULL input. ON EMPTY / ON ERROR
are skipped as intended.
BUG #19621
Reported-by: Suyang Zhong <syzhong16@gmail.com>
Author: jian he <jian.universality@gmail.com>
Discussion: https://www.postgresql.org/message-id/19621-0a480d2dc74e6bd5@postgresql.org
Backpatch-through: 17
---
src/backend/executor/execExpr.c | 29 ++++-----
src/backend/executor/execExprInterp.c | 23 ++++++-
.../regress/expected/sqljson_queryfuncs.out | 61 +++++++++++++++++++
src/test/regress/sql/sqljson_queryfuncs.sql | 24 ++++++++
4 files changed, 116 insertions(+), 21 deletions(-)
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index 82e846a1f4f..18b33b24282 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -4807,6 +4807,17 @@ ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
jsestate->args = lappend(jsestate->args, var);
}
+ /*
+ * Adjust jump target addresses of JUMPs that we added above to point to
+ * the EEOP_JSONEXPR_PATH step, which returns NULL when either
+ * formatted_expr or pathspec is NULL.
+ */
+ foreach(lc, jumps_return_null)
+ {
+ ExprEvalStep *as = &state->steps[lfirst_int(lc)];
+
+ as->d.jump.jumpdone = state->steps_len;
+ }
/* Step for jsonpath evaluation; see ExecEvalJsonExprPath(). */
scratch->opcode = EEOP_JSONEXPR_PATH;
scratch->resvalue = resv;
@@ -4814,24 +4825,6 @@ ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
scratch->d.jsonexpr.jsestate = jsestate;
ExprEvalPushStep(state, scratch);
- /*
- * Step to return NULL after jumping to skip the EEOP_JSONEXPR_PATH step
- * when either formatted_expr or pathspec is NULL. Adjust jump target
- * addresses of JUMPs that we added above.
- */
- foreach(lc, jumps_return_null)
- {
- ExprEvalStep *as = &state->steps[lfirst_int(lc)];
-
- as->d.jump.jumpdone = state->steps_len;
- }
- scratch->opcode = EEOP_CONST;
- scratch->resvalue = resv;
- scratch->resnull = resnull;
- scratch->d.constval.value = (Datum) 0;
- scratch->d.constval.isnull = true;
- ExprEvalPushStep(state, scratch);
-
escontext = jsexpr->on_error->btype != JSON_BEHAVIOR_ERROR ?
&jsestate->escontext : NULL;
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 397219f7a3a..900946ae32e 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -4893,6 +4893,7 @@ ExecEvalJsonIsPredicate(ExprState *state, ExprEvalStep *op)
/*
* Evaluate a jsonpath against a document, both of which must have been
* evaluated and their values saved in op->d.jsonexpr.jsestate.
+ * (If the document is NULL, the jsonpath evaluation is skipped)
*
* If an error occurs during JsonPath* evaluation or when coercing its result
* to the RETURNING type, JsonExprState.error is set to true, provided the
@@ -4919,9 +4920,6 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
int jump_eval_coercion = jsestate->jump_eval_coercion;
char *val_string = NULL;
- item = jsestate->formatted_expr.value;
- path = DatumGetJsonPathP(jsestate->pathspec.value);
-
/* Set error/empty to false. */
memset(&jsestate->error, 0, sizeof(NullableDatum));
memset(&jsestate->empty, 0, sizeof(NullableDatum));
@@ -4934,6 +4932,25 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
}
jsestate->escontext.error_occurred = false;
+ /*
+ * Return NULL if formatted_expr or pathspec is NULL, skipping ON ERROR
+ * and ON EMPTY. If the RETURNING type is a domain with constraints, the
+ * NULL must still be coerced to it so that the constraints are checked.
+ */
+ if (jsestate->formatted_expr.isnull || jsestate->pathspec.isnull)
+ {
+ *op->resvalue = (Datum) 0;
+ *op->resnull = true;
+
+ if (jump_eval_coercion >= 0)
+ return jump_eval_coercion;
+ else
+ return jsestate->jump_end;
+ }
+
+ item = jsestate->formatted_expr.value;
+ path = DatumGetJsonPathP(jsestate->pathspec.value);
+
switch (jsexpr->op)
{
case JSON_EXISTS_OP:
diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out
index fe8aa927b5e..c24d86fc35f 100644
--- a/src/test/regress/expected/sqljson_queryfuncs.out
+++ b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -337,6 +337,16 @@ SELECT JSON_VALUE('"purple"'::jsonb, 'lax $[*]' RETURNING rgb);
SELECT JSON_VALUE('"purple"'::jsonb, 'lax $[*]' RETURNING rgb ERROR ON ERROR);
ERROR: value for domain rgb violates check constraint "rgb_check"
+SELECT x IS NULL AS is_null,
+ json_value(x, 'strict $.a' RETURNING sqljsonb_int_not_null DEFAULT 1 ON ERROR) AS jv
+FROM (VALUES ('1'::jsonb), (NULL), ('1'::jsonb)) v(x);
+ is_null | jv
+---------+----
+ f | 1
+ t | 1
+ f | 1
+(3 rows)
+
SELECT JSON_VALUE(jsonb '[]', '$');
json_value
------------
@@ -475,6 +485,57 @@ FROM
2 | -1
(3 rows)
+-- SQL NULL must not reuse a previous row's ON EMPTY / ON ERROR result.
+SELECT x IS NULL AS is_null,
+ json_value(x, '$.a' RETURNING int DEFAULT 42 ON EMPTY) AS jv
+FROM (VALUES ('{}'), (NULL), ('{}')) v(x);
+ is_null | jv
+---------+----
+ f | 42
+ t |
+ f | 42
+(3 rows)
+
+SELECT x IS NULL AS is_null,
+ json_value(x, 'strict $.a' RETURNING int DEFAULT 42 ON ERROR) AS jv
+FROM (VALUES ('1'), (NULL), ('1')) v(x);
+ is_null | jv
+---------+----
+ f | 42
+ t |
+ f | 42
+(3 rows)
+
+SELECT p IS NULL AS is_null,
+ json_value('{}', p RETURNING int DEFAULT 42 ON EMPTY) AS jv
+FROM (VALUES ('$.a'::jsonpath), (NULL), ('$.a'::jsonpath)) v(p);
+ is_null | jv
+---------+----
+ f | 42
+ t |
+ f | 42
+(3 rows)
+
+SELECT x IS NULL AS is_null,
+ json_query(x, '$.a' DEFAULT '"empty"' ON EMPTY) AS jq
+FROM (VALUES ('{}'::jsonb), (NULL), ('{}'::jsonb)) v(x);
+ is_null | jq
+---------+---------
+ f | "empty"
+ t |
+ f | "empty"
+(3 rows)
+
+SELECT x IS NULL AS is_null,
+ json_exists(x, 'strict $.a' TRUE ON ERROR) AS je
+FROM (VALUES ('1'::jsonb), (NULL), ('1'::jsonb)) v(x);
+ is_null | je
+---------+----
+ f | t
+ t |
+ f | t
+(3 rows)
+
SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a);
json_value
------------
diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql
index 0767db9cfff..80fdb805f0f 100644
--- a/src/test/regress/sql/sqljson_queryfuncs.sql
+++ b/src/test/regress/sql/sqljson_queryfuncs.sql
@@ -80,6 +80,9 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
CREATE DOMAIN rgb AS rainbow CHECK (VALUE IN ('red', 'green', 'blue'));
SELECT JSON_VALUE('"purple"'::jsonb, 'lax $[*]' RETURNING rgb);
SELECT JSON_VALUE('"purple"'::jsonb, 'lax $[*]' RETURNING rgb ERROR ON ERROR);
+SELECT x IS NULL AS is_null,
+ json_value(x, 'strict $.a' RETURNING sqljsonb_int_not_null DEFAULT 1 ON ERROR) AS jv
+FROM (VALUES ('1'::jsonb), (NULL), ('1'::jsonb)) v(x);
SELECT JSON_VALUE(jsonb '[]', '$');
SELECT JSON_VALUE(jsonb '[]', '$' ERROR ON ERROR);
@@ -128,6 +131,27 @@ SELECT
FROM
generate_series(0, 2) x;
+-- SQL NULL must not reuse a previous row's ON EMPTY / ON ERROR result.
+SELECT x IS NULL AS is_null,
+ json_value(x, '$.a' RETURNING int DEFAULT 42 ON EMPTY) AS jv
+FROM (VALUES ('{}'), (NULL), ('{}')) v(x);
+
+SELECT x IS NULL AS is_null,
+ json_value(x, 'strict $.a' RETURNING int DEFAULT 42 ON ERROR) AS jv
+FROM (VALUES ('1'), (NULL), ('1')) v(x);
+
+SELECT p IS NULL AS is_null,
+ json_value('{}', p RETURNING int DEFAULT 42 ON EMPTY) AS jv
+FROM (VALUES ('$.a'::jsonpath), (NULL), ('$.a'::jsonpath)) v(p);
+
+SELECT x IS NULL AS is_null,
+ json_query(x, '$.a' DEFAULT '"empty"' ON EMPTY) AS jq
+FROM (VALUES ('{}'::jsonb), (NULL), ('{}'::jsonb)) v(x);
+
+SELECT x IS NULL AS is_null,
+ json_exists(x, 'strict $.a' TRUE ON ERROR) AS je
+FROM (VALUES ('1'::jsonb), (NULL), ('1'::jsonb)) v(x);
+
SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a);
SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a RETURNING point);
SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a RETURNING point ERROR ON ERROR);
--
2.34.1
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2026-09-28 04:03 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-08-15 03:25 BUG #19621: Unexpected results of JSON_VALUE with DEFAULT ON EMPTY PG Bug reporting form <noreply@postgresql.org>
2026-08-16 14:29 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-08-18 02:06 ` =?ISO-8859-1?B?emVuZ21hbg==?= <zengman@halodbtech.com>
2026-08-18 08:56 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-04 10:38 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-23 20:12 ` Manu <manuelreyesbravo@gmail.com>
2026-09-24 17:06 ` Srinath Reddy Sadipiralla <srinath2133@gmail.com>
2026-09-24 17:38 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-26 16:28 ` jian he <jian.universality@gmail.com>
2026-09-26 18:14 ` Manu <manuelreyesbravo@gmail.com>
2026-09-28 04:03 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox