Re: SQL/JSON DEFAULT ON ERROR/ON EMPTY evaluation fail should rethrow error unconditionally - Mailing list pgsql-hackers

On Wed, Sep 16, 2026 at 12:01 PM jian he <jian.universality@gmail.com> wrote:
>
> On Wed, Sep 16, 2026 at 10:48 AM Amit Langote <amitlangote09@gmail.com> wrote:
> >
> > Hi Jian,
> >
> >
> > I haven't looked at the patch, but could you specify whether your
> > patch fixes a live bug or is an improvement you found while working on
> > the linked thread?  I don't understand which is the case because your
> > report doesn't have an example.
> >
>
> Apologies for the poor communication.
>
> In src/test/regress/expected/sqljson_queryfuncs.out, we have lots of
> ERROR:  could not coerce ON ERROR expression (DEFAULT) to the RETURNING type
>
> The comments in ExecInitJsonExpr also says that, for DEFAULT expr ON ERROR and
> DEFAULT expr ON EMPTY, we evaluate the DEFAULT expression in an error-soft way
> *but* rethrow the error, I assume the purpose is for better error message.
>
> However, in the master branch, there are some cases where we do not
> rethrow the soft error.
> For example, I believe the query below should return an ERROR.
> SELECT JSON_VALUE('"a"', '$' RETURNING int DEFAULT (('a' || (random()
> * 0)::int)::int + 0) ON ERROR);

Thanks for providing the example.  While playing around with that, I
noticed that you do get error when parentheses are moved:

postgres=# SELECT JSON_VALUE('"a"', '$' RETURNING int DEFAULT ('a' ||
((random()* 0)::int)::int + 0) ON ERROR);
ERROR:  could not coerce ON ERROR expression (DEFAULT) to the RETURNING type
DETAIL:  invalid input syntax for type integer: "a0"

I haven't checked but I suppose your patch ensures the query you
shared produces the error like this.

--
Thanks, Amit Langote