in reply to "undef" is not NULL and what to do about it

This is one of the reasons why we - in ALLL our database types - disallow NULL values. All fields should have (default) values.

Also try to think about what will become of data when exported as CSV, which most modern databases support. CSV does not know the difference between 1,,2 and 1,"",2 (by default), so inserting exported data somewhere else might alter your data.

This is also one of the major reasons why I thoroughly hate Oracle. If I insert an empty string into a varchar field in Oracle, it helpfully inserts a NULL instead. HATE HATE HATE. Also see here.

I for one do not see perl's undef being wrong here, but the inconsistency in databases. OTOH I could see value in a new constant really being NULL.


Enjoy, Have FUN! H.Merijn

Replies are listed 'Best First'.
Re^2: "undef" is not NULL and what to do about it
by nikosv (Deacon) on Feb 20, 2013 at 20:07 UTC
    ++ for the link!

    This is one of the reasons why we - in ALLL our database types - disallow NULL values. All fields should have (default) values

    can't avoid the nulls produced by the outer joins though,i.e a left one

    If I insert an empty string into a varchar field in Oracle, it helpfully inserts a NULL instead

    I am not familiar with Oracle;how does it behave when when you put a not null constraint on the column?

    catering for nulls is such an important issue that for example C# introduced Nullable Types into the core language

[reply]
Re^2: "undef" is not NULL and what to do about it
by Rhandom (Curate) on Feb 21, 2013 at 15:47 UTC

    For a brief time 10 years ago I though like you do in this post. However after working with foreign keys and multi-key unique keys in well managed schemas, nulls have become a very welcome tool for helping to model relations via database constraints that could not be done without nulls.

    I share in your lack of enthusiasm for various oracle behaviors.

    Excluding NULL or undef values under a well intentioned blanket statement is fine, but understand you are removing valuable tools from yourself - cutting down on logical variations sure - but you are removing tools from your toolbox. Just because you haven't yet discovered how they are useful to other people doesn't make them less useful.

    my @a=qw(random brilliant braindead); print $a[rand(@a)];
[reply]
Re^2: "undef" is not NULL and what to do about it
by morgon (Priest) on Feb 20, 2013 at 23:15 UTC
    All fields should have (default) values.
    So how do you represent unknown values? By using a default "UNKNOWN"?

    Then I might just as well use a NULL...

    If I insert an empty string into a varchar field in Oracle, it helpfully inserts a NULL instead.
    VARCHAR-fields do in theory exist in Oracle but nobody uses them.

    You are talking about VARCHAR2-fields and for them an empty string IS a NULL. There is no difference at all. If you use that data-type you must be aware of this.

[reply]
      an empty string IS a NULL. There is no difference at all.

      I think the point is you can not select the data using, e.g.WHERE colname = ''.

      So how do you represent unknown values? By using a default "UNKNOWN"?

      Sometimes you know what the value is, and it's blank. There's just too many ways to look at it.

      .
[reply]
[d/l]

      "unknown" values are represented - in our case - by a value that falls outside the legal range. e.g. a for numeric values defined to be 0 or greater, we use -1 to be the "undefined" value and -2 to be the "known to be wrong or invalid" value. For strings that decision is taken on each case, some already have a value for "known to be unknown": . (not our choice, it is a law in that database) others might be a single space.

      varchar2 is Oracle-only, yet another reason to loathe Oracle. Indeed we use varchar2 instead of varchar, but they suck nevertheless.

      I just don't understand how you can say that an empty string equals to NULL. That is only true in the rotten Oracle world.

      FWIW I use a lot of database types (Oracle, Unify, MySQL, PostgreSQL, MariaDB, SQLite, CSV, TSV, MonetDB) and NONE is perfect. Oracle is not the only database with drawbacks.

      The is more btw, how to represent (in a database)

      • Known, valid ("normal" state in databases)
      • Known to be unknown (I checked it, but the value cannot be set)
      • Known to be absent/unavailable
      • Unknown ("NULL" in databases)
      • Known to be illegal (cannot use the illegal value, like date of birth 23770245)

      Enjoy, Have FUN! H.Merijn
      pmsig
[reply]
[d/l]
[select]
Re^2: "undef" is not NULL and what to do about it
by herveus (Prior) on Feb 25, 2013 at 20:26 UTC
    Howdy!

    That behaviour in Oracle is hateful and deviates from the ANSI standard, last I checked.

    yours,
    Michael
[reply]

In Section Meditations