Re: NULL value EQUALS to EMPTY string?!

From: Shaun Tancheff <shaun_at_tancheff.com>
Date: Tue, 08 Feb 2000 18:16:45 GMT
Message-ID: <38a05d87.1461938@news>

On Mon, 07 Feb 2000 13:24:37 GMT, in comp.databases.oracle.server Klim Samgin <klimsamgin_at_yahoo.com> wrote:

Oracle is NOT SQL-92 compliant, but according to the 8.0.5 release  notes, they reserve the right to be compliant in some future release.

In other words, Oracle among it may thousands of faults, sucks really, really, bad.

>Hi!
>
>It's notorious that table cells may have the special
>value null (or unknown).
>This value is different from the number 0,
>and it is also different from the empty string ''.
>
>But...
>
>SQL> create table test2(
> 2 n number,
> 3 c char(5),
> 4 v varchar2(5),
> 5 d date);
>
>Table created.
>
>SQL> insert into test2 values(1, null, null, null);
>
>1 row created.
>
>SQL> insert into test2 values(2, '', '', '');
>
>1 row created.
>
>SQL> commit;
>
>SQL> select * from test2;
>
> N C V D
>---------- ----- ----- ---------
> 1
> 2
>
>SQL> select * from test2 where c is null;
>
> N C V D
>---------- ----- ----- ---------
> 1
> 2
>
>SQL> select * from test2 where v is null;
>
> N C V D
>---------- ----- ----- ---------
> 1
> 2
>
>SQL> select * from test2 where d is null;
>
> N C V D
>---------- ----- ----- ---------
> 1
> 2
>
>SQL> select * from test2 where c='';
>
>no rows selected
>
>SQL> select * from test2 where v='';
>
>no rows selected
>
>SQL> select * from test2 where d='';
>
>no rows selected
>
>SQL> insert into test2 values(null, null, null, null);
>
>1 row created.
>
>SQL> commit;
>
>SQL> select * from test2 where n is null;
>
> N C V D
>---------- ----- ----- ---------
>
>
>SQL> select * from test2 where n='';
>
>no rows selected
>
>SQL>
>
>So, NULL is equal to '' for strings
>and is not equal to '' for numbers!
>
>Have you any comments?
>Is it possible to insert NULL value into char, varchar2?

Not it oracle. And yet you can create tables with

   mycol varchar2(13) DEFAULT '' NOT NULL, and WTF does that mean when '' == NULL ?

I think Ellison has crappy software envy, he's got crappier  stuff than MS and yet has less money.

 http://shaun.tancheff.com/       /?/                 -_-

  :-) shaun ;-] shaun_at_tancheff.com (Shaun Tancheff) .sigs freed Received on Tue Feb 08 2000 - 12:16:45 CST