pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #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>
  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-10 04:35  shihao zhong <zhong950419@gmail.com>
  parent: PG Bug reporting form <noreply@postgresql.org>
  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