Re: Warning about null and open question to Oracle

> John Jones <john_at_iluvatar.tip.duke.edu> writes:
> First, a warning about null values. I knew that Null's were treated special
> in Oracle, but this problem is weird. I had a very LONG stored proc that
> was acting strange. We were comparing two tables to see if values where
> changing from one run to the next. The two tables were a like but the
> changes only got made to one table. In my proc I would open a cursor from
> one table and compare the values in the other table. If the values
> changed I would change them in the other table and then write the changes
> to a log file. The problem came when I noticed that I was updating almost
> every record every time, even when I ran the procedure when there were no
> changes to the first table. I spent 4 days working on this problem. 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???
>>>>

This behavior is PROPER relational logic -- ANYTHING compared to NULL yields a NULL, not a boolean result. Since a NULL is not TRUE, it will not behave like a TRUE result. Some people seem to forget this, or never learn it in the first place.

It is very simple to work around, if you are comparing columns which allow NULL values, using either the NVL function or the IS equivalence test.

Michael Stowe
Constellation Engineering, Inc.
http://www.mcs.com/~stowe Received on Thu Jun 15 1995 - 00:00:00 CEST