Re: ON ERROR in json_query and the like

ú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.

Regards

Pavel

> 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.
>
> -markus
>
>
>
>