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 #19841: SQL/JSON RETURNING coercion to GUC-dependent types (timestamptz etc.) is wrongly judged IMMUTABLE -
Date: Sat, 07 Nov 2026 04:12:26 +0000
Message-ID: <19841-d35115c4b7bf1a37@postgresql.org> (raw)

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:        

**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 — 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 = 'UTC';
SET DateStyle = '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 = '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 = '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 = 'Asia/Tokyo';
SET enable_seqscan=off; SET enable_bitmapscan=off;
SELECT count(*) AS idx_build_literal FROM jr_wr
 WHERE JSON_VALUE(doc,'$' RETURNING timestamptz) = '2020-01-01
12:34:56+00'::timestamptz;  -- 500 (frozen)
SET enable_seqscan=on; SET enable_indexscan=off; SET enable_bitmapscan=off;
SELECT count(*) AS seq_build_literal FROM jr_wr
 WHERE JSON_VALUE(doc,'$' RETURNING timestamptz) = '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 = '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 = '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.








view thread (2+ messages)  latest in thread

Message-ID: <19841-d35115c4b7bf1a37@postgresql.org>
Permalink:  ../19841-d35115c4b7bf1a37@postgresql.org/
Also on:    postgresql.org/message-id/19841-d35115c4b7bf1a37@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 #19841: SQL/JSON RETURNING coercion to GUC-dependent types (timestamptz etc.) is wrongly judged IMMUTABLE -
  In-Reply-To: <19841-d35115c4b7bf1a37@postgresql.org>
  References: <19841-d35115c4b7bf1a37@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