BUG #5477: CREATE DOMAIN NOT NULL constraints not always enforced for INSERT with subquery

"Matt Nourse" <matthew@nplus1.com.au> writes:

CREATE DOMAIN test_id_domain INT NOT NULL;
CREATE TABLE test_state(id test_id_domain PRIMARY KEY, display_value
varchar(20) NOT NULL);
CREATE TABLE test_city(state_id test_id_domain REFERENCES test_state(id));

This produces an error as expected:

INSERT INTO test_city(state_id) VALUES (NULL);

This successfully inserts a NULL value into the state_id field:

INSERT INTO test_city(state_id) VALUES ((SELECT id FROM test_state WHERE
display_value = 'Nonexistent state'));

There are any number of ways you can get a similar result, for example
a LEFT JOIN. To my mind, this demonstrates why not-null constraints
associated with datatypes are a fundamentally flawed concept. If the
SELECT or LEFT JOIN can produce a null value, as it clearly can, then
it's nonsensical to think that the output column should be considered
to be of a NOT NULL domain type. But what else should it be? If we
smash domains to their base types when assigning result types of
queries, that will make many people unhappy.

Moral: NOT NULL constraints at the domain level suck. Don't use 'em.

regards, tom lane

Tom Lane wrote:

"Matt Nourse" <matthew@nplus1.com.au> writes:

CREATE DOMAIN test_id_domain INT NOT NULL;
CREATE TABLE test_state(id test_id_domain PRIMARY KEY, display_value
varchar(20) NOT NULL);
CREATE TABLE test_city(state_id test_id_domain REFERENCES test_state(id));

This produces an error as expected:

INSERT INTO test_city(state_id) VALUES (NULL);

This successfully inserts a NULL value into the state_id field:

INSERT INTO test_city(state_id) VALUES ((SELECT id FROM test_state WHERE
display_value = 'Nonexistent state'));

There are any number of ways you can get a similar result, for example
a LEFT JOIN. To my mind, this demonstrates why not-null constraints
associated with datatypes are a fundamentally flawed concept. If the
SELECT or LEFT JOIN can produce a null value, as it clearly can, then
it's nonsensical to think that the output column should be considered
to be of a NOT NULL domain type. But what else should it be? If we
smash domains to their base types when assigning result types of
queries, that will make many people unhappy.

Moral: NOT NULL constraints at the domain level suck. Don't use 'em.

I have written up the following documentation patch to document this
behavior. It doesn't seem like something we want to fix, so I am not
making it a TODO item.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Tom Lane wrote:

< NOT NULL constraints at the domain level suck. Don't use 'em.

+1

As someone who uses domains very heavily, I can attest that the
semantics of that are very weak. Whether a domain is nullable
depends almost entirely on the context of its use, which you can't
(and shouldn't try to) anticipate on its declaration.

-Kevin

Applied.

---------------------------------------------------------------------------

Bruce Momjian wrote:

Tom Lane wrote:

"Matt Nourse" <matthew@nplus1.com.au> writes:

CREATE DOMAIN test_id_domain INT NOT NULL;
CREATE TABLE test_state(id test_id_domain PRIMARY KEY, display_value
varchar(20) NOT NULL);
CREATE TABLE test_city(state_id test_id_domain REFERENCES test_state(id));

This produces an error as expected:

INSERT INTO test_city(state_id) VALUES (NULL);

This successfully inserts a NULL value into the state_id field:

INSERT INTO test_city(state_id) VALUES ((SELECT id FROM test_state WHERE
display_value = 'Nonexistent state'));

There are any number of ways you can get a similar result, for example
a LEFT JOIN. To my mind, this demonstrates why not-null constraints
associated with datatypes are a fundamentally flawed concept. If the
SELECT or LEFT JOIN can produce a null value, as it clearly can, then
it's nonsensical to think that the output column should be considered
to be of a NOT NULL domain type. But what else should it be? If we
smash domains to their base types when assigning result types of
queries, that will make many people unhappy.

Moral: NOT NULL constraints at the domain level suck. Don't use 'em.

I have written up the following documentation patch to document this
behavior. It doesn't seem like something we want to fix, so I am not
making it a TODO item.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

[ text/x-diff is unsupported, treating like TEXT/PLAIN ]

Index: doc/src/sgml/ref/create_domain.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/create_domain.sgml,v
retrieving revision 1.34
diff -c -c -r1.34 create_domain.sgml
*** doc/src/sgml/ref/create_domain.sgml	3 Apr 2010 07:22:58 -0000	1.34
--- doc/src/sgml/ref/create_domain.sgml	28 May 2010 17:19:35 -0000
***************
*** 121,127 ****
<term><literal>NOT NULL</></term>
<listitem>
<para>
!         Values of this domain are not allowed to be null.
</para>
</listitem>
</varlistentry>
--- 121,132 ----
<term><literal>NOT NULL</></term>
<listitem>
<para>
!         Values of this domain are normally prevented from being null.
!         It is still possible for a domain with this constraint
!         to take a null value if it is assigned a matching domain type
!         that has become null, e.g. via a LEFT OUTER JOIN, or
!         <command>INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM
!         tab WHERE false))</command>.
</para>
</listitem>
</varlistentry>

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Kevin Grittner wrote:

Tom Lane wrote:

< NOT NULL constraints at the domain level suck. Don't use 'em.

+1

As someone who uses domains very heavily, I can attest that the
semantics of that are very weak. Whether a domain is nullable
depends almost entirely on the context of its use, which you can't
(and shouldn't try to) anticipate on its declaration.

Fair enough, thanks Tom and Kevin for the insights and thanks Bruce for
applying the doco patch, that makes things much clearer.

As NOT NULL on domains doesn't always prevent a value from becoming NULL
(and because it "sucks" :) ) would you consider deprecating the
not-null-on-domains feature and then removing it from some future
version of PostgreSQL?

Thanks again,
Matt

Matthew Nourse <matthew@nplus1.com.au> writes:

As NOT NULL on domains doesn't always prevent a value from becoming NULL
(and because it "sucks" :) ) would you consider deprecating the
not-null-on-domains feature and then removing it from some future
version of PostgreSQL?

We can't really because it's required by SQL standard. Perhaps at some
point the standards committee will think it through a little better and
publish something that resolves the contradictions. For the moment the
bottom line is that it's got debatable behavior and is best avoided.

regards, tom lane

Tom Lane wrote:

Matthew Nourse <matthew@nplus1.com.au> writes:

As NOT NULL on domains doesn't always prevent a value from becoming NULL
(and because it "sucks" :) ) would you consider deprecating the
not-null-on-domains feature and then removing it from some future
version of PostgreSQL?

We can't really because it's required by SQL standard. Perhaps at some
point the standards committee will think it through a little better and
publish something that resolves the contradictions. For the moment the
bottom line is that it's got debatable behavior and is best avoided.

Ah, I see. Thanks again!

Matt