Re: [BUGS] BUG #6669: unique index w/ multiple columns and NULLs

[jo]

Hi Tom,

Thanks for the explanation about standard sql.
The goodness of it must be accepted by faith. :-)
I still have a doubt about the result of the GROUP BY clause.
It seems to me that there's an inconsistence between the GROUP BY clause 
and the unique index.

The GROUP BY clause, consider NULLs as known and equal values
while the index unique constraint consider NULLs as unknown values and 
not equals between them.

Don't you think, there's an inconsistence here?

j


Tom Lane wrote:

jose.soa...@sferacarta.com writes:
  

I think I have found an error in pg or at least inconsistency, take a look
at this.
I created an unique index on two columns and pg let me enter repeated values
as NULLs (unknown value),



This is entirely correct per SQL standard: unique constraints do not
reject duplicated rows that include nulls.  If you read the standard,
unique constraints are defined in terms of UNIQUE predicates, and a
UNIQUE predicate for a table T is defined thus:

 2) If there are no two rows in T such that the value of each column
in one row is non-null and is equal to the value of the cor-
responding column in the other row according to Subclause 8.2,
"", then the result of the  is true; otherwise, the result of the 
is false.

(SQL92 section 8.9 )

This is why a primary key constraint is defined as requiring both UNIQUE
and NOT NULL; you need that to ensure that there are indeed no two
indistinguishable rows.

(Mind you, I'm not here to defend *why* the standard is written that
way.  But that is what it says.)

  

Oracle don't allows to insert two NULLs in such column.



Oracle is not exactly the most standards-compliant implementation
around.  They are well-known to be particularly wrong with respect to
NULLs behavior.

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 #6669: unique index w/ multiple columns and NULLs

[jo]

Jeff Davis wrote:

On Mon, 2012-06-04 at 11:56 -0400, Bruce Momjian wrote:
  

I get your point about COUNT(*) really counting rows, not values, but
why doesn't GROUP BY then skip nulls?



A while ago, I came to the conclusion that applying logic to extrapolate
the behavior of NULL is a bad idea:

http://thoughts.davisjeff.com/2009/08/02/what-is-the-deal-with-nulls/

Jose was not wrong about the inconsistency between UNIQUE and GROUP BY.
But the answer is that "we do it that way because the standard says so".
And that's a good reason.

Regards,
Jeff Davis


  

The article pointed by Jeff is very insightful.

NULLs, if you know them, avoid them :-(

I agree with Date and Darwen about NULLs:

"Chris Date and  Hugh Darwen the authors of The Third Manifesto, have 
suggested that the SQL Null implementation is inherently flawed and 
should be ELIMINATED altogether^ 
 
, pointing to inconsistencies and flaws in the implementation of SQL 
Null-handling (particularly in aggregate functions) as proof that the 
entire concept of Null is flawed and should be removed from the 
relational model"


j



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