Re: Strange SQL Results

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 Received on Sat Apr 10 1999 - 06:21:30 CDT