Feedback-ID: ie0a040ee:Fastmail
Message-ID: <5e8879d0-a3c8-4be2-950f-d83aa2af953a@eisentraut.org>
Date: Wed, 11 Sep 2024 12:20:41 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Document DateStyle effect on jsonpath string()
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "David E. Wheeler" <david@justatheory.com>,
 jian he <jian.universality@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <56955B33-6959-4FDA-A459-F00363ECDFEE@justatheory.com>
 <CACJufxHREyTFzKOWz3f-yD14HxAdfeP1vGQQ2SQrusZ+ziRJyA@mail.gmail.com>
 <4D874C72-8939-4083-8336-AB114D9E29AD@justatheory.com>
 <CACJufxHE6q47-GEf3Kk0pD+Snr3yZMM4Byig2=bQKV8js7sP-A@mail.gmail.com>
 <B8E7081B-A8BE-4BAF-9C6B-AACD4F6EAB00@justatheory.com>
 <F492B399-37CB-471C-AEAD-25E1C19B607D@justatheory.com>
 <A556B643-F7E6-442D-9235-2D46A8A887C1@justatheory.com>
 <3541398.1725994308@sss.pgh.pa.us>
 <D6137AEA-0BE7-4328-9718-71B7E6BB4F6C@justatheory.com>
 <b847bc16-57b4-4701-870b-c4ed33d71e51@eisentraut.org>
 <3654686.1725999372@sss.pgh.pa.us>
Content-Language: en-US
From: Peter Eisentraut <peter@eisentraut.org>
In-Reply-To: <3654686.1725999372@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/5e8879d0-a3c8-4be2-950f-d83aa2af953a%40eisentraut.org>
Precedence: bulk

On 10.09.24 22:16, Tom Lane wrote:
> Peter Eisentraut <peter@eisentraut.org> writes:
>> These JSON path functions are specified by the SQL standard, so they
>> shouldn't depend on PostgreSQL-specific settings.  At least in new
>> functionality we should avoid that, no?
> 
> Hmm ... but does the standard precisely define the output format?
> 
> Since these conversions are built on our own timestamp I/O code,
> I rather imagine there is quite a lot of behavior there that's
> not to be found in the standard.  That doesn't really trouble
> me as long as the spec's behavior is a subset of it (i.e.,
> reachable as long as you've got the right parameter settings).

Actually, the standard prohibits this call:

"""
XV) If JM specifies string, then:

1) Forallj,1(one)≤j≤n,
Case:

a) If Ij is not a character string, number, or Boolean value,
then let ST be data exception — non-string SQL/JSON item (2202X).

b) Otherwise, let X be an SQL variable whose value is Ij. Let ML be an 
implementation-defined (IL006) maximum
length of variable-length character strings. Let Vj be the result of

CAST (X AS CHARACTER VARYING(ML)

If this conversion results in an exception condition, then
let ST be that exception condition.
"""

So I guess we have extended this and the current behavior is consistent 
with item b).

What I'm concerned about is that this makes the behavior of JSON_QUERY 
non-immutable.  Maybe there are other reasons for it to be 
non-immutable, in which case this isn't important.  But it might be 
worth avoiding that?