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 1xPzfC-000000014YG-0XAf for pgsql-bugs@arkaria.postgresql.org; Sun, 08 Nov 2026 09:58:15 +0000 Received: from localhost ([127.0.0.1]:39208 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xPzfC-00000005zLa-0FB0 for pgsql-bugs@arkaria.postgresql.org; Sun, 08 Nov 2026 09:58:14 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:38056) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xPtXk-00000005LD4-2NiO for pgsql-bugs@lists.postgresql.org; Sun, 08 Nov 2026 03:26:08 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xPtXh-00000000nnW-3IQx for pgsql-bugs@lists.postgresql.org; Sun, 08 Nov 2026 03:26:07 +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=fN8MmfCgAdeEd3JOA0EmDKK7+J6fYzjlPnJQ9iL2exY=; b=WpoS2vS1RHByJMvTyQvofFTTpf doyEqrKEtyy+PCns3WUBbGN+pGakE2mzV282RPedBqYujiM++dNdM6BDqinWULuLJRcPJwC1Z1UqB tKmie0RIIHx6LdsOoUGKT/ZJWA2XqIkqpXHsrWp6HrjvZKvunwamIRGM7lOoYJGTczQCbBT9wE9xE KoEOhngJ89w+EYlGXIFsI1ivsMcQzLkmwgrcfW7Igd1r4HxRshDuSsrlYwFW6yhVihevKPpyoAFg3 sUnjXDcl01gagIUcSAGLgaKh/npWLQgdwiMWW954PNIU/txhVY+Eri5BYSrXcsZ25Ibks6z8SbnDT OrnwxMsw==; 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 1xPtXg-00000002GYB-3Euf for pgsql-bugs@lists.postgresql.org; Sun, 08 Nov 2026 03:26: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 1xPtXe-00000005ZO5-3xuc for pgsql-bugs@lists.postgresql.org; Sun, 08 Nov 2026 03:26:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19850: SQL/JSON .datetime() output depends on DateStyle/TimeZone but is judged IMMUTABLE - expression index 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: Sun, 08 Nov 2026 03:25:19 +0000 Message-ID: <19850-650941b6eb721873@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: 19850 Logged by: chunling qin Email address: 303677365@qq.com PostgreSQL version: 18.6 Operating system: Linux x86_64; platform-independent (reproduced ide Description: =20 **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 =E2=80=94 after a DateStyle change t= he 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 =3D 'UTC'; -- (1) the value depends on DateStyle (same JSON document): SET DateStyle =3D '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 =3D '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 =3D '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 =3D 'SQL, DMY'; SET enable_seqscan=3Doff; SET enable_bitmapscan=3Doff; SELECT 'idx' AS tag, count(*) AS n FROM t WHERE JSON_VALUE(d, '$.datetime("YYYY-MM-DD")' RETURNING text) =3D '15/08/2023'; -- idx | 0 (uses values frozen at build time: '2023-08-15' !=3D '15/08/2023') RESET enable_seqscan; SET enable_indexscan=3Doff; SET enable_bitmapscan=3Do= ff; SELECT 'seq' AS tag, count(*) AS n FROM t WHERE JSON_VALUE(d, '$.datetime("YYYY-MM-DD")' RETURNING text) =3D '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 =3D '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 =3D 'German, DMY'; INSERT INTO tu VALUES (2, '"2023-08-15"'::jsonb); -- accepted: current value 15.08.2023 differs SET DateStyle =3D '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 =E2=80=94 as the equivalent direct rendering outside SQL/JSON would= be.