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

In article <1134646052.578144.164260_at_f14g2000cwb.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.
>
> Great minds think alike.

I'll take that as an insult. :) Note; I'm not saying that SQL-99 (or SQL in general) is good or sensible, just that

1. The "obvious" problems you talk about are definitely not obvious to a lot of people.
2. My interpretation of Codd, right or wrong, seems thus to be the most commonly accepted.

> > 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.
>
> That's a possible interpretation (having two different equaity
> predicates) as I mentioned in my earlier message on 'meta' vs. 'object'
> language, but completely unnecessary since a simlpler interpretation
> would suffice especially when backed up by common sense. While one can
> argue that the rationale for NULL = NULL evaluating to FALSE, say, in

Nitpick: evaluating to UNKNOWN/NULL.

> the integer domain makes sense because we presumably do not know
> whether NULL represents the same values, one cannot do so in the
> domain of truth values where UNKNOWN (aka NULL) represents *the same*
> degree of truth (if one wants to have n degrees of truth, one uses
> n-VL). Therefore, sacrificing simplicity and common sense for the
> sake of 'uniformity' does not apper to be a very clever decision.

Common sense isn't as common as one would like to think. And the simplicity argument cuts both ways.

> Regarding 'uniformity', witness TRUE OR NULL evaluatiing to TRUE, not
> NULL as a uniformity purist would expect.

Yes, and I do object to that.

> > > 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?
>
> No, and you can easily locate my other objections in the earlier
> messages.

None practical enough for my taste. But I won't bother you more about it if it is so difficult to come up with a concrete example.

> > 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?
>
> Folks who wrote SQL Sever, Oracle, et.

Oracle follows the SQL-99 boolean rules, except that it doesn't allow boolean columns. Example:

create or replace function booltest return varchar2 is   b1 boolean;
begin
  b1 := NULL;

  if    (b1 = b1) = TRUE  then return 'T';
  elsif (b1 = b1) = FALSE then return 'F';
  elsif (b1 = b1) = NULL  then return 'Really strange';
  elsif (b1 = b1) IS NULL then return 'U';

This returns 'U', in accordance with the standard. What does SQL Server do?

-- 
Jon