Re: SQL/JSON DEFAULT ON ERROR/ON EMPTY evaluation fail should rethrow error unconditionally

Hi Jian,

On Mon, Oct 26, 2026 at 9:12 PM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
>
> On Wed, Sep 16, 2026 at 11:29 AM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> >
> > 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.
> >
>
> Yes.
>
> I think
> SELECT JSON_VALUE('"a"', '$' RETURNING int DEFAULT (('a' || (random()
> * 0)::int)::int + 0) ON ERROR);
>
> SELECT JSON_VALUE('"a"', '$' RETURNING int DEFAULT (('a' || (random()
> * 0)::int)::int ) ON ERROR);
>
> They should behave the same; the resulting values might differ
> (assuming no overflow).
>
> While working on the CAST DEFAULT patch
> (https://commitfest.postgresql.org/patch/5941/) I found another bug related to
> this thread: after a soft error has been reported, we didn't return earlier.
> and let the code continues. Since it mostly affects SQL/JSON, I'm posting it
> here too.
>
> CREATE DOMAIN dchk AS int CHECK ((1 / coalesce(VALUE, 0)) IS NOT NULL);
> SELECT JSON_VALUE(jsonb '"x"', '$' RETURNING dchk DEFAULT 11 ON ERROR);
> SELECT JSON_VALUE(jsonb '"x"', '$' RETURNING int DEFAULT 11 ON ERROR);
>
> I belive the above should return the same result 11. Currentlty the
> first one failed with
> ERROR: division by zero
>
> SELECT JSON_QUERY(jsonb '["x"]', '$' RETURNING dchk[] DEFAULT '{11}' ON ERROR);
> It should return {11} instead of throwing an error.
>
> So in many places, once SOFT_ERROR_OCCURRED is true, we should return
> early or handle the error specially, rather than carry on.
> Please review the attached patch commit message, which I believe
> explains it clearly (i hope).

Thanks for reminding me of this thread. I agree about the second bug
and your proposed fix.

I’ve looked at the patch and it seems to be fixing the following two bugs:

1. Lack of a COERCION_FINISH step after ON ERROR/EMPTY expression
evaluation in some cases meant the soft error saved during that
evaluation was never rethrown and the query silently returned NULL.

2. Once a soft error has occurred, the dummy NULL it leaves behind
should not be processed any further, but in a few places
(CoerceToDomain, populate_domain(), populate_record(),
ExecEvalJsonCoercion() called on the DEFAULT result) it currently is.

I decided to break it down into attached two patches along those lines.

--
Thanks, Amit Langote