Re: NULLs in unique indexes; Was: Oracle purchases Sleepycat - is this the "other shoe" for MySQL AB?

Alban Hertroys <alban(at)magproductions(dot)nl> writes:
> But according to:
> http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/21064
> "The definition of unique constraints in the SQL standards specifies
> that the column definition shall not allow null values.", although that
> doesn't literally mean NULL values in unique indexes are not allowed...

Sybase is wrong here, or at least pretty misleading. SQL92 does allow
minimal SQL implementations to impose such a restriction:

2) The following restrictions apply for Entry SQL in addition to
any Intermediate SQL restrictions:

a) If PRIMARY KEY or UNIQUE is specified, then the <column defi-
nition> for each column whose <column name> is in the <unique
column list> shall specify NOT NULL.

But if you don't enforce that, the spec clearly requires you to accept
rows that are duplicate but contain nulls. 11.7 <unique constraint
definition> sayeth:

3) Case:

a) If the <unique specification> specifies PRIMARY KEY, then let
SC be the <search condition>:

UNIQUE ( SELECT UCL FROM TN )
AND
( UCL ) IS NOT NULL

b) Otherwise, let SC be the <search condition>:

UNIQUE ( SELECT UCL FROM TN )

[ UCL = unique column list, TN = table name --- tgl ]

...

2) The unique constraint is not satisfied if and only if

EXISTS ( SELECT * FROM TN WHERE NOT ( SC ) )

is true.

and the UNIQUE predicate (a thing we don't currently implement btw)
is defined in 8.9:

<unique predicate> ::= UNIQUE <table subquery>

1) Let T be the result of the <table subquery>.

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,
"<comparison predicate>", then the result of the <unique predi-
cate> is true; otherwise, the result of the <unique predicate>
is false.

It says "each column" has to be non-null --- so a row containing any
nulls is simply not able to cause a violation of a UNIQUE constraint.

Your other quotes show that a number of implementations get this wrong :-(.
Date and Darwen read it the same way we do, though (see pages 248 and
254 in A Guide to the SQL Standard, 4th edition), so I have confidence
that our reading is correct.

regards, tom lane