On Mon, Jun 17, 2024 at 10:07 PM Markus Winand <markus.win...@winand.at> wrote:
> > On 17.06.2024, at 08:20, Amit Langote <amitlangot...@gmail.com> wrote:
> >>> 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.
Pushed, thanks for testing.
I'll work on the documentation updates that may be needed based on
this and nearby discussion(s).
--
Thanks, Amit Langote
