Thread: BUG #15085: Domain "not null" constraint doesn't detect a nullreturned from a resultset
BUG #15085: Domain "not null" constraint doesn't detect a nullreturned from a resultset
From
PG Bug reporting form
Date:
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;
Re: BUG #15085: Domain "not null" constraint doesn't detect a nullreturned from a resultset
From
"David G. Johnston"
Date:
On Saturday, February 24, 2018, PG Bug reporting form <noreply@postgresql.org> wrote:
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.
This appears to fall within the exceptions for not null noted in the documentation for create domain.
David J.
Re: BUG #15085: Domain "not null" constraint doesn't detect a null returned from a resultset
From
Tom Lane
Date:
"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
Re: BUG #15085: Domain "not null" constraint doesn't detect a nullreturned from a resultset
From
Daniel Einspanjer
Date:
Oops. I am very sorry for missing that note in the docs. I agree it does cover the case I've described quite well.
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?
Just to be clear, I'm thinking about something like the following:
postgres=>CREATE DOMAIN example TEXT NOT NULL;
CREATE DOMAIN
postgres=>CREATE TABLE usage(myexample EXAMPLE NULL);
ERROR: conflicting NULL/NOT NULL declarations for domain and column "myexample" of table "usage"
-- I imagine we could also get fancier if it was helpful and do something like this:
postgres=>CREATE TABLE usage2(myexample EXAMPLE);
NOTICE: NOT NULL constraint from domain added to column "myexample" of table "usage"
That automatic constraint idea came from my understanding of how PRIMARY KEY automatically marks the column as NOT NULL. (Oddly, pg allows you to use both the "NULL" and "PRIMARY KEY" keywords in a column declaration, it just ignores the NULL.)
This doesn't do anything to fix the underlying issue of being able to generate a value that violates the domain constraint, but at least it would prevent the inconsistency of having a table that doesn't allow the insert of some rows but allows others, and it would prevent users from inadvertently setting themselves up to allow this.
-Daniel
On Sat, Feb 24, 2018 at 10:35 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
"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
Re: BUG #15085: Domain "not null" constraint doesn't detect a null returned from a resultset
From
Tom Lane
Date:
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