From 5b0562bb59fe1f1d52fe506d10d953bf007b4aa5 Mon Sep 17 00:00:00 2001 From: shihao zhong 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)