This discussion is archived
1 2 3 26 Previous Next 385 Replies Latest reply: Aug 20, 2007 5:26 AM by Alessandro Rossi RSS

Treatment of zero-length strings as NULLs?

551707 Newbie
Currently Being Moderated
As an Oracle newbie, I have been very curious and just discovered that Oracle treats zero-length empty string as NULL.
SQL> SELECT (
  2    CASE
  3      WHEN '' IS NULL THEN 'null'
  4    ELSE 'not null'
  5    END
  6  ) "IS NULL?"
  7  FROM dual;
  8  /

IS NULL?
--------
null
I know that Sybase ASE, MS SQL Server and MySQL all return 'not null' from the query above. So, why does Oracle treat empty string as NULL?

This is not pure academic question since the treatment of empty strings as NULLs has one very serious consequence: an application cannot distinguish between "touched" and "non-touched" columns. For example, the blank field in an application form that represents a person's mobile tel. number could mean:

(1) it is known fact that the person HAS NOT tel. number at all (the field is touched by empty string)
(2) the person's tel. number is UNKNOWN, NULL (the field is not touched)

Testing the underlying data in the database (NULL?/NOT NULL?) the application can mark the blank field with labels 'not exists' or 'unknown' accordingly. This distinction is not possible in Oracle since Oracle treats both NULL and '' as NULL.

I would appreciate if somebody could explain the reasons for this.

Regards

