Re: Warning about null and open question to Oracle

In article <3rkfk6$m97_at_inet-nntp-gw-1.us.oracle.com> Thomas J Kyte <tkyte_at_us.oracle.com> writes:
>John Jones <john_at_iluvatar.tip.duke.edu> wrote:
>>First, a warning about null values. I knew that Null's were treated special
 <snip>
>>WHY????? It seems that null would be
> ^^^^^^^^^
>
>Because of ANSI, specifically, ISO 9075 states
>
>---------------------------------------------------------------
>8.2 <comparison predicate>
>
>...
>
>General Rules
>1.) Let X and Y be any two corresponding <row value constructor
>element>s. Let XV and YV be the values represented by X and Y,
>respectively.
>
>Case:
> a) If XV or YV is the null value, then "X <comp op> Y" is unknown
> b) If XV and YV are non-null values, then "X <comp on> Y" is
> true or false as follows:
> i) "X = Y" is true if and only if XV and YV are equal
> ii) "X <> Y" is true if and only if XV and YV are not equal.
> ...
> vii> "X <comp op> Y" is false if and only if "X <comp op> Y" is
> not true
>.....
>---------------------------------------------------------------
>
>In short, if any value in a comparision { =, <, >, <>, <=, >= } is
>NULL then the return is UNKNOWN, not true, not false. Clause (vii)
>above, coupled with the requirement in a) above, do not permit A=B
>to return TRUE or FALSE if A and/or B is null.

If the result is UNKNOWN, then why does the database return FALSE? Again, I say there should be a function to test the results. I suggest the following:

    "IF NULLS_ARE_REAL( A=B ) then DO_SOMETHING".

I think this would be appreciated by many application developers.

>
>To solve your problem, either use NVL or use IS NULL. For example:
>
>if ( nvl( a, 0 ) = nvl( b, 0 ) ) then
> .....
>end if;
>
>OR
>
>if ( a = b OR ( a is null and b is null ) ) then
> ...
>end if;
>
>Note that the first example, using the NVL function, may return
>erroneous results if 0 is a valid value for A or B.

THIS IS EXACTLY THE PROBLEM WITH NVL!
>
>The other solution is to not use NULLS in the database at all.

THERE ARE MANY PLACES WHERE IT MAKES SENSE TO USE NULLS.
>
>>equal to null????? To any Oracle Corp. people out there ... Will this
>>ever be fixed???
>
>If it ain't broke, don't fix it.

BUT IT SURE COULD BE IMPROVED!!!
> No, this will not be 'fixed', it is
>in line with the standard. Other databases behave the same way. I know
>earlier versions of other databases did it differently, but they are and
>have changed their handling of NULLS to come in line with the standard.
>
>
>Thomas Kyte
>tkyte_at_us.oracle.com
>Oracle Government
>

Steve Cosner (stevec_at_zimmer.csufresno.edu) (with apologies for the long post) Received on Wed Jun 14 1995 - 00:00:00 CEST