Tuesday, May 17, 2005

NULL vs Nothing

In Oracle, there is a difference between 'null' and nothing at all. Here is my story.

I discovered this when playing with default values. First, I created a table that had a default value for one column. Then I tried to insert a row that had nothing at all, and it wouldn't use the default value. So I tried inserting a row with null, and it didn't assign the default value. Observe.

SQL> create table atable (
2 last_name varchar2(32) not null,
3 first_name varchar2(32) not null,
4 rating smallint default 0);

Table created.

SQL> insert into atable values ('Smith', 'John', null);

1 row created.

SQL> select * from atable;

LAST_NAME FIRST_NAME RATING
-------------------------------- -------------------------------- ----------
Smith John

Notice it inserted NULL, and not the default value.

SQL> insert into atable values ('Smith', 'John');
insert into atable values ('Smith', 'John')
*
ERROR at line 1:
ORA-00947: not enough values

Why doesn't this work? You may know this already, but in the words of Dan Morgan: "By not specifying column names you are signifying that you are providing values for ALL columns. This is why it is a very bad practice as doing an ALTER TABLE ADD immediately invalidates all SQL statements."

So let's do it the proper way and see if there is a difference.

SQL> insert into atable (last_name, first_name) values ('Smith', 'John');

1 row created.

SQL> select * from atable;

LAST_NAME FIRST_NAME RATING
-------------------------------- -------------------------------- ----------
Smith John
Smith John 0

And there is a difference. Excellent. Out of curiousity between the two ways of inserting rows, I tried this:

SQL> insert into atable (last_name, first_name, rating) values ('Smith', 'John', null);

1 row created.

SQL> select * from atable;

LAST_NAME FIRST_NAME RATING
-------------------------------- -------------------------------- ----------
Smith John
Smith John 0
Smith John

So I was thinking, why isn't this null being replaced with the default value? Isn't the default value there for instances to replace null? If you're as skeptical as I am, it will make sense after this second test, where I add the "NOT NULL" constraint.

drop table atable;

create table atable (
last_name varchar2(32) not null,
first_name varchar2(32) not null,
rating smallint default 0 NOT NULL);

