Re: ERROR: failed to find conversion function from key_vals_nn to record[]

On Wed, Jun 15, 2022 at 11:07 PM Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:

> This is what the doc promises. But how can you see it as anything but a
> bug? The subquery evaluates to "null", and only then is the attempt made to
> create a new row which self-evidently violates the domain's constraint. How
> is it any different from this:
>

Because NULL is doing double-duty. The absence of any possible value is
represented by null, as is knowing that a value exists but not knowing what
that value is. The most obvious example of this problem is:
(not tested, may have typos)

SELECT a.vala, b.valb, b.lbl
FROM (values (1)) AS a (vala)
LEFT JOIN (values (2, null)) AS b (valb, lbl) ON vala = valb;

vs.

SELECT a.vala, b.valb, b.lbl
FROM (values (1)) AS a (vala)
LEFT JOIN (values (1, null)) AS b (valb, lbl) ON vala = valb;

The resultant value of "b.lbl" is null in both queries, but not for the
same reason (left join missing semantics pre-empt null type value
semantics).

So, yes, queries can produce NULL even for domains that are defined not
null. If you want protection from null values in your database tables you
must define your columns to not accept nulls. It becomes a habit and can
be readily checked for in the catalogs (I suggest adding a column comment
for why columns defined as null are allowed to be null, then you can query
the column contents to exclude those columns where null was intended - or
go with your idea and just report every column as non-conforming. COMMENT
ON table.column IS '@NULLABLE - optional information the customer might not
provide').

David J.