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>

Hi,

On Thu, Oct 29, 2026 at 12:11 PM jian he <jian(dot)universality(at)gmail(dot)com> wrote:>
> 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.

I like Jian’s approach. IIUC, it fixes both issues at once by coercing
to the domain only after the COALESCE, that is, on the final result,
rather than on each of its inputs.

I’d like to get Richard’s thoughts on it, and I’m also still working
through it myself, so I don’t expect to be able to commit this before
Saturday’s release wrap.

--
Thanks, Amit Langote