Thread: 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

From

"Matt Nourse"

Date:

28 May 2010, 13:55:22

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

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

From

Tom Lane

Date:

28 May 2010, 14:48:14

"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

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

From

Bruce Momjian

Date:

28 May 2010, 17:20:50

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>

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

From

Bruce Momjian

Date:

29 May 2010, 19:06:42

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