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

In article <ji7so1l73jsh1rkjencq25ibirrh7oe8h3_at_4ax.com>, hugo_at_pe_NO_rFact.in_SPAM_fo says...
> I agree with this. Removing NULLs from the results of a DISTINCT
> operation is weird.

I don't think I have actually said that it is weird. In fact, I think both alternatives are equally justified, which is my point: There is no obvious correct solution; so the choice is made for convenience, and users must be taught which solution is used. They cannot trust their intuition, and have to remember when NULLs seem to be equal and when they don't.

> But let's place this in a broader perspective. How should NULLs be
> handled in a GROUP BY operation. [...]

All very 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?
>
> If your DB rejects the second insert, you've found a bug. (Or an awful
> design <grin>).

Oracle does, but Oracle is no stranger to awful design. :)

> >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.
>
> In most cases, that's as easy as adding a NOT NULL constraint to the
> column.

I know that, of course. It would be even easier if I *didn't* have to add NOT NULL.

> If that doesn't work in a specific case, then make sure that you
> formulate the constraint such that it will evaluate to false for each
> row you don't want to accept.

Yes, I know. But that doesn't jive with what database textbooks teach: That constraints are assertions that hold true for all possible values of the database.

> It's not a question of more or less correct, BTW. It's just a question
> of how the rules are defined. But it might help to consider that *if* a
> constraint would reject all rows that evaluate to false or unknown, most
> CHECK constraints would implicitly disallow NULLs in many columns.

I do of course consider that. As a feature. I also think NOT NULL should be the default.

> The
> current definition gives the DBA the control to either add or not add an
> explicit NOT NULL constraint.

My definition wouldn't change that. It would "give the DBA the control" to add an explicit "OR value IS NULL" if s/he wants a constraint on a nullable column. The expressive power *does not change*.

-- 
Jon