SQL NULL vs '' problem

From: <mikep_at_chemweb.com>
Date: Mon, 19 Jul 1999 16:09:25 GMT
Message-ID: <7mvija$43$1@nnrp1.deja.com>

There is either a serious bug in Oracle or I misunderstand how SQL/Oracle works (I assume the latter!). I expect the first count to be at least 1, and the second count to be 0:

SQLWKS> update my_table
     2> set
     3> my_table_row='' where my_other_table_row='jsmith';

1 row processed.
SQLWKS> select count(*) from usr where my_table_row=''; COUNT(*)

0
SQLWKS> select count(*) from usr where my_table_row is null and my_other_table_row='jsmith';
COUNT(*)

1
1 row selected.

Even after a commit, you get the same count results. The value of my_table_row before was a variety of values. I thought '' and NULL were different values, so why does it set it to NULL and even if they were the same value, why does a count of my_table_row='' give 0?

btw I am using
Oracle7 Server Release 7.3.3.4.0 - Production Release With the distributed and parallel query options PL/SQL Release 2.3.3.4.0 - Production
running on NT4

Thanks for any help

Mike

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Jul 19 1999 - 11:09:25 CDT