Re: ON ERROR in json_query and the like

> On 21.06.2024, at 03:00, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
> On Thu, Jun 20, 2024 at 5:22 PM Amit Langote <amitlangote09(at)gmail(dot)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 supplied with something that cannot be cast to json. I'd document them like functions that accept json with the implications that 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 successful completion (00000), then an SQL/JSON sequence. For conciseness, the result will be stated either as an exception condition 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 of the 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 ST be 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 5b ultimately 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