Re: not null - trivial, unexpected behavior

--- Josh Berkus <josh@agliodbs.com> wrote:
> Peter,
> 
> > Correction:  Each Boolean expression can have one of *three* values:
> > true,
> > false, unknown (null).  So those two sets will not be "complimentary"
> > in
> > the traditional sense because both expressions _should_ (modulo the
> > information I pointed you to) return "unknown".
> 
> I agree pretty strongly with Peter here ... the " = NULL " functionality
> should go.  I've already had to re-examine some of my functions for
> unexpected results due to = NULL possibly evaluating to TRUE, an outcome
> for which I did not plan.
> 
> Plus it leads those new to SQL92 into trouble, as it did with John :-)
> 
> -Josh Berkus

not a showstopper for me.

but, as i understand the sql92 standard, both att = null and att != null
are NOT sql92.  perhaps they're just remnants of the Cish nature of quel.

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

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.

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?

-j


__________________________________________________
Do You Yahoo!?
Spot the hottest trends in music, movies, and more.
http://buzz.yahoo.com/