Re: Outer joins and NULLs (old subject "ERROR: failed to find conversion function from key_vals_nn to record[]")

On Sun, Jun 19, 2022 at 2:31 PM Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:

> It would be foolish, therefore, to define the target table for
> "insert-select" using "CTAS where false".
>

SQL is a strongly typed language where the structure of the query output is
determined without any consideration of whether said query returns zero,
one, or many rows. Because of this property it is entirely consistent that
a CTAS produces a table even if the query execution produces zero rows.

That CTAS chooses to not try and produce constraints on the newly created
table by inferring them from the underlying query seems like a reasonable
trade-off between functionality and effort. It simply stops at "data
types" and doesn't care for how any given one is implemented. That domains
have constraints is incidental to the entire design.

Allowing domains to be defined as not null at this point is simply
something that we (IMO, the documentation is not this strongly worded)
don't support but don't error out upon in the interest of backward
compatibility. It, as you note, has some corner-case bugs. You can avoid
those bugs by simply not using a non-null constraint as suggested.

As for the "same source" optimization: the documentation reads - "For
example, this can happen in an outer-join query,...", the bug-fix here is
to simply add this situation as a second example. However, it is
reasonably considered correct that a record you just read from a table
should be able to be written back to said table unchanged. The "error" is
that we allowed the record to exist in the first place, but we absolved
ourselves of responsibility with the caveats on the CREATE DOMAIN page.
Subsequent consequences of that mis-use are likewise attributed to said
mis-use and are on the user's head for allowing their code to produce the
problematic behavior.

At most we should probably go from saying "Best practice therefore..." to
"We no longer support setting a not null constraint on a domain but will
not error in the interest of not breaking existing uses that are careful to
avoid the problematic corner-cases".

David J.