Re: Strange SQL Results

Dave Wotton a écrit :

> I think you're mistaken on this one Sybrand ...
>
> Firstly, your example of concatenating values and nulls is definitely
> wrong ( and is a digression anyway ): in general, the result of
> ValueA||null||ValueB is: ValueA||ValueB, not the null value.
>
> Returning to the original question. It boils down to "what is the value
> of: TRUE OR {comparison with null-value} ?"
>
> This can be tested with a simple example:
>
> SQL> create table dave1 ( DateA date, DateB date, DateC date);
>
> Table created.
>
> SQL> insert into dave1 values ('01-MAR-99','01-APR-99',null);
>
> 1 row created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> select count(*) from dave1
> 2> where dateA > to_date('01-FEB-99');
>
> COUNT(*)
> ----------
> 1
>
> SQL> select count(*) from dave1
> 2> where dateA > to_date('01-FEB-99')
> 3> or dateC > to_date('01-FEB-99');
>
> COUNT(*)
> ----------
> 1
>
> Note that the inclusion of an additional clause which tests against
> a null value did not stop the row being returned. Any test against a
> null value evaluates to FALSE, which when OR'ed with TRUE returns TRUE.
> ( This is Oracle version 7.3.3, BTW ). I agree with Phillippa, there's
> something strange here.
>
> Sybrand Bakker wrote in message
> <923733144.17217.0.muttley.d4ee154e_at_news.demon.nl>...
> >Hi Phillippa,
> >
> >This is because SQL, at least the SQL in Oracle has three valued logic. Any
> >test returns three possible values: true, false or null. I don't know
> better
> >than that any NULL overrides ANY results.
> >Eg if a concatenate in a string three columns and ANY of these three
> columns
> >is NULL the result will be NULL.
> >The same applies probably here. As columns can be NULL, if any of those
> >columns is NULL, the record will not be considered.
>
> Dave.
> --
> Remove "nospam" from my address to reply by email

A simple method to understand logical NULL values in logical expressions is to
consider NULL as
an unknown logical value. So : TRUE AND NULL evaluates to NULL (TRUE AND "An unknown
Value" depends on this unknown value and then is unknown value), TRUE OR NULL
evaluates
to TRUE, FALSE AND NULL evaluates to FALSE ...

If the entire expression evaluates to NULL then it is considered to be FALSE. Received on Thu Apr 22 1999 - 11:50:09 CDT