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

Jon Heggland wrote:
[...]

> 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.

It's simple really if you bother to read something about the 3vl. According to the 3vl rules, whether two nulls are distinct or equal is unknown which maps to false in the 2vl.

>
> 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."
>

Oh yes. Ascending or descending has got nothing to do with null 'ordering'. In order to place nulls at the top/bottom of a result set,  one has to say 'order by nulls first/last' with 'last' being the default thus creating a user mapping that converts nulls to the least/greatest value.

> > 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.
>

What's so hard about that ? Say, you have a set {1,2,3}, then you get a new value 1. Clearly, you have a duplicate. Now, let's assume your new value is null. You cannot say whether it's a duplicate or not -- the result of set membership operation (or comparison if you prefer) is unknown. The 3vl unknown is mapped to false, therefore, null is *not* a duplicate.

> > (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.

With constraints, one wants to be sure that a value is *rejected* if the constraint is *NOT* satisfied. So here again we have: if (unknown) reject -> if false reject -> a row is accepted.

>Which, by the way, gives the amusing(?) result
> that restricting a table by its constraint is not a no-op.
>

I do not understand that.

> > -- you have to make some decision at this point.
>
> *I* would? I don't understand. Doesn't the SQL standard make a decision?

Sure, the standard does, 'you' is an impersonal pronoun here.

>
> > 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.

You are being a little too righteous here. In some cases SQL designers made consistent decisions with respect to NULL and a lot of other things, in some others they did not.

> --
> Jon
Received on Tue Nov 29 2005 - 16:11:29 CET