pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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).








view thread (2+ messages)  latest in thread

Message-ID: <19840-7d5243be84d7ecd3@postgresql.org>
Permalink:  ../19840-7d5243be84d7ecd3@postgresql.org/
Also on:    postgresql.org/message-id/19840-7d5243be84d7ecd3@postgresql.org

 · 

reply

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