BUG #15085: Domain "not null" constraint doesn't detect a nullreturned from a resultset - Mailing list pgsql-bugs

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;