Re: ON ERROR in json_query and the like

po 17. 6. 2024 v 15:07 odesílatel Markus Winand <markus(dot)winand(at)winand(dot)at>
napsal:

>
> > On 17.06.2024, at 08:20, Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> >
> > Hi,
> >
> > (apologies for not replying to this thread sooner)
> >
> > On Tue, May 28, 2024 at 6:57 PM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
> >> út 28. 5. 2024 v 11:29 odesílatel Markus Winand <
> markus(dot)winand(at)winand(dot)at> napsal:
> >>>
> >>> Hi!
> >>>
> >>> I’ve noticed two “surprising” (to me) behaviors related to
> >>> the “ON ERROR” clause of the new JSON query functions in 17beta1.
> >>>
> >>> 1. JSON parsing errors are not subject to ON ERROR
> >>> Apparently, the functions expect JSONB so that a cast is implied
> >>> when providing TEXT. However, the errors during that cast are
> >>> not subject to the ON ERROR clause.
> >>>
> >>> 17beta1=# SELECT JSON_QUERY('invalid', '$' NULL ON ERROR);
> >>> ERROR: invalid input syntax for type json
> >>> DETAIL: Token "invalid" is invalid.
> >>> CONTEXT: JSON data, line 1: invalid
> >>>
> >>> Oracle DB and Db2 (LUW) both return NULL in that case.
> >>>
> >>> I had a look on the list archive to see if that is intentional but
> >>> frankly speaking these functions came a long way. In case it is
> >>> intentional it might be worth adding a note to the docs.
> >>
> >>
> >> I remember a talk about this subject years ago. Originally the
> JSON_QUERY was designed in similar like Oracle, and casting to jsonb was
> done inside. If I remember this behave depends on the fact, so old SQL/JSON
> has not json type and it was based just on processing of plain text. But
> Postgres has JSON, and JSONB and then was more logical to use these types.
> And because the JSON_QUERY uses these types, and the casting is done before
> the execution of the function, then the clause ON ERROR cannot be handled.
> Moreover, until soft errors Postgres didn't allow handling input errors in
> common functions.
> >>
> >> I think so this difference should be mentioned in documentation.
> >
> > Agree that the documentation needs to be clear about this. I'll update
> > my patch at [1] to add a note next to table 9.16.3. SQL/JSON Query
> > Functions.
>
> Considering another branch of this thread [1] I think the
> "Supported Features” appendix of the docs should mention that as well.
>
> The way I see it is that the standards defines two overloaded
> JSON_QUERY functions, of which PostgreSQL will support only one.
> In case of valid JSON, the implied CAST makes it look as though
> the second variant of these function was supported as well but that
> illusion totally falls apart once the JSON is not valid anymore.
>
> I think it affects the following feature IDs:
>
> - T821, Basic SQL/JSON query operators
> For JSON_VALUE, JSON_TABLE and JSON_EXISTS
> - T828, JSON_QUERY
>
> Also, how hard would it be to add the functions that accept
> character strings? Is there, besides the effort, any thing else
> against it? I’m asking because I believe once released it might
> never be changed — for backward compatibility.
>

It is easy to add the function that accepts text, but when you have
overloaded functions, then varchar or text is the preferred type, and then
the arguments will be casted to text by default instead of json. You can
have one function with argument of type "any", but then the
execution is a little bit slower (outer cast is faster than cast inside
function), and again the Postgres cannot deduce used argument types from
function's argument types.

Probably this can be solved if we introduce a new kind of type, where the
preferred type will be json, or jsonb.

So the problem is in the definition of implementation details about the
mechanism of type deduction (when you use string literal or when you use
string expression).

So now, when you will write json_table(x1 || x2), then and x1 and x2 are of
unknown type, then Postgres can know, so x1 and x2 will be jsonb, but when
there
will be secondary function json_table(text), then Postgres detects problem,
and use preferred type (that is text).

Generally, Postgres supports function overloading and it is working well
between text, int and numeric where constants have different syntax, but
when the constant
literal has the same syntax, there can be problems with hidden casts to
unwanted type, so not overloaded function is ideal. These issues can be
solved in the analysis stage by special code, but again it increases code
complexity.

>
> [1]
> https://www.postgresql.org/message-id/CAKFQuwb50BAaj83Np%2B1O6xe3_T6DO8w2mxtFbgSbbUng%2BabrqA%40mail.gmail.com
>
>
> >
> >>> 2. EMPTY [ARRAY|OBJECT] ON ERROR implies ERROR ON EMPTY
> >>>
> >>> 17beta1=# SELECT JSON_QUERY('[]', '$[*]' EMPTY ARRAY ON ERROR) a;
> >>> a
> >>> ----
> >>> []
> >>> (1 row)
> >>>
> >>> As NULL ON EMPTY is implied, it should give the same result as
> >>> explicitly adding NULL ON EMPTY:
> >>>
> >>> 17beta1=# SELECT JSON_QUERY('[]', '$[*]' NULL ON EMPTY EMPTY ARRAY
> ON ERROR) a;
> >>> a
> >>> ---
> >>>
> >>> (1 row)
> >>>
> >>> Interestingly, Oracle DB gives the same (wrong) results. Db2 (LUW)
> >>> on the other hand returns NULL for both queries.
> >>>
> >>> I don’t think that PostgreSQL should follow Oracle DB's suit here
> >>> but again, in case this is intentional it should be made explicit
> >>> in the docs.
> >
> > This behavior is a bug and result of an unintentional change that I
> > made at some point after getting involved with this patch set. So I'm
> > going to fix this so that the empty results of jsonpath evaluation use
> > NULL ON EMPTY by default, ie, when the ON EMPTY clause is not present.
> > Attached a patch to do so.
> >
>
> Tested: works.
>
> Thanks :)
>
> -markus
>
>