Обсуждение: BUG #15085: Domain "not null" constraint doesn't detect a nullreturned from a resultset

The following bug has been logged on the website:

Bug reference:      15085
Logged by:          Daniel Einspanjer
Email address:      deinspanjer@gmail.com
PostgreSQL version: 9.6.7
Operating system:   Linux
Description:

I was trying to create two domains, one that allowed nulls, and another that
built on it that didn't allow nulls.  After some testing, I came across this
unusual behavior that I believe might be a bug.
I did check the TODO and tried to do some web searches (hard keywords to
work with here) but I didn't find any reports about this.

Please find below a simple test case.

create domain test_domain text not null;
create temporary table
test_domain_constraint_vs_column_constraint(val_to_return test_domain,
val_to_find test_domain not null);
insert into test_domain_constraint_vs_column_constraint values
('good','good');
select * from test_domain_constraint_vs_column_constraint;
-- the domain constraint doesn't allow this insert.
insert into test_domain_constraint_vs_column_constraint values
(null,'bad');
-- the table constraint doesn't allow this insert.
insert into test_domain_constraint_vs_column_constraint values
('bad',null);

create function test_domain_constraint_in_return(_in test_domain) returns
test_domain language sql strict as $$
select val_to_return from test_domain_constraint_vs_column_constraint where
val_to_find = _in;
$$;

-- happy case
select test_domain_constraint_in_return('good') as val,
pg_typeof(test_domain_constraint_in_return('good')) as typ;

-- sad case
select test_domain_constraint_in_return('ugly') as val,
pg_typeof(test_domain_constraint_in_return('ugly')) as typ;

-- if we try to insert into the val_to_find column, the column constraint
prevents it
insert into test_domain_constraint_vs_column_constraint values
('ugly',test_domain_constraint_in_return('ugly'));

-- but if we insert into the val_to_return column which only has the domain
constraint to protect it, we succeed.
insert into test_domain_constraint_vs_column_constraint values
(test_domain_constraint_in_return('ugly'),'ugly');
select * from test_domain_constraint_vs_column_constraint where
val_to_return is null;

-- cleanup
drop domain test_domain cascade ;
drop table test_domain_constraint_vs_column_constraint;

The following bug has been logged on the website:

Bug reference:      15085
Logged by:          Daniel Einspanjer
Email address:      deinspanjer@gmail.com
PostgreSQL version: 9.6.7
Operating system:   Linux
Description:

I was trying to create two domains, one that allowed nulls, and another that
built on it that didn't allow nulls.  After some testing, I came across this
unusual behavior that I believe might be a bug.
I did check the TODO and tried to do some web searches (hard keywords to
work with here) but I didn't find any reports about this.

"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

Daniel Einspanjer <deinspanjer@gmail.com> writes:
> Tom, please forgive me if this is a non-starter for a suggestion, but what
> about forcing a not null constraint on a column that is declared to be of
> the type of a domain that has a not null constraint?

I don't particularly want to go there.  What happens when you add or drop
the not null constraint on an existing domain type?

In any case, this would amount to positive reinforcement for people who
use domain not null constraints, which IMO is bad practice to start with.
Not everything in the SQL standard is a good idea.

            regards, tom lane