Re: ON ERROR in json_query and the like

> On 12.06.2024, at 15:31, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
> On Tuesday, May 28, 2024, Markus Winand <markus(dot)winand(at)winand(dot)at> 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.
>
> The docs here don’t seem to cover the on empty clause at all nor fully cover all options.
>
> Where do you find the claim that the one implies the other? Is it a typo that your examples says “implies null on empty” but the subject line says “implies error on empty”?

I see the confusion caused — sorry. The headline was meant to describe the observed behaviour in 17beta1, while the content refers to how the standard defines it.

> Without those clauses a result is either empty or an error - they are mutually exclusive (ignoring matches). I would not expect one clause to imply or affect the behavior of the other. There is no chaining. The original result is transformed to the new result specified by the clause.

Agreed, that’s why I found the 17beta1 behaviour surprising.

> I’d need to figure out whether the example you show is actually producing empty or error; but it seems correct if the result is empty.

As I understand the standard, an empty result is not an error.

> The first query ignores the error clause - the empty array row seems to be the representation of empty here; the second one matches the empty clause and outputs null instead of the empty array.

But the first should behave the same, as the standard implies NULL ON EMPTY if there is no explicit ON EMPTY clause. Oracle DB behaving differently here makes me wonder if there is something in the standard that I haven’t noticed yet...

-markus