Re: not null - trivial, unexpected behavior

--- Josh Berkus <josh(at)agliodbs(dot)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/