From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 303677365@qq.com
Subject: BUG #19840: contain_mutable_functions() misses VOLATILE sub-expressions in SQL/JSON functions when the jsonpath
Date: Sat, 07 Nov 2026 03:52:57 +0000
Message-ID: <19840-7d5243be84d7ecd3@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 19840
Logged by: chunling qin
Email address: 303677365@qq.com
PostgreSQL version: 18.6
Operating system: Linux x86_64; platform-independent (reproduced on
Description:
**Description:**
When an SQL/JSON function's path expression is a NULL jsonpath constant,
`contain_mutable_functions()` wrongly reports the whole expression as
immutable, so VOLATILE sub-expressions in the same JsonExpr (RETURNING
coercion, PASSING values, DEFAULT/ON ERROR expressions) escape the check. As
a result:
- expression indexes that should be rejected with `functions in index
expression must be marked IMMUTABLE` (42P17) are accepted — and then return
wrong results once the volatile value changes (index scan vs seq scan
disagree);
- partition-key and stored-generated-column checks are bypassed the same
way;
- a NON-NULL constant jsonpath with the very same VOLATILE sub-expressions
is correctly rejected, showing the NULL branch is the defect.
**Root cause:** `src/backend/optimizer/util/clauses.c`,
`contain_mutable_functions_walker()` JsonExpr branch (around lines 450-466):
```c
if (cnst->constisnull) return false; /* returns "no mutable functions" for
the
whole subtree and does NOT recurse
*/
```
This is the only early-return in clauses.c that skips
`expression_tree_walker()` recursion. The sibling walkers
`contain_volatile_functions_walker()` and `max_parallel_hazard_walker()`
have no such special case and always recurse, so they correctly find the
VOLATILE function — only the mutable-check is asymmetric.
**Reproduction (self-contained SQL; this exact script verified on master —
partition-key face also verified on 18.6):**
```sql
CREATE DOMAIN d_jb AS int NOT NULL;
CREATE FUNCTION g() RETURNS int LANGUAGE plpgsql VOLATILE AS
$$ BEGIN RETURN current_setting('my.g', true)::int; END $$;
SET my.g = '1'; -- value used when the index is built
CREATE TABLE t(id int, j jsonb);
INSERT INTO t SELECT g, '{}'::jsonb FROM generate_series(1,5) g;
-- CONTROL: same VOLATILE expression, NON-NULL const jsonpath -> correctly
rejected
CREATE INDEX t_ctrl ON t (JSON_VALUE(j, '$.a' RETURNING d_jb DEFAULT g() ON
ERROR));
-- ERROR: functions in index expression must be marked IMMUTABLE
-- TEST: NULL const jsonpath -> accepted (should have been rejected too)
CREATE INDEX t_test ON t (JSON_VALUE(j, NULL::jsonpath RETURNING d_jb
DEFAULT g() ON ERROR));
-- CREATE INDEX (no 42P17!)
ANALYZE t;
-- change the volatile function's value AFTER the index was built
SET my.g = '2';
-- both plans answer the SAME query; they must agree:
SET enable_seqscan=off; SET enable_bitmapscan=off;
SELECT 'idxscan' tag, count(*) AS n FROM t
WHERE JSON_VALUE(j, NULL::jsonpath RETURNING d_jb DEFAULT g() ON ERROR) =
1;
-- idxscan | 5 (uses the values frozen at index-build time)
RESET enable_seqscan; SET enable_indexscan=off; SET enable_bitmapscan=off;
SELECT 'seqscan' tag, count(*) AS n FROM t
WHERE JSON_VALUE(j, NULL::jsonpath RETURNING d_jb DEFAULT g() ON ERROR) =
1;
-- seqscan | 0 (re-evaluates the volatile function now)
RESET enable_indexscan; RESET enable_bitmapscan;
-- partition-key check bypassed the same way (also verified on 18.6):
CREATE TABLE tp(id int, j jsonb)
PARTITION BY RANGE (JSON_VALUE(j, NULL::jsonpath RETURNING d_jb DEFAULT
g() ON ERROR));
-- CREATE TABLE (accepted)
```
**Actual results:** with a NULL jsonpath the index and the partitioned table
are created without the 42P17 error; after the volatile value changes, the
index scan returns 5 while the seq scan returns 0 for the same query.
**Expected behavior:** a NULL jsonpath constant must not short-circuit the
mutability check of the surrounding JsonExpr; the expression should be
treated as mutable whenever any sub-expression (RETURNING coercion, PASSING
values, DEFAULT/ON ERROR expressions) contains STABLE/VOLATILE functions —
as already happens for a non-NULL jsonpath.
**Impact:** silent wrong results from stale expression indexes; UNIQUE
constraints built on such expressions fail to enforce; wrong partition
routing; stored generated columns computed once and never re-checked. The
affected callers include the index-expression check (indexcmds.c),
partition-key check (tablecmds.c), generated-column check, and SQL-function
inlining volatility checks (clauses.c).
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, 303677365@qq.com
Subject: Re: BUG #19840: contain_mutable_functions() misses VOLATILE sub-expressions in SQL/JSON functions when the jsonpath
In-Reply-To: <19840-7d5243be84d7ecd3@postgresql.org>
References: <19840-7d5243be84d7ecd3@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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