Re: So what's null then if it's not nothing?

In article <1133191499.952147.182080_at_z14g2000cwz.googlegroups.com>, boston103_at_hotmail.com says...
>
> Re. projection. The projection definition uses the notion of set
> membership so that the issue of duplicates and therefore their
> elimination via comparison does not arise at the logical level either
> with respect to nulls or non-nulls.

Sophistry. A SELECT DISTINCT (it is really too kind to call this SQL construct "projection") eliminates duplicate NULLs, just like it eliminates duplicate values. To handwave this by saying "well, they're not *distinct*, but that doesn't mean they're *equal*" is just a bad excuse.

I suppose sorting is defined in a similar manner? "NULLs are collected at the top in an ascending sort, and at the bottom in a descending sort, but we don't actually *compare* them to anything. Oh no."

> The unique constraint behaviour depends on how you define it.

Ditto.

> The
> SQL'92 standard says that the column value is a duplicate if it's
> already present in the set of existing column non-nulls

I'm not able to parse this.

> (the SQL'92
> entry level does not allow unique constraints on nullable columns at
> all). So for NULL, the DUPLICATE predicate would return UNKNOWN (or
> NULL) which is then mapped to false

Indeed? AFAIK, SQL constraints are considered satisfied if they are true or if they are unknown. Which, by the way, gives the amusing(?) result that restricting a table by its constraint is not a no-op.

> -- you have to make some decision at this point.

*I* would? I don't understand. Doesn't the SQL standard make a decision?

> Of course, you can redefine the DUPLICATE predicate so
> that it would map UNKNOWN/NULL to true but that would be a non-standard
> behaviour which is also inconsistent with many other cases where the
> 3vl unknown is mapped to false.

Inconsistent with "many other cases"? Not all cases, then? Either it's consistent, or it's not. SQL is not.

-- 
Jon