BUG #19850: SQL/JSON .datetime() output depends on DateStyle/TimeZone but is judged IMMUTABLE - expression index

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: 303677365(at)qq(dot)com
Subject: BUG #19850: SQL/JSON .datetime() output depends on DateStyle/TimeZone but is judged IMMUTABLE - expression index
Date: 2026-11-08 03:25:19
Message-ID: 19850-650941b6eb721873@postgresql.org
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 19850
Logged by: chunling qin
Email address: 303677365(at)qq(dot)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.

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2026-11-08 03:28:37 BUG #19851: Adding a tautological CHECK(true) to a domain changes JSON_VALUE's result (silent NULL, value/type c
Previous Message shihao zhong 2026-11-08 03:23:00 Re: BUG #19808: NOT NULL NO INHERIT copied by CREATE TABLE (LIKE) not enforced on partitioned table