| 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.
| 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 |