Re: Warning about null and open question to Oracle

stevec_at_zimmer.CSUFresno.EDU (Steve Cosner) wrote:
>In article <3rictg$r0j_at_crl2.crl.com> tactics_at_crl.com (Dewey Blaylock) writes:
>>John Jones (john_at_iluvatar.tip.duke.edu) wrote:
 <snip>
>>: I finally figured out that if both columns that were being compared were
>>: null then they were not equal. WHY????? It seems that null would be
>>: equal to null????? To any Oracle Corp. people out there ... Will this
>>: ever be fixed???
>>
>>You are exactly correct, a NULL does not equal NULL. Null is always
>>undefined. Since it has no definition it cannot be equal to another
>>undefined value. This is how NULL are treated in ORACLE and I don't
>>think it is something that needs to be fixed. One way to handle this
>>is to wrap nvl(your_column_her,0) in your cursor select or in the comparison
>>this will ensure that NULL will be handled identically.
>>
>>IF nvl(a,0) = nvl(b,0) then
>> do something;
>>END IF;
>>
>>gthomas_at_us.oracle.com
>----------------
>Watch out! The above example will fail when a=0 and b is null, and
>when a is null and b=0.
>
>The only way you can really be sure is with something like this:
> IF a = b or (a is null and b is null)
>
>Oracle's way of always returning FALSE to any comparison where a
>column value is null indicates that they don't work much in the *real*

                                                                 ^^^^^^^

No, it indicates that we adhere to ANSI/ISO standards like SQL/89 and SQL/92 which are adament in the way in which NULLS are treated. Specifically, ANSI demands that X <comp> Y returns UNKNOWN if X and/or Y is NULL. Not True, Not False, but Unknown.

BTW, we aren't returning FALSE, we are returning Unknown. If we returned false then "select * from dual where NOT ( null = null )" would return a row but it doesn't.

>world. If they really wanted to help, they would come up with some
>function to be used with comparisons like:
>
> If NULLS_ARE_REAL(a=b) then do_something
>

Write it. You have the ability to create pl/sql packages/functions in the database. You have the ability to invoke these functions in SQL.

>Such a function should treat two null values as equal, and if
>comparing < or >, nulls would always be less than anything else.
>
>How about it, Oracle???

Ok, here is the partial package, the rest you can fill in with your rules for comparision ( is a null < null for example?. Why is a null < anything else? )

	function "="( a in varchar2, b in varchar2 ) return number;
	pragma restrict_references ( "=", WNDS, RNDS, WNPS, RNPS );


	function "<"( a in varchar2, b in varchar2 ) return number;
	pragma restrict_references ( "<", WNDS, RNDS, WNPS, RNPS );

end nulls;
/

create or replace package body nulls
as

	function "="( a in varchar2, b in varchar2 ) return number
	is
	begin
		if ( a = b OR ( a is NULL and b is NULL ) ) 
		then
			return 1;
		else
			return 0;
		end if;
	end "=";

	function "<"( a in varchar2, b in varchar2 ) return number
	is
	begin
		if ( a is NULL and b is NOT NULL ) 
		then
			return 1;
		elsif ( a < b )
		then
			return 1;
		else
			return 0;
		end if;
	end "<";

end nulls;
/

select nulls."="( null, null )
from dual
/
NULLS."="(NULL,NULL)

                   1
 
 

select nulls."<"( null, 1 )
from dual
/
NULLS."<"(NULL,1)

                1
 

select * from dual
where nulls."="( null, null ) = 1
/
D
-
X

select * from dual
where nulls."<"( null, dummy ) = 1
/
D
-
X  

>
>stevec_at_zimmer.csufresno.edu (Steve Cosner)
>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Wed Jun 14 1995 - 00:00:00 CEST