Re: Warning about null and open question to Oracle

stevec_at_zimmer.CSUFresno.EDU (Steve Cosner) wrote:
>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".

Again, I tell you, It returns UNKNOWN, NOT TRUE, NOT FALSE. To prove this try:

SELECT * FROM DUAL WHERE NULL = NULL; 0 rows returned

SELECT * FROM DUAL WHERE NOT ( NULL = NULL ); 0 rows returned

If, NULL = NULL returned FALSE, then NOT (NULL=NULL) would return TRUE, right? Well NOT (NULL=NULL) does not return TRUE, it returns UNKNOWN, eg: Neither True nor FALSE.

One of my previous posts on this subject shows you exactly how to write your NULLS_ARE_REAL function in PL/SQL to be used in SQL calls and other PL/SQL routines if you want to.

>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!
>

I may just be picking on semantics here BUT, this is not a problem with NVL, NVL works just dandy. It is however a problem with peoples general misunderstanding of how NULLS have been dictated to work in relational databases adhering to the ANSI/ISO standard.

>>
>>The other solution is to not use NULLS in the database at all.
>
>THERE ARE MANY PLACES WHERE IT MAKES SENSE TO USE NULLS.
>

Then live with the consequences. A null is not equal to null (just like in math, infinity is not the same as infinity). How can something that does not exist be the same as something else that does not exist?

>>
>>>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!!!
Again, improve it by writing Pl/SQL to make it behave the way *you* want it too. The way you want it to work is probably different from the way someone else wants it to. Thats the whole point behind extensible SQL.

>
>> 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)
>
>
>
>

tkyte again. Received on Wed Jun 14 1995 - 00:00:00 CEST