pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19841: SQL/JSON RETURNING coercion to GUC-dependent types (timestamptz etc.) is wrongly judged IMMUTABLE -
2+ messages / 2 participants
[nested] [flat]
* BUG #19841: SQL/JSON RETURNING coercion to GUC-dependent types (timestamptz etc.) is wrongly judged IMMUTABLE -
@ 2026-11-07 04:12 PG Bug reporting form <noreply@postgresql.org>
2026-11-10 04:35 ` Re: BUG #19841: SQL/JSON RETURNING coercion to GUC-dependent types (timestamptz etc.) is wrongly judged IMMUTABLE - shihao zhong <zhong950419@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2026-11-07 04:12 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: 303677365@qq.com
The following bug has been logged on the website:
Bug reference: 19841
Logged by: chunling qin
Email address: 303677365@qq.com
PostgreSQL version: 18.6
Operating system: Linux x86_64; platform-independent (reproduced on
Description:
**Description:**
`JSON_VALUE(doc, '$' RETURNING timestamptz)` (also date / timestamp /
timetz) internally coerces the JSON string using the type's STABLE input
function (`timestamptz_in` etc.), whose result depends on
TimeZone/DateStyle. The coercion is set up inside `ExecInitJsonExpr()`
(execExpr.c:4878-4908) and is invisible to
`contain_mutable_functions_walker()`, so the expression is judged IMMUTABLE.
The equivalent direct cast `s::timestamptz` is correctly rejected with 42P17
for the very same reason — the JSON_VALUE form is accepted.
Consequences (all demonstrated below):
- the expression index is accepted, then returns wrong results after a
TimeZone change (index values frozen at build time vs re-evaluated seq
scan);
- a UNIQUE index on this expression silently fails to enforce after a
TimeZone change;
- partition keys and STORED generated columns are exposed to the same
misjudgment.
**Reproduction (self-contained SQL; verified on 18.6 official image and
master):**
```sql
SET TimeZone = 'UTC';
SET DateStyle = 'ISO, MDY';
-- (1) the value depends on TimeZone:
SELECT current_setting('TimeZone') AS tz,
JSON_VALUE(to_jsonb('2020-01-01 12:34:56'::text), '$' RETURNING
timestamptz) AS v;
SET TimeZone = 'Asia/Tokyo';
SELECT current_setting('TimeZone') AS tz,
JSON_VALUE(to_jsonb('2020-01-01 12:34:56'::text), '$' RETURNING
timestamptz) AS v;
-- UTC: 2020-01-01 12:34:56+00 / Tokyo: 2020-01-01 21:34:56+09 (same JSON,
different value)
-- (2) equivalent direct cast rejected, JSON_VALUE accepted:
SET TimeZone = 'UTC';
CREATE TABLE jr_wr (id int, s text, doc jsonb);
INSERT INTO jr_wr SELECT i, '2020-01-01 12:34:56', to_jsonb('2020-01-01
12:34:56'::text)
FROM generate_series(1,500) i;
CREATE INDEX jr_wr_cast ON jr_wr ((s::timestamptz));
-- ERROR: functions in index expression must be marked IMMUTABLE
CREATE INDEX jr_wr_idx ON jr_wr ((JSON_VALUE(doc, '$' RETURNING
timestamptz))); -- ACCEPTED
CREATE INDEX jr_wr_date ON jr_wr ((JSON_VALUE(doc, '$' RETURNING date)));
-- ACCEPTED
CREATE INDEX jr_wr_ts ON jr_wr ((JSON_VALUE(doc, '$' RETURNING
timestamp))); -- ACCEPTED
CREATE INDEX jr_wr_tmz ON jr_wr ((JSON_VALUE(doc, '$' RETURNING timetz)));
-- ACCEPTED
ANALYZE jr_wr;
-- (3) wrong results: index built at UTC, then change only TimeZone
SET TimeZone = 'Asia/Tokyo';
SET enable_seqscan=off; SET enable_bitmapscan=off;
SELECT count(*) AS idx_build_literal FROM jr_wr
WHERE JSON_VALUE(doc,'$' RETURNING timestamptz) = '2020-01-01
12:34:56+00'::timestamptz; -- 500 (frozen)
SET enable_seqscan=on; SET enable_indexscan=off; SET enable_bitmapscan=off;
SELECT count(*) AS seq_build_literal FROM jr_wr
WHERE JSON_VALUE(doc,'$' RETURNING timestamptz) = '2020-01-01
12:34:56+00'::timestamptz; -- 0 (re-evaluated under Tokyo)
RESET enable_seqscan; RESET enable_indexscan; RESET enable_bitmapscan;
-- (4) UNIQUE silently fails to enforce:
SET TimeZone = 'UTC';
CREATE TABLE jr_uniq (id int, doc jsonb);
INSERT INTO jr_uniq VALUES (1, to_jsonb('2020-01-01 12:34:56'::text));
CREATE UNIQUE INDEX jr_uniq_u ON jr_uniq ((JSON_VALUE(doc, '$' RETURNING
timestamptz)));
SET TimeZone = 'Asia/Tokyo';
INSERT INTO jr_uniq VALUES (2, to_jsonb('2020-01-01 12:34:56'::text)); --
accepted
SELECT id, JSON_VALUE(doc,'$' RETURNING timestamptz) AS cur_val FROM jr_uniq
ORDER BY id;
-- 1 | 2020-01-01 12:34:56+09
-- 2 | 2020-01-01 12:34:56+09 <- two rows with the same indexed value,
UNIQUE not enforced
```
**Root cause:** the RETURNING-type IO coercion is wired internally in
`ExecInitJsonExpr()` (`src/backend/executor/execExpr.c:4878-4908`) and calls
the type's STABLE input function; the mutability walker's JsonExpr branch
(`src/backend/optimizer/util/clauses.c:450-462`) does not model this
coercion, so the whole JsonExpr is treated as immutable.
**Expected behavior:** if the RETURNING type's input function is STABLE (as
`timestamptz_in` etc. are), the SQL/JSON expression should be treated as
STABLE and rejected for index expressions / partition keys / stored
generated columns, exactly like the equivalent direct cast.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #19841: SQL/JSON RETURNING coercion to GUC-dependent types (timestamptz etc.) is wrongly judged IMMUTABLE -
2026-11-07 04:12 BUG #19841: SQL/JSON RETURNING coercion to GUC-dependent types (timestamptz etc.) is wrongly judged IMMUTABLE - PG Bug reporting form <noreply@postgresql.org>
@ 2026-11-10 04:35 ` shihao zhong <zhong950419@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: shihao zhong @ 2026-11-10 04:35 UTC (permalink / raw)
To: 303677365@qq.com; pgsql-bugs@lists.postgresql.org
Hi
Thanks for the reports. #19840, #19841 and #19850 come from the same
check in contain_mutable_functions_walker(), so the attached patch
fixes all three.
One interesting thing is that existing indexes using these
expressions will fail on restore and pg_upgrade, like in b58a9a97fe7.
I still lean toward 17 and up with a release note, since those indexes can
already
return wrong results.
Thanks,
Shihao
Attachments:
v1-REL_17_18-0001-Fix-mutability-checks-of-SQL-JSON-query-functions.-nocfbot.txt (text/plain, 7.6K) download
From 5b0562bb59fe1f1d52fe506d10d953bf007b4aa5 Mon Sep 17 00:00:00 2001
From: shihao zhong <zhong950419@gmail.com>
Date: Mon, 9 Nov 2026 23:23:10 -0500
Subject: [PATCH REL_17_18 v1] Fix mutability checks of SQL/JSON query
functions
contain_mutable_functions() missed three cases where JSON_VALUE() and
JSON_QUERY() depend on DateStyle or TimeZone. With a NULL path, PASSING
and DEFAULT were not checked. The coercion to the RETURNING type was
not checked. JSON_VALUE() prints datetime items using DateStyle.
Existing indexes using such expressions will fail on restore and
pg_upgrade.
Reported-by: chunling qin <303677365@qq.com>
Discussion: https://postgr.es/m/19840-7d5243be84d7ecd3@postgresql.org
Discussion: https://postgr.es/m/19841-d35115c4b7bf1a37@postgresql.org
Discussion: https://postgr.es/m/19850-650941b6eb721873@postgresql.org
Backpatch-through: 17
---
src/backend/optimizer/util/clauses.c | 45 +++++++++++++++++--
src/backend/utils/adt/jsonpath.c | 17 ++++++-
src/include/utils/jsonpath.h | 2 +
.../regress/expected/sqljson_queryfuncs.out | 8 ++++
src/test/regress/sql/sqljson_queryfuncs.sql | 5 +++
5 files changed, 72 insertions(+), 5 deletions(-)
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 65324c32669..cfdb1f35d4a 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -369,6 +369,27 @@ contain_mutable_functions(Node *clause)
return contain_mutable_functions_walker(clause, NULL);
}
+/*
+ * Is coercing a JsonExpr result to the given type immutable? The coercion
+ * calls the input function of the type, or of the element type for arrays.
+ */
+static bool
+jsonexpr_coercion_is_immutable(Oid typid)
+{
+ Oid elemtype;
+ Oid typinput;
+ Oid typioparam;
+
+ typid = getBaseType(typid);
+ elemtype = get_element_type(typid);
+ if (OidIsValid(elemtype))
+ typid = getBaseType(elemtype);
+
+ getTypeInputInfo(typid, &typinput, &typioparam);
+
+ return func_volatile(typinput) == PROVOLATILE_IMMUTABLE;
+}
+
static bool
contain_mutable_functions_checker(Oid func_id, void *context)
{
@@ -416,6 +437,7 @@ contain_mutable_functions_walker(Node *node, void *context)
{
JsonExpr *jexpr = castNode(JsonExpr, node);
Const *cnst;
+ bool returns_datetime = false;
if (!IsA(jexpr->path_spec, Const))
return true;
@@ -423,12 +445,27 @@ contain_mutable_functions_walker(Node *node, void *context)
cnst = castNode(Const, jexpr->path_spec);
Assert(cnst->consttype == JSONPATHOID);
- if (cnst->constisnull)
- return false;
- if (jspIsMutable(DatumGetJsonPathP(cnst->constvalue),
- jexpr->passing_names, jexpr->passing_values))
+ /* With a NULL path, still check PASSING and DEFAULT below */
+ if (!cnst->constisnull &&
+ jspIsMutableExtended(DatumGetJsonPathP(cnst->constvalue),
+ jexpr->passing_names, jexpr->passing_values,
+ &returns_datetime))
+ return true;
+
+ if ((jexpr->use_io_coercion || jexpr->use_json_coercion) &&
+ !jsonexpr_coercion_is_immutable(jexpr->returning->typid))
return true;
+
+ /* JSON_VALUE() prints datetime items using DateStyle */
+ if (returns_datetime &&
+ jexpr->op == JSON_VALUE_OP &&
+ jexpr->returning->typid != JSONOID &&
+ jexpr->returning->typid != JSONBOID &&
+ !jexpr->use_json_coercion)
+ return true;
+
+ /* Check all subnodes */
}
if (IsA(node, SQLValueFunction))
diff --git a/src/backend/utils/adt/jsonpath.c b/src/backend/utils/adt/jsonpath.c
index 11e6193e964..1eddcc10d7e 100644
--- a/src/backend/utils/adt/jsonpath.c
+++ b/src/backend/utils/adt/jsonpath.c
@@ -1271,9 +1271,21 @@ static enum JsonPathDatatypeStatus jspIsMutableWalker(JsonPathItem *jpi,
*/
bool
jspIsMutable(JsonPath *path, List *varnames, List *varexprs)
+{
+ return jspIsMutableExtended(path, varnames, varexprs, NULL);
+}
+
+/*
+ * Like jspIsMutable(), but also report whether the path may return a
+ * datetime value.
+ */
+bool
+jspIsMutableExtended(JsonPath *path, List *varnames, List *varexprs,
+ bool *returns_datetime)
{
struct JsonPathMutableContext cxt;
JsonPathItem jpi;
+ enum JsonPathDatatypeStatus status;
cxt.varnames = varnames;
cxt.varexprs = varexprs;
@@ -1282,7 +1294,10 @@ jspIsMutable(JsonPath *path, List *varnames, List *varexprs)
cxt.mutable = false;
jspInit(&jpi, path);
- (void) jspIsMutableWalker(&jpi, &cxt);
+ status = jspIsMutableWalker(&jpi, &cxt);
+
+ if (returns_datetime)
+ *returns_datetime = (status != jpdsNonDateTime);
return cxt.mutable;
}
diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h
index ee35698d083..d084e489bce 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -205,6 +205,8 @@ extern char *jspGetString(JsonPathItem *v, int32 *len);
extern bool jspGetArraySubscript(JsonPathItem *v, JsonPathItem *from,
JsonPathItem *to, int i);
extern bool jspIsMutable(JsonPath *path, List *varnames, List *varexprs);
+extern bool jspIsMutableExtended(JsonPath *path, List *varnames,
+ List *varexprs, bool *returns_datetime);
extern const char *jspOperationName(JsonPathItemType type);
diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out
index 3ef4dc48286..2b4b707a2e0 100644
--- a/src/test/regress/expected/sqljson_queryfuncs.out
+++ b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -1310,6 +1310,14 @@ ERROR: functions in index expression must be marked IMMUTABLE
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, $.a ? (@.datetime("HH:MI") == $x)]' PASSING '12:34'::time AS x));
CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' DEFAULT random()::int ON ERROR));
ERROR: functions in index expression must be marked IMMUTABLE
+-- PASSING and DEFAULT with a NULL path, RETURNING coercion, datetime output
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, NULL DEFAULT random()::int ON EMPTY));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING date));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$.datetime("YYYY-MM-DD")'));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING int));
-- DEFAULT expression
CREATE OR REPLACE FUNCTION ret_setint() RETURNS SETOF integer AS
$$
diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql
index 819ff3da218..bbf62ef8841 100644
--- a/src/test/regress/sql/sqljson_queryfuncs.sql
+++ b/src/test/regress/sql/sqljson_queryfuncs.sql
@@ -438,6 +438,11 @@ CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, $.a ? (@.datetime()
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, 0 to $.a ? (@.datetime() == $x)]' PASSING '12:34'::time AS x));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, $.a ? (@.datetime("HH:MI") == $x)]' PASSING '12:34'::time AS x));
CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' DEFAULT random()::int ON ERROR));
+-- PASSING and DEFAULT with a NULL path, RETURNING coercion, datetime output
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, NULL DEFAULT random()::int ON EMPTY));
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING date));
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$.datetime("YYYY-MM-DD")'));
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING int));
-- DEFAULT expression
CREATE OR REPLACE FUNCTION ret_setint() RETURNS SETOF integer AS
--
2.37.1 (Apple Git-137.1)
v1-0001-Fix-mutability-checks-of-SQL-JSON-query-functions.patch (application/octet-stream, 7.7K) download
From 7117bb1f6d7027fba19666ad03632a3870419355 Mon Sep 17 00:00:00 2001
From: shihao zhong <zhong950419@gmail.com>
Date: Mon, 9 Nov 2026 22:55:20 -0500
Subject: [PATCH v1] Fix mutability checks of SQL/JSON query functions
contain_mutable_functions() missed three cases where JSON_VALUE() and
JSON_QUERY() depend on DateStyle or TimeZone. With a NULL path, PASSING
and DEFAULT were not checked. The coercion to the RETURNING type was
not checked. JSON_VALUE() prints datetime items using DateStyle.
Existing indexes using such expressions will fail on restore and
pg_upgrade.
Reported-by: chunling qin <303677365@qq.com>
Discussion: https://postgr.es/m/19840-7d5243be84d7ecd3@postgresql.org
Discussion: https://postgr.es/m/19841-d35115c4b7bf1a37@postgresql.org
Discussion: https://postgr.es/m/19850-650941b6eb721873@postgresql.org
Backpatch-through: 17
---
src/backend/optimizer/util/clauses.c | 45 +++++++++++++++++--
src/backend/utils/adt/jsonpath.c | 17 ++++++-
src/include/utils/jsonpath.h | 2 +
.../regress/expected/sqljson_queryfuncs.out | 14 ++++++
src/test/regress/sql/sqljson_queryfuncs.sql | 6 +++
5 files changed, 79 insertions(+), 5 deletions(-)
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index bfba2ce0733..f7a25693206 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -404,6 +404,27 @@ contain_mutable_functions(Node *clause)
return contain_mutable_functions_walker(clause, NULL);
}
+/*
+ * Is coercing a JsonExpr result to the given type immutable? The coercion
+ * calls the input function of the type, or of the element type for arrays.
+ */
+static bool
+jsonexpr_coercion_is_immutable(Oid typid)
+{
+ Oid elemtype;
+ Oid typinput;
+ Oid typioparam;
+
+ typid = getBaseType(typid);
+ elemtype = get_element_type(typid);
+ if (OidIsValid(elemtype))
+ typid = getBaseType(elemtype);
+
+ getTypeInputInfo(typid, &typinput, &typioparam);
+
+ return func_volatile(typinput) == PROVOLATILE_IMMUTABLE;
+}
+
static bool
contain_mutable_functions_checker(Oid func_id, void *context)
{
@@ -451,6 +472,7 @@ contain_mutable_functions_walker(Node *node, void *context)
{
JsonExpr *jexpr = castNode(JsonExpr, node);
Const *cnst;
+ bool returns_datetime = false;
if (!IsA(jexpr->path_spec, Const))
return true;
@@ -458,12 +480,27 @@ contain_mutable_functions_walker(Node *node, void *context)
cnst = castNode(Const, jexpr->path_spec);
Assert(cnst->consttype == JSONPATHOID);
- if (cnst->constisnull)
- return false;
- if (jspIsMutable(DatumGetJsonPathP(cnst->constvalue),
- jexpr->passing_names, jexpr->passing_values))
+ /* With a NULL path, still check PASSING and DEFAULT below */
+ if (!cnst->constisnull &&
+ jspIsMutableExtended(DatumGetJsonPathP(cnst->constvalue),
+ jexpr->passing_names, jexpr->passing_values,
+ &returns_datetime))
+ return true;
+
+ if ((jexpr->use_io_coercion || jexpr->use_json_coercion) &&
+ !jsonexpr_coercion_is_immutable(jexpr->returning->typid))
return true;
+
+ /* JSON_VALUE() prints datetime items using DateStyle */
+ if (returns_datetime &&
+ jexpr->op == JSON_VALUE_OP &&
+ jexpr->returning->typid != JSONOID &&
+ jexpr->returning->typid != JSONBOID &&
+ !jexpr->use_json_coercion)
+ return true;
+
+ /* Check all subnodes */
}
if (IsA(node, SQLValueFunction))
diff --git a/src/backend/utils/adt/jsonpath.c b/src/backend/utils/adt/jsonpath.c
index 7bfc18c9888..89b4f239132 100644
--- a/src/backend/utils/adt/jsonpath.c
+++ b/src/backend/utils/adt/jsonpath.c
@@ -1379,9 +1379,21 @@ static enum JsonPathDatatypeStatus jspIsMutableWalker(JsonPathItem *jpi,
*/
bool
jspIsMutable(JsonPath *path, List *varnames, List *varexprs)
+{
+ return jspIsMutableExtended(path, varnames, varexprs, NULL);
+}
+
+/*
+ * Like jspIsMutable(), but also report whether the path may return a
+ * datetime value.
+ */
+bool
+jspIsMutableExtended(JsonPath *path, List *varnames, List *varexprs,
+ bool *returns_datetime)
{
struct JsonPathMutableContext cxt;
JsonPathItem jpi;
+ enum JsonPathDatatypeStatus status;
cxt.varnames = varnames;
cxt.varexprs = varexprs;
@@ -1390,7 +1402,10 @@ jspIsMutable(JsonPath *path, List *varnames, List *varexprs)
cxt.mutable = false;
jspInit(&jpi, path);
- (void) jspIsMutableWalker(&jpi, &cxt);
+ status = jspIsMutableWalker(&jpi, &cxt);
+
+ if (returns_datetime)
+ *returns_datetime = (status != jpdsNonDateTime);
return cxt.mutable;
}
diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h
index 8d27206e242..a387ae0d7d2 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -213,6 +213,8 @@ extern char *jspGetString(JsonPathItem *v, int32 *len);
extern bool jspGetArraySubscript(JsonPathItem *v, JsonPathItem *from,
JsonPathItem *to, int i);
extern bool jspIsMutable(JsonPath *path, List *varnames, List *varexprs);
+extern bool jspIsMutableExtended(JsonPath *path, List *varnames,
+ List *varexprs, bool *returns_datetime);
extern const char *jspOperationName(JsonPathItemType type);
diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out
index c0086de4c7e..e7b4b0cab48 100644
--- a/src/test/regress/expected/sqljson_queryfuncs.out
+++ b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -1374,6 +1374,20 @@ CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.upper()'));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.initcap()'));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.replace("hello", "bye")'));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.split_part(",", 2)'));
+-- PASSING and DEFAULT with a NULL path, RETURNING coercion, datetime output
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, NULL DEFAULT random()::int ON EMPTY));
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, NULL D...
+ ^
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING date));
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RE...
+ ^
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$.datetime("YYYY-MM-DD")'));
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$.dat...
+ ^
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING int));
-- DEFAULT expression
CREATE OR REPLACE FUNCTION ret_setint() RETURNS SETOF integer AS
$$
diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql
index d5ea5b3d8d2..5020c3c50fa 100644
--- a/src/test/regress/sql/sqljson_queryfuncs.sql
+++ b/src/test/regress/sql/sqljson_queryfuncs.sql
@@ -448,6 +448,12 @@ CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.initcap()'));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.replace("hello", "bye")'));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.split_part(",", 2)'));
+-- PASSING and DEFAULT with a NULL path, RETURNING coercion, datetime output
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, NULL DEFAULT random()::int ON EMPTY));
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING date));
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$.datetime("YYYY-MM-DD")'));
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING int));
+
-- DEFAULT expression
CREATE OR REPLACE FUNCTION ret_setint() RETURNS SETOF integer AS
$$
--
2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-11-10 04:35 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-07 04:12 BUG #19841: SQL/JSON RETURNING coercion to GUC-dependent types (timestamptz etc.) is wrongly judged IMMUTABLE - PG Bug reporting form <noreply@postgresql.org>
2026-11-10 04:35 ` shihao zhong <zhong950419@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