Re: So what's null then if it's not nothing?
Date: Tue, 29 Nov 2005 09:28:14 +0100
In article <1133191499.952147.182080_at_z14g2000cwz.googlegroups.com>,
> 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."
> 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.
*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.