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

Jon Heggland wrote:
> In article <1134488609.199682.139210_at_g49g2000cwa.googlegroups.com>,
> boston103_at_hotmail.com says...
> >
> > > > Not being able to say whether two truth table for arbitrary expressions
> > > > are in fact the same. Did not I say that, like, a dozen times ?
> > >
> > > Wouldn't it be easy to fudge it using some IS NULL construct?
> >
> > How ?
>
> For example, whenever you feel like comparing two truth tables, instead
> of checking for each line if (x = y), check if ((x = y) or ((x is null)
> = (y is null))).

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

>
> > >And when
> > > do you need to do this anyway in a database context? I am looking for an
> > > scenario as concrete as my 3VL capital knowledge example; that's why I'm
> > > not satisfied with what you've said a dozen times before.
> > >
> >
> > OK. In your system (I cannnot call it logic), where truth tables
> > cannot be judged equal, you cannot prove anything (since each
> > derivation step is supposed to be truth-preserving and you cannot
> > determine that). It may be OK in your system if it's never supposed to
> > be used used for any sort of deduction, though.
>
> 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.

> > 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. Logical expression equivalence, among other things, is what allows the optimizer to transform and rearrange the original query components, no matter what logic, 2VL or 10VL, one chooses to use. If one abandons the notion of equivalence, then no transformation is possible. 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).

> --
> Jon
Received on Wed Dec 14 2005 - 21:56:03 CET