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

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: deinspanjer(at)gmail(dot)com
Subject: BUG #15085: Domain "not null" constraint doesn't detect a null returned from a resultset
Date: 2018-02-25 01:08:17
Message-ID: 151952089752.1463.4091997216675283064@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15085
Logged by: Daniel Einspanjer
Email address: deinspanjer(at)gmail(dot)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;

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message David G. Johnston 2018-02-25 02:11:40 Re: BUG #15085: Domain "not null" constraint doesn't detect a null returned from a resultset
Previous Message Magnus Hagander 2018-02-24 22:09:41 Re: BUG #15076: postmaster crashes unexpectedly when using up arrow key in psql command