Re: not null - trivial, unexpected behavior

John,

> but, as i understand the sql92 standard, both att = null and att !=
> null
> are NOT sql92.

You are correct.

> perhaps they're just remnants of the Cish nature of
> quel.

Tom seems to indicate that = Null is a result of (possibly misguided)
desire for compatibility with Microsoft ODBC.

> seems to me that if att != null is unknown, then att = null
> should be unknown as well. or at least documented as such.

Yup. Absolutely. Any expression which includes a NULL should evaluate
to NULL. If the expression includes an unknown value, its result must
also remain unknown.

> a practical example of where this becomes a problem
> is a query like
>
> select * from t where a != :aqual
>
> to do this properly for a null 'aqual' in sql92 i need to change '='
> to
> 'is not null', which is considerably more complex than simple
> variable
> substitution.

If this is a concern, then set the column to NOT NULL and force a value
to be inserted ... 0 for numerical columns, '' for VARCHAR, and
'1900-01-01' for dates.

Hmmm ... what is a good "no date" value for dates? Depends on the
application, I suppose.

> i guess a summary question would be : is a predicate like
> "att = null" even predicatable and, if not,
> why should any unpredicatble query
> be accepted by the database engine?

It *is* predictable in that "att = null" evaluates to False, regardless
of the value of att. att cannot be equal to Null since Null is the
"unknown value".

If you wish to test two values as equivalent because they both equal
NULL, then you can do like the following:

IF (COALESCE(att, 'NULL')) = (COALESCE(pbs, 'NULL'))

or:

IF (att = pbs) OR (att IS NULL and pbs IS NULL)

All of the troubles you mention above are the reason why some DB
theorists are opposed to the use of NULLs at all. However, NULLs are
part of the SQL standard, as is all null tests = False and all null
computations = Null. We gain little by flaunting the standard.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco