Re: ON ERROR in json_query and the like

> On 21.06.2024, at 07:38, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
> On Thursday, June 20, 2024, Markus Winand <markus(dot)winand(at)winand(dot)at> wrote:
>
>
> > On 21.06.2024, at 06:46, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> >>
>
> >
> > 2 also has the benefit of being standard conforming while 1 does not.
>
> Why do you think so? Do you have any references or is this just based on previous statements in this discussion?
>
>
> Hearsay.
>
> https://www.postgresql.org/message-id/CAFj8pRCnzO2cnHi5ebXciV%3DtuGVvAQOW9uPU%2BDQV1GkL31R%3D-g%40mail.gmail.com
>
> > 4) If ALREADY PARSED is False, then it is implementation-defined whether the
> > following rules are applied:
> > a) The General Rules of Subclause 9.36, "Parsing JSON text", are applied with
> > JT as JSON TEXT, an implementation-defined <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.
> > b) If ST is not successful completion, then ST is returned as the STATUS of
> > this application of these General Rules, and no further General Rules of
> > this Subclause are applied.
>
> But maybe I’m mis-interpreting that snippet and Nikita’s related commentary regarding have chosen between options for this implementation-defined feature.

Ah, here we go. Nowadays this is called IA050, “Whether a JSON context item that is not of the JSON data type is parsed.” (Likewise IA054 “Whether a JSON parameter is parsed.”)

So updating the three options:

> 1. Disallow anything but jsonb for context_item (the patch I posted yesterday)

* Non-conforming
* patch available

> 2. Continue allowing context_item to be non-json character or utf-8
> encoded bytea strings, but document that any parsing errors do not
> respect the ON ERROR clause.

* Conforming by choosing IA050 to implement GR4: raise errors independent of the ON ERROR clause.
* currently committed.

> 3. Go ahead and fix implicit casts to jsonb so that any parsing errors
> respect ON ERROR (no patch written yet).

* Conforming by choosing IA050 not to implement GR4: Parsing happens later, considering the ON ERROR clause.
* no patch available, not trivial

I guess I’m the only one in favour of 3 ;) My remaining arguments are that Oracle and Db2 (LUW) do it that way and also that it is IMHO what users would expect. However, as 2 is also conforming (how could I miss that?), proper documentation is a very tempting option.

-markus
ps: Does anyone know a dialect that implements GR4?