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

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).

--
jian
https://www.enterprisedb.com/