Subject: Re: pgsql: Add more SQL/JSON constructor functions

From: Tom Lane <[email-masked]>
Date: Wed, 29 May 2024 09:44:35 -0700

Amit Langote <[email-masked]> writes:
> On Mon, May 27, 2024 at 7:10 PM Alvaro Herrera <[email-masked]> wrote:
>> On 2024-May-27, Alvaro Herrera wrote:
>> I just noticed this behavior, which looks like a bug to me:
>> 
>> select json_serialize('{"a":1, "a":2}' returning varchar(5));
>> json_serialize
>> ────────────────
>> {"a":
>> 
>> I think this function should throw an error if the destination type
>> doesn't have room for the output json.  Otherwise, what good is the
>> serialization function?

> This behavior comes from using COERCE_EXPLICIT_CAST when creating the
> coercion expression to convert json_*() functions' argument to the
> RETURNING type.

Yeah, I too think this is a cast, and truncation is the spec-defined
behavior for casting to varchar with a specific length limit.  I see
little reason that this should work differently from

select json_serialize('{"a":1, "a":2}' returning text)::varchar(5);
 json_serialize 
----------------
 {"a":
(1 row)

			regards, tom lane

---

From: Peter Eisentraut <[email-masked]>
Date: Sun, 2 Jun 2024 23:17:06 +0200

> select json_serialize('{"a":1, "a":2}' returning text)::varchar(5);

The SQL standard says essentially that the output of json_serialize() is 
some string that when parsed back in gives you an equivalent JSON value 
as the input.  That doesn't seem compatible with truncating the output.

If you want output truncation, you can of course use an actual cast. 
But it makes sense that the RETURNING clause is separate from that.

---

From: Tom Lane <[email-masked]>
Date: Mon, 03 Jun 2024 00:46:36 -0400

Peter Eisentraut <[email-masked]> writes:
>> behavior for casting to varchar with a specific length limit.

> as the input.  That doesn't seem compatible with truncating the output.

Maybe you should take this up with the SQL committee?  If you don't
like our current behavior, then either you have to say that RETURNING
with a length-limited target type is illegal (which is problematic
for the spec, since they have no such type) or that the cast behaves
like an implicit cast, with errors for overlength input (which I find
to be an unintuitive definition for a construct that names the target
type explicitly).

> But it makes sense that the RETURNING clause is separate from that.

Are you trying to say that the RETURNING clause's specified type
isn't the actual output type?  I can't buy that either.

Again, if you think our existing behavior isn't right, I think
it's a problem for the SQL committee not us.

			regards, tom lane

---

From: Peter Eisentraut <[email-masked]>
Date: Mon, 3 Jun 2024 11:15:37 +0200

On 02.06.24 21:46, Tom Lane wrote:
> If you don't

It asks for the latter behavior, essentially (but it's not defined in 
terms of casts).  It says:

"""
ii) Let JV be an implementation-dependent (UV097) value of type TT and 
encoding ENC such that these two conditions hold:

1) JV is a JSON text.

2) When the General Rules of Subclause 9.42, “Parsing JSON text”, are 
applied with JV as JSON TEXT, FO as FORMAT OPTION, and WITHOUT UNIQUE 
KEYS as UNIQUENESS CONSTRAINT; let CST be the STATUS and let CSJI be the 
SQL/JSON ITEM returned from the application of those General Rules, CST 
is successful completion (00000) and CSJI is an SQL/JSON item that is 
equivalent to SJI.

If there is no such JV, then let ST be the exception condition: data 
exception — invalid JSON text (22032).

iii) If JV is longer than the length or maximum length of TT, then an 
exception condition is raised: data exception — string data, right 
truncation (22001).
"""

Oracle also behaves accordingly:

SQL> select json_serialize('{"a":1, "a":2}' returning varchar2(20)) from 
dual;

JSON_SERIALIZE('{"A"
--------------------
{"a":1,"a":2}

SQL> select json_serialize('{"a":1, "a":2}' returning varchar2(5)) from 
dual;
select json_serialize('{"a":1, "a":2}' returning varchar2(5)) from dual
                                                                    *
ERROR at line 1:
ORA-40478: output value too large (maximum: 5)
JZN-00018: Input to serializer is too large
Help: https://docs.oracle.com/error-help/db/ora-40478/

As opposed to:

SQL> select cast(json_serialize('{"a":1, "a":2}') as varchar2(5)) from dual;

CAST(
-----
{"a":

---

From: Tom Lane <[email-masked]>
Date: Mon, 03 Jun 2024 13:20:22 -0400

> terms of casts).  It says:

Meh.  Who needs consistency?  But I guess the answer is to do what was
suggested earlier and change the code to use COERCE_IMPLICIT_CAST.

			regards, tom lane

---

From: Amit Langote <[email-masked]>
Date: Tue, 4 Jun 2024 19:03:18 +0900

> suggested earlier and change the code to use COERCE_IMPLICIT_CAST.

OK, will post a patch to do so in a new thread on -hackers.

-- 
Thanks, Amit Langote