Re: ON ERROR in json_query and the like - Mailing list pgsql-hackers

> On 21.06.2024, at 03:00, David G. Johnston <david.g.johnston@gmail.com> wrote:
>
> On Thu, Jun 20, 2024 at 5:22 PM Amit Langote <amitlangote09@gmail.com> wrote:
>
> Soft error handling *was* used for catching cast errors in the very
> early versions of this patch (long before I got involved and the
> infrastructure you mention got added).  It was taken out after Pavel
> said [1] that he didn't like producing NULL instead of throwing an
> error.  Not sure if Pavel's around but it would be good to know why he
> didn't like it at the time.
>
>
> I'm personally in the "make it error" camp but "make it conform to the standard" is a stronger membership (in
general).
>
> I see this note in your linked thread:
>
> > By the standard, it is implementation-defined whether JSON parsing errors
> > should be caught by ON ERROR clause.
>
> Absent someone contradicting that claim I retract my position here and am fine with failing if these "functions" are
suppliedwith something that cannot be cast to json.  I'd document them like functions that accept json with the
implicationsthat any casting to json happens before the function is called and thus its arguments do not apply to that
step.

That claim was also made in 2020, before the current (2023)
SQL standard was released — yet it might have been the same.

My understanding of the 2023 standard is that ON ERROR
covers invalid JSON because the conversion from a character
string to JSON is done deeply nested inside the JSON_QUERY &
Co functions.

9.47 Processing <JSON API common syntax> Function GR 3
triggers
9.46, “SQL/JSON path language: syntax and semantics”
Where GR 11 says:
————
GR 11) The result of evaluating a <JSON path wff> is a completion condition, and, if that completion condition is
successfulcompletion (00000), then an SQL/JSON sequence. For conciseness, the result will be stated either as an
exceptioncondition or as an SQL/JSON sequence (in the latter case, the completion condition successful completion
(00000)is implicit). Unsuccessful completion conditions are not automatically raised and do not terminate application
ofthe General Rules in this Subclause. 
      a) If <JSON path context variable> JPCV is specified, then
         Case:
        • i)  If PARSED is True, then the result of evaluating JPCV is JT.
        • ii)  If the declared type of JT is JSON, then the result of evaluating JPCV is JT.
        • iii)  Otherwise:
             • 1)  The General Rules of Subclause 9.42, “Parsing JSON text”, are applied with JT as JSON TEXT, an
implementation-defined(IV185) <JSON key uniqueness constraint> as UNIQUENESS CONSTRAINT, and FO as FORMAT OPTION; let
STbe the STATUS and let CISJI be the SQL/JSON ITEM returned from the application of those General Rules. 
             • 2)  Case:
                 • A)  If ST is not successful completion (00000), then the result of evaluating JPCV is ST.
                 • B)  Otherwise, the result of evaluating JPCV is CISJI.
————

In case of an exception, it is passed along to clause 9.44 Converting an SQL/JSON sequence to an SQL/JSON item where GR
5bultimately says (the exception is in TEMPST in the meanwhile): 

——
       • b)  If TEMPST is an exception condition, then Case:
             i) If ONERROR is ERROR, then let OUTST be TEMPST.
             ii) Otherwise, let OUTST be successful completion (00000). Case:
                 • 1)  If ONERROR is NULL, then let JV be the null value.
                 • 2)  If ONERROR is EMPTY ARRAY, then let JV be an SQL/JSON array that has no SQL/JSON elements.
                 • 3)  If ONERROR is EMPTY OBJECT, then let JV be an SQL/JSON object that has no SQL/JSON members.
——

Let me know if I’m missing something here.

The whole idea that a cast is implied outside of JSON_QUERY & co
might be covered by a clause that generally allows implementations
to cast as they like (don’t have the ref at hand, but I think
such a clause is somewhere). On the other hand, the 2023 standard
doesn’t even cover an **explicit** cast from character strings to
JSON as per 6.13 SR 7 (that’ where the matrix of source- and
destination types is given for cast).

So my bet is this:

* I’m pretty sure JSON parsing errors being subject to ON ERROR
  is conforming.
  That’s also “backed” by the Oracle and Db2 (LUW) implementations.

* Implying a CAST might be ok, but I have doubts.

* I don’t see how failing without being subject to ON ERRROR
  (as it is now in 17beta1) could possibly covered by the standard.
  But as we all know: the standard is confusing. If somebody thinks
  differently, references would be greatly appreciated.

-markus