Re: BUG #15085: Domain "not null" constraint doesn't detect a null returned from a resultset

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Saturday, February 24, 2018, PG Bug reporting form <
> noreply@postgresql.org> wrote:
>> After some testing, I came across this
>> unusual behavior that I believe might be a bug.

> This appears to fall within the exceptions for not null noted in the
> documentation for create domain.
> https://www.postgresql.org/docs/9.6/static/sql-createdomain.html

Yeah, the core issue here is that at no point does the query attempt to
convert a value of some other type to the domain type.  Rather, the
appearance of the null result falls out of the semantics of a scalar
subquery that returns no rows.  Like the case for a "not null" domain
column on the right side of a left join, this is a basic inconsistency
in the SQL language, and it's not clear that anyone would thank us for
throwing errors.  (But they *would* complain if we slowed down these
fundamental operations to check for such cases :-(.)

I've occasionally wondered if we should force domain types that appear in
these contexts to decay to their base type, so as to avoid the situation
of returning a value that claims to be of a domain type but is actually
null.  (That is, the reported output column type in an outer join or
whatever would be the base type not the domain type.)  I'm doubtful
that any but pedants would find that to be an improvement, though; and
I've not come across anything in the SQL standard that suggests an
implementation should do that.

            regards, tom lane