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

From: Jon Heggland <>
Date: Tue, 29 Nov 2005 17:36:59 +0100
Message-ID: <>

In article <>, says...
> 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.

By the exact same argument, duplicate NULLs should *not* be removed, because they are not equal. Choosing one over the other is an arbitrary decision; an artifact of how you formulate the SELECT DISTINCT operation.

And this "unknown maps to false"---where do you get that from?

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

And to place 'A' before 'B', one has to say that 'order A before B'.  

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

I didn't understand the phrase "set of existing column non-nulls".

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

Now insert NULL again. Still no duplicate? Not according to SQL, and it seems you agree that that is sensible.

But now take a table with a composite key (or a set of tuples, if you prefer). Insert (NULL, NULL). Insert (NULL, NULL) again. No duplicate. Insert (1, NULL). No duplicate. Insert (1, NULL) again. Duplicate! Why?

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

I might want to be sure that the value is accepted only if the constraint is satisfied. Why is one more correct than the other? I know which one I think is easier to state and understand.

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

Create a table with an attribute A, and a constraint that says that A must be greater than five. Insert ten rows successfully. Select the rows where A is greater than five. How many rows do you get?

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

Ok. Now, with 2VL, the unique constraint behaviour does *not* depend on how you define it, and there is no decision to arbitrarily make. It is clear and simple, and doesn't surprise anyone. I think it is better.

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

Being sometimes consistent and sometimes not, is not being consistent. That is kind of the point of consistency. That said, I admit to being righteous and arrogant. Sorry. :)

Received on Tue Nov 29 2005 - 17:36:59 CET

Original text of this message