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;
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 |