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 #19850: SQL/JSON .datetime() output depends on DateStyle/TimeZone but is judged IMMUTABLE - expression index
Date: Sun, 08 Nov 2026 03:25:19 +0000
Message-ID: <19850-650941b6eb721873@postgresql.org> (raw)

The following bug has been logged on the website:

Bug reference:      19850
Logged by:          chunling qin
Email address:      303677365@qq.com
PostgreSQL version: 18.6
Operating system:   Linux x86_64; platform-independent (reproduced ide
Description:        

**Description:**

`JSON_VALUE(doc, '$.datetime("YYYY-MM-DD")' RETURNING text)` renders the
matched datetime through the type's STABLE output function, so its value
changes with `DateStyle` (and, without a template, with `TimeZone`). The
planner nevertheless judges the whole expression IMMUTABLE, so:

- expression indexes on it are accepted, and their stored values are frozen
at the index's build-time GUC settings — after a DateStyle change the index
scan and the seq scan return different results for the same query;
- a UNIQUE index on it silently fails to enforce after a DateStyle change.

**Reproduction (self-contained SQL; verified identically on official 18.6
and master):**

```sql
SET TimeZone = 'UTC';

-- (1) the value depends on DateStyle (same JSON document):
SET DateStyle = 'ISO, MDY';
SELECT 'iso' AS ds, JSON_VALUE('"2023-08-15"'::jsonb,
'$.datetime("YYYY-MM-DD")' RETURNING text) AS v;
-- 2023-08-15
SET DateStyle = 'German, DMY';
SELECT 'german' AS ds, JSON_VALUE('"2023-08-15"'::jsonb,
'$.datetime("YYYY-MM-DD")' RETURNING text) AS v;
-- 15.08.2023

-- (2) the index is accepted (a STABLE expression should be rejected with
42P17):
SET DateStyle = 'ISO, MDY';
CREATE TABLE t (id int, d jsonb);
INSERT INTO t SELECT i, to_jsonb('2023-08-15'::text) FROM
generate_series(1,500) i;
CREATE INDEX t_i ON t ((JSON_VALUE(d, '$.datetime("YYYY-MM-DD")' RETURNING
text)));  -- accepted
ANALYZE t;

-- (3) wrong results: index built under ISO, queried under SQL,DMY for the
current value '15/08/2023':
SET DateStyle = 'SQL, DMY';
SET enable_seqscan=off; SET enable_bitmapscan=off;
SELECT 'idx' AS tag, count(*) AS n FROM t
 WHERE JSON_VALUE(d, '$.datetime("YYYY-MM-DD")' RETURNING text) =
'15/08/2023';
-- idx | 0    (uses values frozen at build time: '2023-08-15' !=
'15/08/2023')
RESET enable_seqscan; SET enable_indexscan=off; SET enable_bitmapscan=off;
SELECT 'seq' AS tag, count(*) AS n FROM t
 WHERE JSON_VALUE(d, '$.datetime("YYYY-MM-DD")' RETURNING text) =
'15/08/2023';
-- seq | 500  (re-evaluates under the current DateStyle)
RESET enable_indexscan; RESET enable_bitmapscan;

-- (4) UNIQUE silently fails to enforce:
SET DateStyle = 'ISO, MDY';
CREATE TABLE tu (id int, d jsonb);
CREATE UNIQUE INDEX tu_u ON tu ((JSON_VALUE(d, '$.datetime("YYYY-MM-DD")'
RETURNING text)));
INSERT INTO tu VALUES (1, '"2023-08-15"'::jsonb);
SET DateStyle = 'German, DMY';
INSERT INTO tu VALUES (2, '"2023-08-15"'::jsonb);   -- accepted: current
value 15.08.2023 differs
SET DateStyle = 'ISO, MDY';
SELECT id, JSON_VALUE(d, '$.datetime("YYYY-MM-DD")' RETURNING text) AS
indexed_key FROM tu ORDER BY id;
-- 1 | 2023-08-15
-- 2 | 2023-08-15    <- two rows with the same indexed key, UNIQUE not
enforced
RESET DateStyle; RESET TimeZone;
```

**Root cause:** the `.datetime()` path materializes datetime values as text
through the type's STABLE output functions (inside
`ExecGetJsonValueItemString()` and the jsonpath runtime), which
`contain_mutable_functions_walker()`'s JsonExpr branch does not model.

**Expected behavior:** any SQL/JSON expression whose result is rendered by a
STABLE output function (DateStyle/TimeZone-sensitive) must be judged STABLE,
and rejected for index expressions / partition keys / stored generated
columns — as the equivalent direct rendering outside SQL/JSON would be.








Message-ID: <19850-650941b6eb721873@postgresql.org>
Permalink:  ../19850-650941b6eb721873@postgresql.org/
Also on:    postgresql.org/message-id/19850-650941b6eb721873@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 #19850: SQL/JSON .datetime() output depends on DateStyle/TimeZone but is judged IMMUTABLE - expression index
  In-Reply-To: <19850-650941b6eb721873@postgresql.org>
  References: <19850-650941b6eb721873@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