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-0JLn 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-4C3s for pgsql-bugs@arkaria.postgresql.org; Sat, 07 Nov 2026 16:01:34 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:49328) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xPXni-00000002IRB-3AGu for pgsql-bugs@lists.postgresql.org; Sat, 07 Nov 2026 04:13:10 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xPXnf-00000000klV-2Up8 for pgsql-bugs@lists.postgresql.org; Sat, 07 Nov 2026 04:13:10 +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=0DJzJ2DSiI9BCznl4IrAfmVPYUBTGfkKzGeVu08So9g=; b=BAQK7gYvhBk4SGFFrY0VQalWcN vmotbJf/7DJmGWOqdileRdGN8xc0YqjyRBUzl1AenKVqa+D4hIVVgrKHCJrRI0tlM7yvdLnUnNyw9 SMm8ddWFVAQ2ZCynvXKxV4lYV3dfgKHkV4y1YVeybm/tqKLkEUghqrhak18/7vRQBLYSkOLSAJvWU xKZ3D8IM7x/Y2nuyjBSgiCLVEm4ik/t0h6EaFmx53fE03O4JpLDrJpqtFJxb2oZnaraL45zepfqfG w3xGJTz+uwwR/9VnwsubyfouxGDo+MWAe/8XK9GfGg8FLm13c01ZWNvM8Ww1dB1Exil7Zc+B9e69Z wpIggSUA==; 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 1xPXnc-00000001oKt-3yZF for pgsql-bugs@lists.postgresql.org; Sat, 07 Nov 2026 04:13: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 1xPXna-00000004X1T-2usQ for pgsql-bugs@lists.postgresql.org; Sat, 07 Nov 2026 04:13:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19841: SQL/JSON RETURNING coercion to GUC-dependent types (timestamptz etc.) is wrongly judged IMMUTABLE - 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 04:12:26 +0000 Message-ID: <19841-d35115c4b7bf1a37@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: 19841 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:** `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 =E2=80=94 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 =3D 'UTC'; SET DateStyle =3D '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 =3D '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 =3D '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 =3D 'Asia/Tokyo'; SET enable_seqscan=3Doff; SET enable_bitmapscan=3Doff; SELECT count(*) AS idx_build_literal FROM jr_wr WHERE JSON_VALUE(doc,'$' RETURNING timestamptz) =3D '2020-01-01 12:34:56+00'::timestamptz; -- 500 (frozen) SET enable_seqscan=3Don; SET enable_indexscan=3Doff; SET enable_bitmapscan= =3Doff; SELECT count(*) AS seq_build_literal FROM jr_wr WHERE JSON_VALUE(doc,'$' RETURNING timestamptz) =3D '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 =3D '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 =3D '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.