Re: CHAR datatype and NULL values

SFidai writes
> Consider following SQL statements:
>
> col1 char(10),
> col2 number();
>
> update table1 set col1 = '' where col2=2;
> update table1 set col1 = null where col2=1;
>
> select count(*) from table1 where col1 = '';
>
> The above query will not find any records as col1 is set to null and
> you have
> to use IS NULL or IS NOT NULL verbs to select records with null
> values.
 

I consider this a bug in Oracle, at least insofar as ANSI compatibility goes. Oracle apparently sets nullable char fields to NULL when confronted with a zero-length character string. This seems to be true for CHAR and VARCHAR2 fields (new in System 7). This is just plain wrong. The CHAR field should be set to blanks (right-blank-pad semantics always apply to fixed CHAR fields) and the VARCHAR2 field should be set to a zero-length string. Neither of these values is considered to be NULL in ANSI SQL.

The only way you should be able to set a field to NULL is to say SET col = NULL, to use NULL on an INSERT, to omit the field on an INSERT, or to generate a NULL value from an INSERT ... SELECT. There are no other ways to do it, certainly not the way that is demonstrated.

Hey, anyone from Oracle who's lurking, why is this implemented "wrong"?

Jon Rosen Received on Sat Jun 04 1994 - 06:19:22 CEST