Re: ON ERROR in json_query and the like

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.

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