Re: [***DKIM violation***] Re: BUG #19418: SQL/JSON JSON_VALUE() does not conform to ISO/IEC 9075-2:2023(E) 6.34 <JSON value constructor>

On Fri, Oct 16, 2026 at 8:39 PM Staroverov Ilja <i(dot)staroverov(at)ftdata(dot)ru> wrote:
>
> Hi,
>
> There's another problem with the empty-array fallback of
> JSON_ARRAY(query) from 8d829f5a0, this time with domains in the
> RETURNING clause. Reproduced on master (aa7b9cf13); REL_19_STABLE has
> the same code:
>
> CREATE DOMAIN nonempty_arr AS jsonb
> CHECK (jsonb_array_length(VALUE) > 0);
> SELECT JSON_ARRAY(SELECT 1 RETURNING nonempty_arr);
> ERROR: value for domain nonempty_arr violates check constraint "nonempty_arr_check"
>

>
> One related case is not fixed by this:
>
> CREATE DOMAIN nn AS jsonb NOT NULL;
> SELECT JSON_ARRAY(SELECT 1 WHERE false RETURNING nn);
> ERROR: domain nn does not allow null values
>
> although the result should be []. The same happens with any domain
> whose constraints reject NULL, such as CHECK (VALUE IS NOT NULL). Here
> the RETURNING coercion is applied to the NULL result of the inner
> JSON_ARRAYAGG, before the COALESCE, so this is independent of the
> patch. Fixing that too would mean computing the array in json or
> jsonb and applying the RETURNING coercion once, after the COALESCE,

> which is roughly what Ayush suggested upthread. That is a larger
> change, so I kept the patch minimal, but I can rework it that way if
> that's preferred.
>

Hi.
These two issues are related.

In transformJsonArrayQueryConstructor, if we change
from
COALESCE((SELECT JSON_ARRAYAGG(a) FROM (subquery) q(a)), '[]')
to
(COALESCE((SELECT JSON_ARRAYAGG(a) FROM (subquery) q(a)), '[]'))::returning_type

Then your patch may not be needed, because the outer CoerceToDomain
already checks the final COALESCE result, whichever branch it comes
from.

Anyway, another draft/demo patch attached.
Obviously, some of the comments need to be adjusted.

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