SQL> insert into atable (last_name, first_name, rating) values ('Smith', 'John', null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("ROBERT"."ATABLE"."RATING")

That's when I figured out where I was confused. There are 3 things you can assign to a column when doing an insert:
1. a value (eg: 0)
2. null
3. nothing at all

See, there is a difference between #2 (null) and #3 (nothing at all). To wit:
1. Using null (#2) is 'ok' if you are inserting without specifying columns. Nothing at all (#3) is not.
2. Null (#2) will not be replaced by a default value when inserting, whereas nothing at all (#3) will.

When I started looking at stored procedures, I found even more difference between NULL and nothing. My second story starts off as an investigation of passing NULL to stored procedures.

There is no such thing as "NOT NULL" for stored procedure parameters.

I noticed that the "NOT NULL" keyword can't be used in procedure parameters (nor can NULL). Apparently that is for creating tables only. You can not force input parameters to a procedure to be non-null. All you can do is start your procedure by verifying the input parameters.

SQL> create or replace procedure MyProc1 (some_value IN number NOT NULL)
2 AS
3 BEGIN
4 NULL;
5 END;
6 /

Warning: Procedure created with compilation errors.

Nor can you allow it to be NULL.

SQL> create or replace procedure MyProc2 (some_value IN number NULL)
2 AS
3 BEGIN
4 NULL;
5 END;
6 /

Warning: Procedure created with compilation errors.

You can, however, assign default values. However, note the difference between assigning "NULL" and assigning nothing at all.

SQL> create or replace procedure MyProc3 (some_value IN number := 0)
2 AS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE(some_value);
5 NULL;
6 END;
7 /

Procedure created.

SQL> exec MyProc3();
0

PL/SQL procedure successfully completed.

SQL> exec MyProc3(NULL);

PL/SQL procedure successfully completed.

SQL> exec MyProc3(1);
1

PL/SQL procedure successfully completed.

Bottom line, there is a difference NULL and nothing at all for both tables and stored procedures. So what is a guy to do?
1. Understand there is a difference between NULL and nothing
2. For tables, use "NOT NULL" and for stored procedures, verify the input manually
3. Use default values for both tables and stored procedures when passing nothing at all.

Comments:
NULL means "Not Known". Not knowing something is very different from knowing that something is nothing (for which the empty string '' is provided)!

When you insert a row that explicitly includes a NULL, you are explicitly stating you don't know what value should be included for that column. That is again very different from just missing it out. The one is a deliberate action and acknowledgement of the limits to knowledge. The other could be just a mere oversight.

This is why I cannot agree with your advice 'For tables, use NOT NULL'. That is a constraint, and constraints should describe business rules. So *if* your business rule states that for a column you must always know a value, then sure: use NOT NULL. It's what it's there for. But it is frequently the case that things will be unknown at the time of data entry, and if so, a not null constraint is entirely inappropriate, and the explicit assignment of NULL to a value is (in my view) better practice than just remaining silent on the matter.

Incidentally, because NULL means 'I don't know', you can never equate anything to NULL... even itself. Two unknowns are two unknowns, not two bits of the same unknown... for to be able to say that would require knowing something about them!! Thus:

SQL> select count(*) from emp where 1=1;

COUNT(*)
----------
14

SQL> select count(*) from emp where null=null;

COUNT(*)
----------
0

Whilst 1=1 evaluates to 'aways true', and hence every row in EMP gets counted, NULL=NULL is never true, and hence no rows are selected or counted.

Regards
HJR
 
Thanks Howard for the correction and further explanation. "NULL" is a fascinating topic and sure to be the focus of another upcoming post.

In response to the May 19th article on Dynamically assigning size of varchar2, Tom Kyte commented on a way to force parameters to a stored procedure to be NOT NULL without having to validate inputs yourself explicitly.

http://thinkoracle.blogspot.com/2005/05/dynamically-assigning-size-of-varchar2.html

"you can use subtypes (trick that works with the NULLS from your other blog too -- if you want parameters that are NOT NULL, you can create a subtype which is NOT NULL and use that type...)"

create or replace package types as
subtype not_null_type is varchar2(32) NOT NULL;
end;

create or replace procedure MyProc4 (some_value IN types.not_null_type := 0)
as
begin
dbms_output.put_line(some_value);
end;

SQL> exec MyProc4();
0

PL/SQL procedure successfully completed.

SQL> exec MyProc4(NULL);
BEGIN MyProc4(NULL); END;

*
ERROR at line 1:
ORA-06550: line 1, column 15:
PLS-00567: cannot pass NULL to a NOT NULL constrained formal parameter
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

However, note that this does NOT work for tables!

create or replace procedure MyProc4 (some_value IN atable.last_name%TYPE := 0)
as
begin
dbms_output.put_line(some_value);
end;

SQL> exec MyProc4(NULL);

PL/SQL procedure successfully completed.

So what is a guy to do?
1. Validate the input of a stored procedure yourself, or
2. Create a "not null" type in a package as Tom described, and let Oracle do your validation, but
3. Do NOT use the %TYPE of a column in a table, because the NOT NULL property will be ignored.
 
I received the following from Tom in email:

You can use constrained subtypes in PLSQL to do the NOT NULL'ness

ops$tkyte@ORA9IR2> create or replace package foo
2 as
3 subtype mytype is number NOT NULL;
4
5 procedure bar( x in mytype );
6 end;
7 /
Package created.

ops$tkyte@ORA9IR2> create or replace package body foo
2 as
3 procedure bar( x in mytype )
4 as
5 begin
6 dbms_output.put_line( 'x = ' || x );
7 end;
8 end;
9 /
Package body created.

ops$tkyte@ORA9IR2> exec foo.bar( null );
BEGIN foo.bar( null ); END;

*
ERROR at line 1:
ORA-06550: line 1, column 16:
PLS-00567: cannot pass NULL to a NOT NULL constrained formal parameter
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
 
This comment has been removed by a blog administrator.
 
Hi,

Another example why null is not nothing, but it is an unknown value:

SQL> select 1 from dual where 1 in (2,null);
no rows selected
as there is a unknown value in the list, the IN evaluation is UNKNOWN, unknown is not true, so no rows are returned

SQL> select 1 from dual where 1 in (2);
no rows selected
there is only known values in the list, the IN evaluation is FALSE, false is not true so no rows are returned

SQL> select 1 from dual where 1 not in (2,null);
no rows selected
there is a unknown value in the list, the IN evaluation is UNKNOWN, so NOT IN is evaluated to UNKOWN, that is not true and no rows are returned

SQL> select 1 from dual where 1 not in (2);
1
----------
1
1 row selected.
there is only known values in the list, the IN evaluation is FALSE, so NOT IN is evaluated to TRUE, and rows are returned

The 2 preceding results shows again a difference between nothing and unknown.

Regards,
Franck.
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?