Re: "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

Comment on Re: "undef" is not NULL and what to do about it Select or Download Code

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]
Re^3: "undef" is not NULL and what to do about it by runrig (Abbot) on Feb 21, 2013 at 00:06 UTC
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]
Re^3: "undef" is not NULL and what to do about it by Tux (Canon) on Feb 21, 2013 at 06:58 UTC
"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]


No such thing as a small change
	PerlMonks