[BUGS] BUG #8141: multi-column check expression evaluating to NULL

2013-05-08 Thread andras . vaczi 
The following bug has been logged on the website:

Bug reference:  8141
Logged by:  Andras Vaczi
Email address:  andras.va...@zalando.de
PostgreSQL version: 9.1.9
Operating system:   linux/Ubuntu 12.10
Description:

Consider the following table with a CHECK constraint:

CREATE TABLE check_test
(
  id integer NOT NULL,
  col integer,
  CONSTRAINT unique_with_null_check1 CHECK (col = 1 AND id  20)
);

This INSERT statement succeeds:

INSERT INTO check_test (id, col) VALUES (1, NULL);

While, col being NULL, the whole CHECK condition evaluates to NULL - this is
covered in the documentation.

But this is refused:
INSERT INTO check_test (id, col) VALUES (21, NULL);

ERROR:  new row for relation check_test violates check constraint
unique_with_null_check1

I think this behaviour should be either also mentioned in the docs or
cosidered a bug.



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

Re: [BUGS] BUG #8141: multi-column check expression evaluating to NULL

2013-05-08 Thread Tom Lane 
andras.va...@zalando.de writes:
 Consider the following table with a CHECK constraint:

 CREATE TABLE check_test
 (
   id integer NOT NULL,
   col integer,
   CONSTRAINT unique_with_null_check1 CHECK (col = 1 AND id  20)
 );

 This INSERT statement succeeds:

 INSERT INTO check_test (id, col) VALUES (1, NULL);

 While, col being NULL, the whole CHECK condition evaluates to NULL - this is
 covered in the documentation.

 But this is refused:
 INSERT INTO check_test (id, col) VALUES (21, NULL);

 ERROR:  new row for relation check_test violates check constraint
 unique_with_null_check1

 I think this behaviour should be either also mentioned in the docs or
 cosidered a bug.

I see no bug here.  In the first case, the col = 1 condition yields
NULL while id  20 yields TRUE, so you have NULL AND TRUE which is
NULL, which is considered a pass for a CHECK condition per spec.
In the second case, col = 1 is still NULL, but id  20 is FALSE,
so you have NULL AND FALSE which is FALSE (*not* NULL), and so failure
is per spec.

Yes, the behavior of AND/OR with NULLs is documented.
http://www.postgresql.org/docs/9.1/static/functions-logical.html

regards, tom lane


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

Re: [BUGS] BUG #8141: multi-column check expression evaluating to NULL

2013-05-08 Thread Gavin Flower 

On 09/05/13 01:07, andras.va...@zalando.de wrote:

The following bug has been logged on the website:

Bug reference:  8141
Logged by:  Andras Vaczi
Email address:  andras.va...@zalando.de
PostgreSQL version: 9.1.9
Operating system:   linux/Ubuntu 12.10
Description:

Consider the following table with a CHECK constraint:

CREATE TABLE check_test
(
   id integer NOT NULL,
   col integer,
   CONSTRAINT unique_with_null_check1 CHECK (col = 1 AND id  20)
);


[...]

if 'id' is the primary key, then the table should be defined as:

CREATE TABLE check_test
(
  id  integer PRIMARY KEY,
  col integer NOT NULL,
  CONSTRAINT unique_with_null_check1 CHECK (col = 1 AND id  20)
);

Because:

1.   'PRIMARY KEY ' implies 'NOT NULL' and also 'UNIQUE', and so an
   index is created for 'id'
   (index is required to enforce uniqueness)

2. you are testing that 'col' has a certain type of numeric value, so
   this logically implies that it is 'NON NULL', but you actually have
   to tell progress explicitly tat you want it to be 'NON NULL'



Cheers,
Gavin