Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xPirG-00000000rTZ-1Did for pgsql-bugs@arkaria.postgresql.org; Sat, 07 Nov 2026 16:01:35 +0000 Received: from localhost ([127.0.0.1]:57122 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xPirF-000000049lg-49ZG for pgsql-bugs@arkaria.postgresql.org; Sat, 07 Nov 2026 16:01:33 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:54348) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xPXUM-00000002IFM-0NLC for pgsql-bugs@lists.postgresql.org; Sat, 07 Nov 2026 03:53:10 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xPXUH-00000000eps-3LMT for pgsql-bugs@lists.postgresql.org; Sat, 07 Nov 2026 03:53:09 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=XkUff/erbQbAS/FFT0FuP4KJd/7iXGilAK1PacB2NCs=; b=LS4f4D1O/d01SXjedIdEG828Mc qlJ2RK2FTIVjmfim+GnLRdoqaiL0AVoIKUTqEDfl3oWsX/tjbcFb173iNc/qCBy14pUuf8ukLpenA toHcpa2n4kwPjM6+SnODJVAB+Eb3NWm2HpSNk7jk8HktErlYXuu1bCgahpagpGly+QhMhalotBJWt eeTtboDRchNZzn9yK0mQ3A49b3Iej7ZZwmj8Z15UcpOANmMbgNiKsjgVWixIZRSHR7NUwERdPC9M4 2KQGi+JOVHII9Bd/3GhSLsgqRDlpJl0MR+iNRtaAuKqA4vnPcz+Mx0gVMVlgPHq2DyBF269l6hC7X dt4wRPsA==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xPXUG-00000001nwM-3ZGn for pgsql-bugs@lists.postgresql.org; Sat, 07 Nov 2026 03:53:06 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xPXUE-00000004Vjx-3Dya for pgsql-bugs@lists.postgresql.org; Sat, 07 Nov 2026 03:53:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19840: contain_mutable_functions() misses VOLATILE sub-expressions in SQL/JSON functions when the jsonpath To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: 303677365@qq.com Reply-To: 303677365@qq.com, pgsql-bugs@lists.postgresql.org Date: Sat, 07 Nov 2026 03:52:57 +0000 Message-ID: <19840-7d5243be84d7ecd3@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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: =20 **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 =E2=80=94 and the= n 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 =E2=80=94 only the mutable-check is asymmetric. **Reproduction (self-contained SQL; this exact script verified on master = =E2=80=94 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 =3D '1'; -- value used when the index is bui= lt 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 =3D '2'; -- both plans answer the SAME query; they must agree: SET enable_seqscan=3Doff; SET enable_bitmapscan=3Doff; SELECT 'idxscan' tag, count(*) AS n FROM t WHERE JSON_VALUE(j, NULL::jsonpath RETURNING d_jb DEFAULT g() ON ERROR) =3D 1; -- idxscan | 5 (uses the values frozen at index-build time) RESET enable_seqscan; SET enable_indexscan=3Doff; SET enable_bitmapscan=3Do= ff; SELECT 'seqscan' tag, count(*) AS n FROM t WHERE JSON_VALUE(j, NULL::jsonpath RETURNING d_jb DEFAULT g() ON ERROR) =3D 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 = =E2=80=94 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).