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 Thu, Nov 5, 2026 at 10:15 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> 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.

Here's an updated version of Jian's patch with comment cleanup and
other mostly cosmetic fixes.

Like 8d829f5a0, which added the COALESCE and the ‘[]’ constant, this
is not meant for branches before 19. There, a domain on an empty set
only fails because JSON_ARRAY(query) returns NULL for it, which is bug
#19418, whose fix (8d829f5a0) we decided not to back-patch because of
the catversion bump it required.

No catversion bump is needed for this patch though, as no node
definitions change, so existing stored trees can still be read. Views
created before the fix keep the old behavior until re-created:

On master:

CREATE DOMAIN arr AS jsonb;
CREATE VIEW v_master AS SELECT JSON_ARRAY(SELECT 1 WHERE false
RETURNING arr) AS a;
select * from v_master ;
a
----
[]
(1 row)

ALTER DOMAIN arr ADD CONSTRAINT nonempty CHECK (jsonb_array_length(VALUE) > 0);

-- wrong
select * from v_master ;
a
----
[]
(1 row)

Patched (same initdb'd cluster as master):

-- old view behaves the same as master
select * from v_master ;
a
----
[]
(1 row)

-- ok after view recreated
CREATE OR REPLACE VIEW v_master AS SELECT JSON_ARRAY(SELECT 1 WHERE
false RETURNING arr) AS a;
select * from v_master ;
ERROR: value for domain arr violates check constraint "nonempty"

This is moot for an unreleased 19 anyway. The commit message says as much.

--
Thanks, Amit Langote