Albert
  • 1. Re: Treatment of zero-length strings as NULLs?
    Justin Cave Oracle ACE
    Currently Being Moderated
    There have been a couple of recent blog posts on the meaning of NULL

    http://radiofreetooting.blogspot.com/2006/12/title-is-null.html
    http://igor-db.blogspot.com/2006/12/null-is-my-middle-name.html

    Andrew's entry talks about Codd's attempts later in life to categorize all the possible meanings for NULL (i.e. a phone number might not be known, it might not exist, the user might not know at the instant in time they were filling out the form, etc). When you start trying to figure out exactly what NULL might or might not mean, it starts to get a bit sticky.

    Realistically, though, the answer is probably that it made sense 25 years ago (and makes some sense today, though the final decision would probably be different) to say that there is a single sense of NULL and that we'd roll up NULLs that are "non existant" with NULLs that are "we don't know at the moment" with NULLs that are "unanswered" and have a single NULL. Once Oracle made that decision and people started writing code that assumed '' was NULL, there was no going back because doing so would break boatloads of existing code.

    Justin
  • 2. Re: Treatment of zero-length strings as NULLs?
    551707 Newbie
    Currently Being Moderated
    Thanks a lot for links.

    <<My take on this is coloured by the fact the NULL is my middle name. Or to be more precise, my parents chose not to give me a middle name. So let me make it quite clear that NULL is NOT "Unknown". I know for a fact, backed up by my birth certificate, that I do not have a middle name.>>

    This is wrong logic. If he knows that he doesn't have a middle name, that information is NOT NULL (the field is touched by zero-length string in the meaning: "Middle name doesn't exist at all"). If he (or operator) doesn't know his middle name or doesn't even know if it exists, that information is NULL, UNKNOWN and the field will not be touched at all.

    NULL always means UNKNOWN. Null set is the set containing NOTHING, NO elements (pieces of information) - the empty set of information. Just as 0 (zero) is not null in numeric world, thus an empty-string is not null in alphanumeric world because zero-length empty string is KNOWN value: "I KNOW that the value of atribute doesn't exist".

    Regards

    Albert
  • 3. Re: Treatment of zero-length strings as NULLs?
    Justin Cave Oracle ACE
    Currently Being Moderated
    It's very much a philosophical argument here. In Oracle, NULL means both "known not to exist" and "unknown" as well as the dozen or so other "flavors" of NULL that Codd had identified. In other systems, an empty string might represent "known not to exist," but that's a philosophical difference. There is no single, universally agreed upon definition of NULL that says that one's middle name cannot be NULL.

    Since no system lets you represent and differentiate between the dozen different types of NULL, you always have some ambiguity in what NULL really means. Oracle chooses to collapse all 12 or 13 meanings into a single value, NULL. Other databases choose to collapse all 12 or 13 meanings into two values, NULL and the empty string. Philosophically, I can understand the value of having a single representation of NULL. Practically, I can understand the value of differentiating between at least the "known not to exist" and the "unknown" flavors of NULL. When Oracle was designed 25 years ago, the philosophers won out, and that's realistically how it's going to treat the empty string forever.

    Note, however, that CHAR and VARCHAR2 types have different semantics
    DECLARE
      empty_char CHAR(1) := '';
      empty_varchar2 VARCHAR2(1) := '';
    BEGIN
      IF( empty_char IS NULL )
      THEN
        dbms_output.put_line( 'CHAR(1) is null' );
      ELSE
        dbms_output.put_line( 'CHAR(1) is not null' );
      END IF;

      IF( empty_varchar2 IS NULL )
      THEN
        dbms_output.put_line( 'VARCHAR2(1) is null' );
      ELSE
        dbms_output.put_line( 'VARCHAR2(1) is not null' );
      END IF;
    END;

    CHAR(1) is not null
    VARCHAR2(1) is null
    Justin
  • 4. Re: Treatment of zero-length strings as NULLs?
    John Spencer Oracle ACE
    Currently Being Moderated
    "thus an empty-string is not null in alphanumeric world because zero-length empty string is KNOWN value: "I KNOW that the value of atribute doesn't exist"."

    Well, as Justin noted. it really is a philosophical question, but a zero length string does not, inherently, mean anything at all. Many times, particularly coming out of the C world, an empty string could easily be an artifact of a truly unknown value. C uses a null character (ASCI 0) as a string terminator, so an empty string ('') is really a single byte long. If you were pulling information off of a form in C and sending it to the database, any alpha field that was not filled in, however that happened, would be sent as an empty string. So, now what is the difference between NULL (I never even touched that field on the form) and the empty string (I deliberately left the field empty because I know the value does not exist)?

    If differentiating between NULL as unknown and something else as does not exist at all, then you could do something like:
    SQL> DECLARE
      2     empty_str VARCHAR2(1) := CHR(0);
      3     null_str  VARCHAR2(1) := NULL;
      4  BEGIN
      5     IF empty_str IS NULL THEN
      6        DBMS_OUTPUT.Put_Line('CHR(0) is null');
      7     ELSE
      8        DBMS_OUTPUT.Put_Line('CHR(0) is not null' );
      9     END IF;
    10     IF null_str IS NULL THEN
    11        DBMS_OUTPUT.Put_Line('NULL is null');
    12     ELSE
    13        DBMS_OUTPUT.Put_Line('NULL is not null');
    14     END IF;
    15  END;
    16  /
    CHR(0) is not null
    NULL is null

    PL/SQL procedure successfully completed.
    You still can't do:

    IF empty_str = ''

    but you can test explicitly for

    IF empty_str = CHR(0)

    John
  • 5. Re: Treatment of zero-length strings as NULLs?
    William Robertson Oracle ACE
    Currently Being Moderated
    I personally find it a great simplification that there is only one kind of empty for VARCHAR2 strings. For example, what if we declared SURNAME to be NOT NULL because we really want to make sure that everyone has a surname. If we allowed '' as a not-null value, to me that would defeat the point of the NOT NULL constraint. We would have to declare an additional check constraint to ensure that LENGTH(surname) > 0.

    btw Oracle could still introduce this behaviour for another datatype, most likely VARCHAR:

    Although the VARCHAR datatype is currently synonymous with VARCHAR2, the VARCHAR datatype is scheduled to be redefined as a separate datatype used for variable-length character strings compared with different comparison semantics.

    For some reason, we don't seem to have this discussion about dates and numbers.
  • 6. Re: Treatment of zero-length strings as NULLs?
    551707 Newbie
    Currently Being Moderated
    I am afraid that we've just found out a bug or at least design flaw in Oracle architecture. There is severe inconsistency between server SQL and PL SQL treating the same, identical thing:


    -- Your example -------------------------------------
    DECLARE
      empty_char CHAR(1) := '';
    BEGIN
      IF( empty_char IS NULL )
      THEN
        dbms_output.put_line( 'CHAR(1) is null' );
      ELSE
        dbms_output.put_line( 'CHAR(1) is not null' );
      END IF;
    END;

    CHAR(1) is not null
    -- My example ---------------------------------------
    CREATE TABLE test1 (
    empty_char CHAR(1)
    );
    INSERT INTO test1(empty_char) VALUES('');
    SELECT (CASE
              WHEN empty_char IS NULL THEN 'CHAR(1) is null'
            ELSE
             'CHAR(1) is not null'
            END) "empty_char"
    FROM test1;

    empty_char
    -------------------
    CHAR(1) is null
    Regards

    Albert
  • 7. Re: Treatment of zero-length strings as NULLs?
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    A thread from yesterday :
    character value with a length of zero

    Nicolas.
  • 8. Re: Treatment of zero-length strings as NULLs?
    William Robertson Oracle ACE
    Currently Being Moderated
    Possibly rather academic since you would never actually use CHAR ;)
  • 9. Re: Treatment of zero-length strings as NULLs?
    castorp Explorer
    Currently Being Moderated
    I personally find it a great simplification that
    there is only one kind of empty for VARCHAR2 strings.
    For example, what if we declared SURNAME to be NOT
    NULL because we really want to make sure that
    everyone has a surname. If we allowed '' as a
    not-null value, to me that would defeat the point of
    the NOT NULL constraint. We would have to declare an
    additional check constraint to ensure that
    LENGTH(surname) > 0.
    Very good point, I have never looked at it from this point.
  • 10. Re: Treatment of zero-length strings as NULLs?
    551707 Newbie
    Currently Being Moderated
    Hi William,

    allow me to disagree with you. It's not academic. Who says that I would never actually use CHAR? Why CHAR exists at all? But regardles of that and regardless of the main issue of this thread (zero-length strings as NULLs ???), one thing should be clear - the system must be CONSISTENT inside itself. It should not treat zero-length string today as NULL and tomorrow as NOT NULL.

    1. In Justin's example the empty_char variable of type CHAR(1) was declared and zero-length string was assigned to it.
    2. In my example a table with the empty_char column of the same type CHAR(1) was created and zero-length string was inserted into the column.
    3. In Justin's example PL/SQL engine says the empty_char is NOT NULL.
    4. In my example server SQL engine says the empty_char is NULL.

    Trying to figure out this contradiction as academic is completely beyond me.

    Albert
  • 11. Re: Treatment of zero-length strings as NULLs?
    William Robertson Oracle ACE
    Currently Being Moderated
    Yes, I agree the handling of CHAR values should be consistent between PL/SQL and SQL.

    > Who says that I would never actually use CHAR? Why CHAR exists at all?

    It provides useless functionality for some sort of ANSI compatibility reasons I have never understood. Why would anyone want to blank-pad anything? I have never seen a use for it in the 15 years it has existed.
  • 12. Re: Treatment of zero-length strings as NULLs?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Devil's advocate.

    A data type like VARCHAR2 and NUMBER describes a set of possible values. Imagine a huge circle with all the numbers floating in them (yes you can add some special FX to the image if you want to).

    A NUMBER variable can only be equal to a value in that data set. If not, then that variable is null.

    A VARCHAR2 defines a set of character values. Is an "empty string" a value in that set? Or like a NUMBER, is a VARCHAR2 variable null when it does not contain any values from that set?

    Saying that NULL means unknown is also false. NULL means simply that the variable (or column) has no value. Nothing more. And that is likely the problem as we want to have more meaning than just that - e.g. is the the value unknown or is the known value for that without a value?
  • 13. Re: Treatment of zero-length strings as NULLs?
    dccase Explorer
    Currently Being Moderated
    The NUMBER circle has the handy feature of containing the number Zero,
    which is, of course, different from Null.

    There is no equivalent in Oracle's VARCHAR2 circle.
    In some other databases, the empty string plays the role of Zero.

    Which is right? The first database you work with.
    Which are wrong? The other databases you need to support to make your application "database independent".

    Doug
  • 14. Re: Treatment of zero-length strings as NULLs?
    551707 Newbie
    Currently Being Moderated
    Saying that NULL means unknown is also false. NULL means simply that the variable
    (or column) has no value. Nothing more. And that is likely the problem as we want
    to have more meaning than just that - e.g. is the the value unknown or is the known
    value for that without a value?
    Marry Christmas!

    I recommend you learning the basics of set theory and relational algebra by at least reading the book C. J. Date: "An Introduction to Database Systems".

    NULL means that NO value was assigned/entered/inserted to/into a variable/form-field/column. And the value was not assigned/entered/inserted BECAUSE the value was UNKNOWN at the moment of assignment/entering/insertion or it was/is unknown permanently. So:
    NULL <=> NO VALUE <=> UNKNOWN
    where <=> means IS IDENTICAL (or IS SYNONYM).

    Not one of the relavant RDBMS systems on the market today handles zero-length strings as NULLs (Sybase ASE, MS SQL Server, IBM DB2, MySQL, PostgreSQL etc.). These RDBMS are perfectly aware of the following possibilities:

    1. Scott has the telephone number and it will be inserted as is (NOT NULL):
    phone_num = <some_value>
    2. Scott has not the telephone device at all (because he doesn't like it), so his "telephone number" will be inserted as empty string (NOT NULL):
    phone_num = ''
    3. Scott has the telephone device, but the operator doesn't know its number:
    phone_num IS NULL, UNKNOWN, NO VALUE
    4. Nobody knows if Scott has the telephone device at all:
    phone_num IS NULL, UNKNOWN, NO VALUE
    So, as you can see, the treatment of empty strings as NULLs is obviously Oracle's "exclusivity" on the database market. This "exclusivity" is the main cause of the unseen confusion concerning the meaning of NULLs, blank strings or zero-length strings at OTN Discussion Forums. The "exclusivity" itself, I am afraid, is not a bug. It's the consequence of serious design flaws deeply in Oracle's architecture, as can be illustrated by the following two contradictory examples (already mentioned in this thread):


    EXAMPLE 1.
    DECLARE
      empty_char CHAR(1) := '';
    BEGIN
      IF( empty_char IS NULL )
      THEN
        dbms_output.put_line( 'CHAR(1) is null' );
      ELSE
        dbms_output.put_line( 'CHAR(1) is not null' );
      END IF;
    END;

    CHAR(1) is not null
    EXAMPLE 2.
    CREATE TABLE test1 (
    empty_char CHAR(1)
    );
    INSERT INTO test1(empty_char) VALUES('');
    SELECT (CASE
              WHEN empty_char IS NULL THEN 'CHAR(1) is null'
            ELSE
             'CHAR(1) is not null'
            END) "empty_char"
    FROM test1;

    empty_char
    -------------------
    CHAR(1) is null
    Regards

    Albert Richter
1 2 3 26 Previous Next