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

In article <1134593763.282919.322250_at_z14g2000cwz.googlegroups.com>, boston103_at_hotmail.com says...
>
> Saying that two truth tables are the same should make it blindingly
> obvious that there is no room to weasel out and say that NULL != NULL
> in this context.

Strange that the SQL committee is just as blind as me to the blindingly obvious, then.

> If one insists on NULL != NULL in the context of
> truth values, then the truth tables clearly are not the same. Using
> IS_NULL is just a trick serving to fix the broken, for no good reason,
> equality predicate over the domain of truth values. IS_NULL does not
> contribute anything substantial to the crucial decision to consider the
> two tables containing NULLs to be equal.

It may be "just a trick", but so what? SQL commonly considers tables containing NULLs to be equal in other contexts anyway---for purposes of union and intersection, for example. NULLs are (seemingly) sometimes equal and sometimes not; it's no problem to consider them equal for truth table comparison purposes.

> > SQL-99 seems to use "my system" (it isn't *my* system, though; I think
> > it's badly designed). Can you provide a SQL example where there is a
> > serious problem?
> >
>
> I gave you examples of simple logical expressions not being equivalent
> in my earlier response. You can easily imagine any SQL using such
> expressions.

So transformations for optimisation is your only objection?

> > > The optimizer cannot do much with your queries as it cannot rearrange
> > > and transform predicates to achieve better performance.
> >
> > That, of course, is a good point, but it applies to NULLs in all
> > domains.
>
> I do not undestand that at all.

There are many strategies for optimisation by expression transformation that are no longer valid when NULLs are taken into account.

> Of course, the real life database engine
> designers are not so stupid as to blindly adhere to the nonsensical
> standard stating that one cannot compare some truth values and do
> consider two truth tables with NULLs (or UNKNOWNs) in the maching rows
> to be in fact the same (of course if other matching rows contain the
> same not-NULL/not-UNKNOWN values).

Aren't they then just doing what I am suggesting: using a "trick" to compare truth tables? What database engines and designers are you talking about, by the way?

-- 
Jon