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,

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"

The subquery returns a row and the result [1] satisfies the domain;
JSON_ARRAYAGG(... RETURNING nonempty_arr) over the same row works. And
in the other direction:

CREATE DOMAIN arr AS jsonb;
CREATE VIEW v AS SELECT JSON_ARRAY(SELECT 1 WHERE false RETURNING arr) AS a;
ALTER DOMAIN arr ADD CONSTRAINT nonempty
CHECK (jsonb_array_length(VALUE) > 0);
SELECT * FROM v;
a
----
[]

The view returns a value that violates the domain's constraint. The
same happens with a prepared statement.

The cause is that the fallback constant is built during parse analysis
by calling the input function of the RETURNING type, which for a domain
is domain_in(). So the constraints are checked right away, whether or
not the fallback will be used, and the stored constant is not checked
again later.

The attached patch builds the fallback as an untyped literal and
coerces it to the RETURNING type with coerce_to_target_type(), the
same way as any other untyped literal. For a domain, coerce_type()
then calls the base type's input function and adds a CoerceToDomain
node, so the constraints are checked at run time, and only if the
fallback is used. The typmod case from 9d124a14b is still covered: the
length check is now done by a length coercion step instead of the input
function, so it happens at plan time rather than during parse analysis,
with the same error. The existing tests pass unchanged, and the patch
adds tests for the two cases above, which fail without it.

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.

Since 8d829f5a0 is new in v19, perhaps this should be a v19 open item.

--
Ilya Staroverov