CeDeT: OIF (Oracle is funny (that way)) #8

Three-valued logic comes from Nothing (and gets you nowhere)

I will use this page to give you plenty of reasons to avoid using NULLs!

Please note, that some people do not agree with me on this - see for example Should Nulls be considered harmful?

Last updated 07-02-2007


NOT IN NULL


NULL pointers


The empty string IS NULL


Confused? No! Try this then (comments in bold added by me (and SQL*Plus answers in Danish - but you know what it's saying)):
    SQL> select 'x' from dual where NULL IS NULL; NULL is NULL! (So far so good)


    '
    -
    x

    SQL> select 'x' from dual where '' IS NULL; The empty string IS NULL! (I told you so)
  
    '
    -
    x

    SQL> select 'x' from dual where '' IS ''; The empty string is not a keyword but NULL is! (Well, ok)
    select 'x' from dual where '' IS ''
                                  *
    FEJL i linie 1:
    ORA-00908: manglende NULL-nøgleord


    SQL> select 'x' from dual where ''=''; The empty string is not equal to the empty string! (Darn, and see aside below)

    Ingen rækker er valgt

    SQL> select 'x' from dual where NULL='';

    Ingen rækker er valgt

    SQL> select 'x' from dual where ''=NULL;

    Ingen rækker er valgt
Aside: The empty string is not not equal to the empty string (either) - but that's just 3VL for you...

Nulls: Nothing to Worry About

Read the article Nulls: Nothing to Worry About by Jonathan Gennick and Lex de Haan.

The catch here is that NULL is Nothing and that is what you must Worry About (even Oracle gets it wrong now and then, see Sightings below)!


Foreign Keys and NULLs

From Oracle9i Database Concepts manual we have:

Nulls and Foreign Keys
The relational model permits the value of foreign keys either to match the referenced primary or unique key value, or be null. If any column of a composite foreign key is null, then the non-null portions of the key do not have to match any corresponding portion of a parent key

I do believe that Chris J. Date would have a hard time with this particular reference to the relational model - see his book Database in Depth: The Relational Model for Practitioners (in association with Amazon.co.uk).


Nulls and Unique Keys

From Oracle9i Database Concepts manual we have:

Combine UNIQUE Key and NOT NULL Integrity Constraints
... In fact, any number of rows can include nulls for columns without NOT NULL constraints because nulls are not considered equal to anything. A null in a column (or in all columns of a composite UNIQUE key) always satisfies a UNIQUE key constraint.

...

Note:
Because of the search mechanism for UNIQUE constraints on more than one column, you cannot have identical values in the non-null columns of a partially null composite UNIQUE key constraint.

Nulls and B*Tree indexes

From The Tom Kyte Blog we have:

The facts are:

Read the rest in Something about nothing... and the follow-up Mull about Null....


Sightings

  1. Procedure wb_truncate_table supplied by Oracle w/Oracle Warehouse Builder 10g Release 1 (10.1) (comments in bold added by me):
    SQL*Plus: Release 10.1.0.2.0 - Production on Wed Jan 18 20:49:50 2006
    
    Copyright (c) 1982, 2004, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.7.0 - Production
    
    SQL> SHOW USER;
    USER is "OWBSTG"
    SQL> DESCRIBE wb_truncate_table;
    PROCEDURE wb_truncate_table
     Argument Name                  Type                    In/Out Default?
     ------------------------------ ----------------------- ------ --------
     P_TABLE                        VARCHAR2                IN
    
    SQL> EXECUTE wb_truncate_table(p_table=>NULL);
    BEGIN wb_truncate_table(p_table=>NULL); END;
    
    *
    ERROR at line 1:
    ORA-00903: invalid table name OK, but was this the intended behaviour?
    ORA-06512: at "OWBSTG.WB_TRUNCATE_TABLE", line 67
    ORA-06512: at line 1
    
    
    SQL> SET PAGESIZE 100;
    SQL> SET LINESIZE 2000;
    SQL> SELECT text FROM all_source WHERE name='WB_TRUNCATE_TABLE'
      2   ORDER BY name,type,line;
    
    TEXT
    ----------------------------------------------------------------------------------------------------
    PROCEDURE WB_Truncate_Table( p_table  VARCHAR2 )
    AUTHID CURRENT_USER AS
    ...
    BEGIN
    BEGIN
    IF (p_table = null) THEN return; I think not - should have read p_table IS NULL (and just return)!
    ELSIF (SUBSTR(p_table,1,1) = '"')
    THEN v_tableName := p_table;
    ELSE v_tableName := UPPER(p_table);
    END IF;
    END;
    ...   
  2. The max value of Null
  3. ON THE NOTHING THAT'S WRONG WITH NULLS