Обсуждение: BUG #5477: CREATE DOMAIN NOT NULL constraints not always enforced for INSERT with subquery
BUG #5477: CREATE DOMAIN NOT NULL constraints not always enforced for INSERT with subquery
От
"Matt Nourse"
Дата:
The following bug has been logged online: Bug reference: 5477 Logged by: Matt Nourse Email address: matthew@nplus1.com.au PostgreSQL version: 8.4 Operating system: Linux (Debian and Red Hat) Description: CREATE DOMAIN NOT NULL constraints not always enforced for INSERT with subquery Details: Set up: 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')); When I use simpler subqueries (eg SELECT 1 WHERE 1 = 0), the NOT NULL constraint is enforced. If I remove the test_id_domain domain and replace its use with INT NOT NULL, the constraint is enforced. Thanks and regards, Matt
"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 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>
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