My favorite WTF with Oracle is that an empty string is equivalent to NULL. Seriously. This means that something like SELECT * FROM foo WHERE bar='' will never return results, because it's equivalent to say WHERE bar=NULL, which as Alex has already pointed out always returns false.

I remember working at a company where I was the junior programmer and had spent about 3 days with Oracle before figuring this out. Every time I told one of the senior programmers about it, they would pester me with "Are you sure? I don't think so..." until I demonstrated it to them. And they always had the same reaction: "well, that must have changed because it didn't used to behave that way." Guess again.

Talk about lurking bugs waiting to bite you...

myself (and the guys under me, since I demanded it) would always just wrap string values like that into a function that would return "=..." or "is null"

in order to get around all that kind of hassle, as well as doubling up quotes, etc.

That's why it took me a few minutes to recall why I never had that problem :)

This is without a doubt the stupidest oracle WTF!

Should be in the top 10 on this site.

I'm not so sure this actually is a bug, I think Oracle actually intends that NULL is equivalent to an empty string. I think remember that on one of the early releases of 8i (yes, we still use it, even if everyone else has moved on), there was a bug where NVL( '', 'Foo' ) didn't produce the same results as NVL( NULL, 'Foo' ) - they fixed it in a minor patch.

In a way SQL in Oracle is loosely typed, so that saying WHERE USER_ID = 123 and WHERE USER_ID = '123' produce the same results - likewise with dates. So in that sense I can understand why a loosely typed empty string is null - it contains no data and has no type. Sounds like null to me.

Can anyone tell me when having '' in a db field is useful?

dhromed wrote:

Can anyone tell me when having '' in a db field is useful?

When the column is a [var]char(n) and you have set it to be not null in its definition.  Assuming you mean having an empty string as opposed to two apostrophes.

sinistral wrote:

dhromed wrote: Can anyone tell me when having '' in a db field is useful? When the column is a [var]char(n) and you have set it to be not null in its definition.  Assuming you mean having an empty string as opposed to two apostrophes.

Which just leads to the question, if you've defined a column to be a not-null varchar, why would you want to populate it with an empty string? If a column's not-null, it must have a value.

The more I think about it, this isn't a WTF from Oracle, it's actually a rather sensible philosophy for databases - it *prevents* exactly the thing you've described. Why bother having not-null defintions for columns if you can just populate them with empty strings? It makes not-null restrictions on varchar columns completely pointless. By making null equivalent to an empty string, they close this possible loophole before some idiot exploits it.

So long live the null-ish empty string!

sinistral wrote:

dhromed wrote: Can anyone tell me when having '' in a db field is useful? When the column is a [var]char(n) and you have set it to be not null in its definition.  Assuming you mean having an empty string as opposed to two apostrophes.

Very good.

So the empty string is then useful because it allows for a fake sort of pseudo-null, handily circumventing the NOT NULL requirement?

So again, when is that empty string useful? When would you say "This blasted null! I wish I had an empty string!"?

Wouldn't empty string fuck up your null checks in your script if the field was emptied? Wouldn't a check against NULL have it come up as "filled", and then write out nothing at all? I can think of situations where you'd say "This blasted empty string! I wish I had a proper null!"

Why the hell am I putting this whole deal in question-format, even though the questions are roughly 40% rhetorical? I believe in the strict human equivalence of '' and NULL, and can't envision a system design where you'd want to distinguish between the two, but I'm 60% open-minded towards the existence of such a design, even though the technology services the human needs, nothing equals nothing every time and twice on Sundays.

dhromed wrote:

Very good. So the empty string is then useful because it allows for a fake sort of pseudo-null, handily circumventing the NOT NULL requirement? So again, when is that empty string useful? When would you say "This blasted null! I wish I had an empty string!"? Wouldn't empty string fuck up your null checks in your script if the field was emptied? Wouldn't a check against NULL have it come up as "filled", and then write out nothing at all? I can think of situations where you'd say "This blasted empty string! I wish I had a proper null!" Why the hell am I putting this whole deal in question-format, even though the questions are roughly 40% rhetorical? I believe in the strict human equivalence of '' and NULL, and can't envision a system design where you'd want to distinguish between the two, but I'm 60% open-minded towards the existence of such a design, even though the technology services the human needs, nothing equals nothing every time and twice on Sundays.

I sincerely wish I could come up with a good reason to support both.  My primary reason for arguing against the equivlence of NULL and '' is based on my programming background.  In C or C++, if you use a char* foo, having foo == NULL is not the same as foo == ''.  That is, NULL means that the pointer is pointing to no valid memory, but an empty string has the string terminator in it.  Yes, this is perhaps a poor reason, since database design is not the same as programming in a specific language.

I also recall issues some of our server developers encountered with the Oracle libraries not doing the right thing for NULL varhchars vs. empty ( == '') varchars.  But that may be an incomplete and incorrect memory.

sinistral wrote:

I sincerely wish I could come up with a good reason to support both.

my first post!

I always thought that the reason for this is that '' is definate, while null is unknown. For example, take an employees table with a manager_id foreign key. If the manager_id is '' that would mean the person has no manager. While manager_id is NULL means you don't know the manager_id. I think this is how its done in SQLServer, and its always bugged me.

mlathe wrote:

sinistral wrote: I sincerely wish I could come up with a good reason to support both. my first post! I always thought that the reason for this is that '' is definate, while null is unknown. For example, take an employees table with a manager_id foreign key. If the manager_id is '' that would mean the person has no manager. While manager_id is NULL means you don't know the manager_id. I think this is how its done in SQLServer, and its always bugged me.

dunno where you got that from.

A NULL in that foreign key field means that the employee has no manager. The moment there is a value in there (i.e. not null), then that value must also exist in the manager table or you will get a sql error.

mlathe wrote:

I always thought that the reason for this is that '' is definate, while null is unknown. For example, take an employees table with a manager_id foreign key. If the manager_id is '' that would mean the person has no manager. While manager_id is NULL means you don't know the manager_id. I think this is how its done in SQLServer, and its always bugged me.

That would be a good situation for the '' -> NULL behaviour, seeing as how '' in this case is absolutely meaningless and is closer to data pollution than anything else. A person either has a manager, in which case the field has a string value > 0, or not, in which case the field SHOULD be null.

If the manager is unknown you could opt to create a value "[unknown]" or simply choose NULL, but not an empty string. Empty strings mean nothing.

murphyman wrote:

sinistral wrote:  dhromed wrote: Can anyone tell me when having '' in a db field is useful? When the column is a [var]char(n) and you have set it to be not null in its definition.  Assuming you mean having an empty string as opposed to two apostrophes. Which just leads to the question, if you've defined a column to be a not-null varchar, why would you want to populate it with an empty string? If a column's not-null, it must have a value. The more I think about it, this isn't a WTF from Oracle, it's actually a rather sensible philosophy for databases - it *prevents* exactly the thing you've described. Why bother having not-null defintions for columns if you can just populate them with empty strings? It makes not-null restrictions on varchar columns completely pointless. By making null equivalent to an empty string, they close this possible loophole before some idiot exploits it. So long live the null-ish empty string!

In the beginning, there was a problem domain, and it was defined, and it was known, and it was good. And the database analysts made whole a logical design, and a physical design, and they brought them before the systems analysts, and the business analysts, and the users, and after much talk and rework, the designs were blessed.

And the database analysts went forth, and caused to be made tables a hundredfold, and to each of them columns a hundredfold, each well-typed and well-constrained according to its needs, and myriad procedures.

And the developers went forth and designed user interfaces and batch processes and reports. When all was done, the data was loaded, and found to be good and well-behaved. And there was much feasting upon donuts and pizza, and drinking of beer.

And all was well for a time, and the data flourished and multipled, and the developers made mods and enhancements, and all were happy.

Then to the company came a new monthly data feed, one which knew not of the well-defined problem domain, nor of the blessed designs, nor of the works that had been made before it. And the developers looked and they saw to their horror that data was missing which should be there.

And they went to their managers and said, "This cannot be, for this data which is missing must be laid into a column designated 'not null' as is proper for the design; we must reject this data, and send it back to whence it came, that the makers of the data may cleanse it, and make it whole and right". But to this the managers replied "Nay, we cannot, for it is within the contract. You must abide by this data, and find a way".

And they went to the database analysts and said, "We must change the column, that it may accomodate nulls". But the database analysts averred, saying "We cannot change the design, for much has been built upon it; this is a path that is not open to you."

And the developers were anguished, for the users were demanding the data, but there was no way to provide it to them. And a dark cloud descending upon the land. And many ideas were generated by the developers, but none were found workable.

But then one developer among them said, "What if we just import the column as an empty string and then clean it up? They users can review the data through custom reports or the UI, we can do some batch updating based on what they find, and anything left over they can just update through the UI." And they presented this to the users, who agreed, and all was well again.

 

If the SQL engine equates an empty string to a null, then not-null columns will be populated with "UNKNOWN".

Data is dirty.

Good one, rbriem :)

Here's my main objection to the empty string being turned into a NULL, and the reason why I registered here.

Strings have lengths. Lengths can be 5, or 500, or 1. Or 0. Saying that a String of length 0 is the same as a NULL value is like saying about a numeric field, "You can have any natural number in this field. Oh, wait... except 0." It's the difference between the _positive_ and the _non-negative_ integers. There *is* a difference between saying, "I have a string, and its length is 0" and saying, "I have a string, and its length is... wait, no, actually I don't have a string."

from a functional point of view, there is no difference in having a string with no characters in it and having no string at all (at least i think).

I cannot see use for an empty string where null fails to do the same trick.

ofcourse it is strange when you deliberatly put in an empty string the db changes it to null. I want everything back from the database the way i put it in. when the db changes "" to null and leaves an inputted null to null, there is no way of returning the value i send to the db.

A contrived example:

Suppose you have a database of people with GIVEN_NAME, MIDDLE_NAME, FAMILY_NAME fields. And suppose there is a "John Smith" who has no middle name. Further suppose that there is a "George Shaw" whose middle name is Bernard, but this information is not widely known and was not entered into the database.

I propose that it is reasonable to have the empty string be John Smith's MIDDLE_NAME, and to have NULL be listed for George Shaw's MIDDLE_NAME. This enables queries to distinguish "Tell me all of the people without middle names" and "Tell me all the people whose middle names we do not know."

Another contrived example:
    Consider the difference between an empty password string (i.e. just hit return when logging in) and an unknown password string (perhaps because it has not yet been set by the user).

I am sure there are other less contrived examples. This is a WTF and a bug from Oracle, as evinced by the fact that they are alone among SQL implementers to choose these semantics.

My coworker was just having problems with this today. Her code is in PL/SQL and looks like thus:

declare;
l_tmp1 VARCHAR2(10);
l_tmp2 VARCHAR2(10);
begin
  if(something) then
     l_tmp1 := null;
     l_tmp2 := 'foobar';
  else
     l_tmp1 := 'foo';
     l_tmp2 := 'bar';
  end if;

  select * into whatever from table where
        field1 = l_tmp1
    And field2 = l_tmp2;
end;

I've cut a lot out to make this smaller, but basically the if statements are longer and have more than one value which may be null. Because she can't check for something = null, the bottom select statement has to be much much larger to account for possible null values (or she has to use dynamic sql). Kind of a pain in the ass.

steven_h wrote:

A contrived example: Suppose you have a database of people with GIVEN_NAME, MIDDLE_NAME, FAMILY_NAME fields. And suppose there is a "John Smith" who has no middle name. Further suppose that there is a "George Shaw" whose middle name is Bernard, but this information is not widely known and was not entered into the database. I propose that it is reasonable to have the empty string be John Smith's MIDDLE_NAME, and to have NULL be listed for George Shaw's MIDDLE_NAME. This enables queries to distinguish "Tell me all of the people without middle names" and "Tell me all the people whose middle names we do not know." Another contrived example:     Consider the difference between an empty password string (i.e. just hit return when logging in) and an unknown password string (perhaps because it has not yet been set by the user). I am sure there are other less contrived examples. This is a WTF and a bug from Oracle, as evinced by the fact that they are alone among SQL implementers to choose these semantics.

In your example(s), what is the difference, to us, between NULL and 'unknown'? For all intents and purposes, aren't they the same?

What is the informational value of having a query that can distinguish between the unknown middle name and the absent middle name? Is it relevant that the system knows a middle name is unknown? Is it relevant, even, that the people who use the system can distinguish between no middle name and an unknown middle name? Why would someone need a list of all the positive unknowns that differs from a list of the NULLs or a list of everything?

As far as the contrivedness goes; I'm looking for real situations. Otherwise it's not relevant.

<BLOCKQUOTE><table width="85%"><tr><td class="txt4"><img src="/Themes/default/images/icon-quote.gif">&nbsp;<strong>dhromed wrote:</strong></td></tr><tr><td class="quoteTable"><table width="100%"><tr><td width="100%" valign="top" class="txt4">In your example(s), what is the difference, to us, between NULL and 'unknown'? For all intents and purposes, aren't they the same?</td></tr></table></td></tr></table></BLOCKQUOTE>
I suppose that's a valid question, but, without meaning to offend anyone, it's not a question a programmer would ask. To a programmer (say in Java or in Smalltalk), a string (i.e. a varchar type field in DB terms) is a collection of characters. You can ask a string, say, (a) whether it begins with the character 'e', or (b) for its reverse string, or (c) for its length. If these examples don't mean much to you, I'm sure you can think of better examples, the point is, you can expect a string to understand these sort of 'messages'.

The string "example", for example, would return the boolean 'true' to (a), the string "elpmaxe" to (b), and the integer '7' to (c).

The empty string, i.e. "", would return: (a) 'false', (b) "", and (c) '0'.

However, if your string's value is 'null' (i.e. it hasn't been instantiated yet, or its value has been set to 'null' explicityly), then sending message (a), (b) or (c) to it would result in an error each time.

Why? Because 'null' is different from an empty string. It's not a string at all, in Java it's not even an object; it's a placeholder for "this is nothing, no particular type of object, null, void...".

To take it one step further, there are also collections of other types of objects, let's say a collection of, oh, strings. If I have three objects, one empty string (i.e. an empty collection of characters), one empty collection of strings, and a 'null', then, even though the first two are both empty, I know what type of object they are, and therefore what types of messages they should be able to understand. With the third, I don't know anything yet - it's a "nothing".

Empty strings are as different from 'null' when it comes to strings as 0 is different from 'null' when it comes to integers. Imagine if Oracle stored 'null' for every integer type field you wrote a 0 into, and you wanted to add up all the field values, you'd get an error, because 3 + 0 = 0, but 3 + 'null' isn't defined...

Ok, done raving now, hope I made my point ;)

Amos wrote:

dhromed wrote: In your example(s), what is the difference, to us, between NULL and 'unknown'? For all intents and purposes, aren't they the same? I suppose that's a valid question, but, without meaning to offend anyone, it's not a question a programmer would ask. To a programmer (say in Java or in Smalltalk), a string (i.e. a varchar type field in DB terms) is a collection of characters. You can ask a string, say, (a) whether it begins with the character 'e', or (b) for its reverse string, or (c) for its length. If these examples don't mean much to you, I'm sure you can think of better examples, the point is, you can expect a string to understand these sort of 'messages'. The string "example", for example, would return the boolean 'true' to (a), the string "elpmaxe" to (b), and the integer '7' to (c). The empty string, i.e. "", would return: (a) 'false', (b) "", and (c) '0'. However, if your string's value is 'null' (i.e. it hasn't been instantiated yet, or its value has been set to 'null' explicityly), then sending message (a), (b) or (c) to it would result in an error each time. Why? Because 'null' is different from an empty string. It's not a string at all, in Java it's not even an object; it's a placeholder for "this is nothing, no particular type of object, null, void...". To take it one step further, there are also collections of other types of objects, let's say a collection of, oh, strings. If I have three objects, one empty string (i.e. an empty collection of characters), one empty collection of strings, and a 'null', then, even though the first two are both empty, I know what type of object they are, and therefore what types of messages they should be able to understand. With the third, I don't know anything yet - it's a "nothing". Empty strings are as different from 'null' when it comes to strings as 0 is different from 'null' when it comes to integers. Imagine if Oracle stored 'null' for every integer type field you wrote a 0 into, and you wanted to add up all the field values, you'd get an error, because 3 + 0 = 0, but 3 + 'null' isn't defined... Ok, done raving now, hope I made my point ;)

We were talking about databases, not languages. Yes, I think everybody knows what a null reference is in Java or C#.

Amos wrote:

I suppose that's a valid question, but, without meaning to offend anyone, it's not a question a programmer would ask. To a programmer (say in Java or in Smalltalk), a string (i.e. a varchar type field in DB terms) is a collection of characters. You can ask a string, say, (a) whether it begins with the character 'e', or (b) for its reverse string, or (c) for its length. If these examples don't mean much to you, I'm sure you can think of better examples, the point is, you can expect a string to understand these sort of 'messages'. The string "example", for example, would return the boolean 'true' to (a), the string "elpmaxe" to (b), and the integer '7' to (c). The empty string, i.e. "", would return: (a) 'false', (b) "", and (c) '0'. However, if your string's value is 'null' (i.e. it hasn't been instantiated yet, or its value has been set to 'null' explicityly), then sending message (a), (b) or (c) to it would result in an error each time. Why? Because 'null' is different from an empty string. It's not a string at all, in Java it's not even an object; it's a placeholder for "this is nothing, no particular type of object, null, void...". To take it one step further, there are also collections of other types of objects, let's say a collection of, oh, strings. If I have three objects, one empty string (i.e. an empty collection of characters), one empty collection of strings, and a 'null', then, even though the first two are both empty, I know what type of object they are, and therefore what types of messages they should be able to understand. With the third, I don't know anything yet - it's a "nothing". Empty strings are as different from 'null' when it comes to strings as 0 is different from 'null' when it comes to integers. Imagine if Oracle stored 'null' for every integer type field you wrote a 0 into, and you wanted to add up all the field values, you'd get an error, because 3 + 0 = 0, but 3 + 'null' isn't defined... Ok, done raving now, hope I made my point ;)

I see your point, but I don't agree that ('' vs NULL) is the same as (0 vs NULL). 0 is an existing int. It is a value; a piece of information. I disagree with several languages that it should return false in boolean context.

You are right that if the database interface consistently returns '' when '' is in the DB, you can simply expect a string, and use its methods without needing error handling of the try-catch variety.

But whatever you want to do with the string data will probably yield odd or absent results if applied to an empty string. If you want to prevent that, you'll have to build in a check to see if the string is empty, and then handle it accordingly. But in doing so, you are right back to the Practical Equivalence of '' and NULL, and the DB might as well have replaced '' with NULL.

If the DB converts '' to NULL upon input, you are required to check and see if you can use string methods on the data. If you don't, you might receive an error -- unless of course, the field is NOT NULL, in which case the matter is moot.

The question I think, is whether it's necessary in your application to handle absent information, or let the script do it 'blindly'. If you absolutely crave your script to do it blindly, enforce a NOT NULL on the field. Otherwise, you need to check, and that renders '' and NULL completely equivalent.

<BLOCKQUOTE><table width="85%"><tr><td class="txt4"><img src="/Themes/default/images/icon-quote.gif">&nbsp;<strong>Quinnum wrote:</strong></td></tr><tr><td class="quoteTable"><table width="100%"><tr><td width="100%" valign="top" class="txt4"><BLOCKQUOTE><table width="85%"><tr><td class="txt4"><img src="/Themes/default/images/icon-quote.gif">&nbsp;<strong>Amos wrote:</strong></td></tr><tr><td class="quoteTable"><table width="100%"><tr><td width="100%" valign="top" class="txt4"><BLOCKQUOTE><table width="85%"><tr><td class="txt4"><img src="/Themes/default/images/icon-quote.gif">&nbsp;<strong>dhromed wrote:</strong></td></tr><tr><td class="quoteTable"><table width="100%"><tr><td width="100%" valign="top" class="txt4">In your example(s), what is
the difference, to us, between NULL and 'unknown'? For all intents and
purposes, aren't they the same?</td></tr></table></td></tr></table></BLOCKQUOTE>
I suppose that's a valid question, but, without meaning to offend
anyone, it's not a question a programmer would ask. To a programmer
(say in Java or in Smalltalk), a string (i.e. a varchar type field in
DB terms) is a collection of characters. You can ask a string, say, (a)
whether it begins with the character 'e', or (b) for its reverse
string, or (c) for its length. If these examples don't mean much to
you, I'm sure you can think of better examples, the point is, you can
expect a string to understand these sort of 'messages'.
The string "example", for example, would return the boolean 'true' to
(a), the string "elpmaxe" to (b), and the integer '7' to (c).
The empty string, i.e. "", would return: (a) 'false', (b) "", and (c)
'0'.
However, if your string's value is 'null' (i.e. it hasn't been
instantiated yet, or its value has been set to 'null' explicityly),
then sending message (a), (b) or (c) to it would result in an error
each time.
Why? Because 'null' is different from an empty string. It's not a
string at all, in Java it's not even an object; it's a placeholder for
"this is nothing, no particular type of object, null, void...".
To take it one step further, there are also collections of other types
of objects, let's say a collection of, oh, strings. If I have three
objects, one empty string (i.e. an empty collection of characters), one
empty collection of strings, and a 'null', then, even though the first
two are both empty, I know what type of object they are, and therefore
what types of messages they should be able to understand. With the
third, I don't know anything yet - it's a "nothing".
Empty strings are as different from 'null' when it comes to strings as
0 is different from 'null' when it comes to integers. Imagine if Oracle
stored 'null' for every integer type field you wrote a 0 into, and you
wanted to add up all the field values, you'd get an error, because 3 +
0 = 0, but 3 + 'null' isn't defined...
Ok, done raving now, hope I made my point ;)</td></tr></table></td></tr></table></BLOCKQUOTE><br>
<br>
We were talking about databases, not languages. Yes, I think everybody knows what a null reference is in Java or C#.<br>
<br></td></tr></table></td></tr></table></BLOCKQUOTE>
I guess we're coming from completely different directions, which is why I said "to a programmer". I really don't know, does anyone use a database just by itself for anything? For me, there always has to be a "language" or some sort of application that uses the database - it's a tool, not an application in itself, isn't it? (Feel free to correct me if I'm wrong, but that's not really the point of this discussion...)

The actual point, I think, and why I bothered posting and why this topic is being discussed on a "WTF" site, is that we're not being given a choice at all here. Let's call the two sides of the argument the "wtf-side" and the "but-that's-how-it-should-be-side". The db is, in this case, not giving us an option, it's saying, "An empty string is the same as a null value, deal with it, end of story".

I dare say that the wtf-side isn't insisting that this should be changed to allow empty strings, deal with it, end of story. I hope I'm speaking for most of the wtf-side by stating that we'd be happy with having the choice (I mean, Oracle gives you a gazillion choices in everything else, what's one more?), we're saying that we can see cases where an empty string could be wanted. We could live with having to change the default setting every time we set up a new database.

The but-that's-how-it-should-be-side is arguing that they can't imagine such a case is likely, and it seems to me (please correct me if I'm wrong) that they're not only arguing that the default should stay the same, they're also arguing that it's the way it should be and that we shouldn't even have an option.

The first time I learned about this problem, I thought, "oh, so that's why I get these NullPointerExceptions... I'll just have to set it to allow empty strings." I tried and failed, and asked my DBA (you may have figured out by now that I'm no DBA...) where I could find this setting. It was the response that "there IS no such setting" that made me say, "what the...".

Couldn't you, dhromed, cede the point that even though you wouldn't use it, such a setting could be useful to others...? Instead of arguing about whether or not we can imagine a scenario where the setting would be needed, let's argue about "what could it hurt to allow such a setting"...

Disclaimer: This post made sense at the time of writing.

GRRR... what's with the blockquote error? I didn't even try to edit it this time... and apparently I can't edit my post 5 seconds after I posted it...

Amos wrote:

GRRR... what's with the blockquote error? I didn't even try to edit it this time... and apparently I can't edit my post 5 seconds after I posted it...

You click 'quote' and DON'T DO ANYTHING ELSE. No sudden movements or sleight of hands. The forum is faster than you.


As for the argument,

Maybe we should look at it from a different practical viewpoint: currently, MSSQL and Oracle (and..?) convert '' to NULL.
What are the issues that come from this?
What are the issues that come from allowing empty strings in a DB?

I haven't experienced any issues coming from ('' to NULL), but I can imagine that keeping the '' would render the "is null" and "nvl()" clauses in your SQL useless, even though there is no data, so your query will return nothing for that field.

Any real-life issues that stem from ('' to NULL)?

dhromed wrote:

Amos wrote: GRRR... what's with the blockquote error? I didn't even try to edit it this time... and apparently I can't edit my post 5 seconds after I posted it... You click 'quote' and DON'T DO ANYTHING ELSE. No sudden movements or sleight of hands. The forum is faster than you. As for the argument, Maybe we should look at it from a different practical viewpoint: currently, MSSQL and Oracle (and..?) convert '' to NULL. What are the issues that come from this? What are the issues that come from allowing empty strings in a DB? I haven't experienced any issues coming from ('' to NULL), but I can imagine that keeping the '' would render the "is null" and "nvl()" clauses in your SQL useless, even though there is no data, so your query will return nothing for that field. Any real-life issues that stem from ('' to NULL)?

MSSQL doesn't convert "" to NULL, certainly not by default anyway. And I've never looked into turning that 'feature' on (if that is even possible) in that case, because it shits me no end.

NULL is bad when the columns it's allowed in are going to be used in the WHERE clause of a Transact Sql Server statement on MS Sql 6.5, 7.0 and 2000. Use of the IS NULL keywords in the WHERE clause makes that portion of the query non-sargable, which means that portion of the query cannot use an index. Basically, badly thought out allowing of NULL values in a column can screw knacker all your hard thougt out performance enhancing indexing, so be careful.

MySQL does this, empty string == NULL. I have mixed feelings about this, but I really try to write code that doesn't depend on this behavior so I can port webapps to different backends.

Beware of those side effects:

length('') returns NULL (not 0)

rpad('', 5) returns NULL which equals '' (not '     ')

(rpad normally pads the string to the given length)

ammoQ wrote:

Beware of those side effects: length('') returns NULL (not 0) rpad('', 5) returns NULL which equals '' (not ' ') (rpad normally pads the string to the given length)

Given the debatable correctness of the behaviour [NULL == ''], the first seems acceptable.

The second is entirely intolerable.

dhromed wrote:

ammoQ wrote: Beware of those side effects: length('') returns NULL (not 0) rpad('', 5) returns NULL which equals '' (not ' ') (rpad normally pads the string to the given length) Given the debatable correctness of the behaviour [NULL == ''], the first seems acceptable.

It's a trap anyway, catches me every now and then. And I do PL/SQL programming every day.

The second is entirely intolerable.

This is an even more deadly trap for me :-((
Our systems communicate with other systems by fixed format strings (a WTF itself, but it wasn't our choice), the rpad missbehaviour leads to "unexptected" results:

v_msg := rpad(bla, 2) || rpad(blubb, 3) || rpad (foo, 2) || rpad (bar, 4) || .... (~ 20 more fields);

If one of those fields is unexpectedly null, the rest of the message is shifted... BANG

Bumping a semi-ancient thread to throw some actual math at it.

Given the following:

1.  NULL represents a lack of a value.  It has no measurable properties except 'IS NULL'.

2.  Strings are composed of a sequence of symbols in an alphabet.  For strings in a database, A is usually the UNICODE alphabet or similar.

3.  Finite state machines can be used to determine if a string is a member of a given language.

4.  It's possible to create a finite state machine that accepts a language composed only of the empty string.  One such machine has two states (initial and rejected), is initially on 'initial', transitions to 'rejected' on any character from either state, and accepts strings if the final state is 'initial'.

5.  It's possible to create finite state machines that accept a language including the empty string.  (Left as an exercise.)

6.  It's trivially possible to create a finite state machine that accepts a language not containing the empty string by taking the machine from (4) and accepting strings only if the final state is 'rejected'.

Therefore:

The empty string has a measurable property (membership of languages defined by finite state machines).

Therefore:

The empty string is not NULL.

Angstrom wrote:

Bumping a semi-ancient thread to throw some actual math at it. Given the following: 1.  NULL represents a lack of a value.  It has no measurable properties except 'IS NULL'. 2.  Strings are composed of a sequence of symbols in an alphabet.  For strings in a database, A is usually the UNICODE alphabet or similar. 3.  Finite state machines can be used to determine if a string is a member of a given language. 4.  It's possible to create a finite state machine that accepts a language composed only of the empty string.  One such machine has two states (initial and rejected), is initially on 'initial', transitions to 'rejected' on any character from either state, and accepts strings if the final state is 'initial'. 5.  It's possible to create finite state machines that accept a language including the empty string.  (Left as an exercise.) 6.  It's trivially possible to create a finite state machine that accepts a language not containing the empty string by taking the machine from (4) and accepting strings only if the final state is 'rejected'. Therefore: The empty string has a measurable property (membership of languages defined by finite state machines). Therefore: The empty string is not NULL.

This is evident, from a technical perspective. Even without your Vulcan logic, one knows that a String has the overhead of being a string that just happens to be empty. And empty CD takes up more space than no CD at all. :)

The issue is humans. NULL (absence of data) and empty string (absence of data) are psychologically equivalent. An empty CD where data must be used, read, processed -- might as well be no CD at all.

When comparing two columns, either or both of which may be NULL, I use

WHERE ':'||columnA = ':'||columnB

It looks ridiculous, but it gets the job done.

Forgot to mention *Oracle*

hash1978 wrote:

When comparing two columns, either or both of which may be NULL, I use WHERE ':'||columnA = ':'||columnB It looks ridiculous, but it gets the job done.

This makes it impossible to use existing indexes on eighter column.

ammoQ wrote:

hash1978 wrote: When comparing two columns, either or both of which may be NULL, I use WHERE ':'||columnA = ':'||columnB It looks ridiculous, but it gets the job done. This makes it impossible to use existing indexes on eighter column.

Should be able to use a function based index.

The real-life issues are because All the Other DBMS Do It the Other Way. Queries become non-portable. Applications have been written with the semantics that the empty string has a specific meaning. Data integration to such apps or systems is complicated tremendously by the lack of an empty string value. Yes, the integrator-software could test for NULL and translate to empty-string consistently; and, vice-versa. But for systems which respond to data changes, this might resemble a change when there was none. 

It's like the command set for FTP: even if it isn't sensible or ideal, it is best for everyone that all systems do it the same way. 

MrWonderful:

The real-life issues are because All the Other DBMS Do It the Other Way. Queries become non-portable.

 

So? How about locking or clustering then? Oracle does it differently than most of the competitors, but who wants to downgrade?

MrWonderful:

Applications have been written with the semantics that the empty string has a specific meaning.

 

And that specific meaning would be?

 

MrWonderful:

 

Data integration to such apps or systems is complicated tremendously by the lack of an empty string value.

 

It could also be complicated because of a broken data model. 

MrWonderful:

Yes, the integrator-software could test for NULL and translate to empty-string consistently; and, vice-versa. But for systems which respond to data changes, this might resemble a change when there was none. 

It's like the command set for FTP: even if it isn't sensible or ideal, it is best for everyone that all systems do it the same way. 

I'm not sure what "systems which respond to data changes" should mean, but there's a recent OTN-thread on this topic, if you'll like to continue on this subject.
 

l.

ammoQ:

Beware of those side effects:

length('') returns NULL (not 0)

rpad('', 5) returns NULL which equals '' (not '     ')

(rpad normally pads the string to the given length)

 

Big Deal!

 select length(rpad(nvl('',' '),5)) from dual;

will give you 5 i.e. rpad(nvl('',' '),5) = '     '

Very very easily handled.

 

 

Jimbob:

ammoQ:

Beware of those side effects:

length('') returns NULL (not 0)

rpad('', 5) returns NULL which equals '' (not '     ')

(rpad normally pads the string to the given length)

 

Big Deal!

 select length(rpad(nvl('',' '),5)) from dual;

will give you 5 i.e. rpad(nvl('',' '),5) = '     '

Very very easily handled. 

Wow, really?

Unfortunately, I have to maintain code that looks like that:

v_msg := rpad(v_head.foo, 20)
                ||rpad(v_head.bar,10)
                ||rpad(v_head.blublu,5)
                ||rpad(v_head.blabla,30)
...
30 more lines like that
...
                ||rpad(v_head.nixda,10);

This means a lot of nvls just to make sure nothing goes wrong. Or I replace rpad with a sane user-defined function that does what it is expected to do.

 

I have used empty strings and NULL strings with non-equivalent meanings in a practical context before.  I develop content management systems in ASP.NET with Microsoft SQL Server.  Our client had an existing database external to the site I was developing.  They wanted to be able to show their existing (and volatile) data from their own database, unless they specifically overrode it in our database.  So, we replicate that table to our database nightly, and have another table for overrides.  The stored procedures will pull from our table, and for fields that are NULL, it will pull those from the replicated table.  A blank string means "this field is explicitly blank," where a NULL string means "no override exists, use the replicated value."

joe.edwards@imaginuity.com:

I have used empty strings and NULL strings with non-equivalent meanings in a practical context before.  I develop content management systems in ASP.NET with Microsoft SQL Server.  Our client had an existing database external to the site I was developing.  They wanted to be able to show their existing (and volatile) data from their own database, unless they specifically overrode it in our database.  So, we replicate that table to our database nightly, and have another table for overrides.  The stored procedures will pull from our table, and for fields that are NULL, it will pull those from the replicated table.  A blank string means "this field is explicitly blank," where a NULL string means "no override exists, use the replicated value."

 What about columns with other datatypes, like dates or numbers?

 
l.
 

"And don't tell me there isn't one bit of difference between null and space, because that's exactly how much difference there is."

 -Larry Wall 

lofwyr:

joe.edwards@imaginuity.com:

I have used empty strings and NULL strings with non-equivalent meanings in a practical context before.  I develop content management systems in ASP.NET with Microsoft SQL Server.  Our client had an existing database external to the site I was developing.  They wanted to be able to show their existing (and volatile) data from their own database, unless they specifically overrode it in our database.  So, we replicate that table to our database nightly, and have another table for overrides.  The stored procedures will pull from our table, and for fields that are NULL, it will pull those from the replicated table.  A blank string means "this field is explicitly blank," where a NULL string means "no override exists, use the replicated value."

 What about columns with other datatypes, like dates or numbers?

 
l.
 

Are you asking for practical examples of why you would want to differentiate between NULL and 0 or NULL and an empty date, such as 0000-00-00?

For numbers, a good example might be coordinates.  It's certainly possible for an object to exist at Lat: 0.000 and Long 0.000.  But what do you put if the location is unknown?  NULL, of course.

Another example for numbers might be test scores.  Students can earn a score between say, 0 and 10 on a pop quiz.  Students who didn't study get a 0, which is averaged into their final grade.  Students who didn't take the quiz (because they were legally absent, perhaps) get NULL, which cannot be averaged into their final grade.

For dates.... technically 0000-00-00 is a date that doesn't exist (there was no year zero), so it could function as "unknown date".  But then again, some databases are perfectly able to do calculations on partial dates such as 2007-02-00 (between January and February).  So in that sense, 0000-00-00 might not be a good idea, since if you do a search for records with [date field] before the year 500 AD, then 0000-00-00 will happily be returned.  If you instead set your unknown date to NULL, then your NULL date will never be returned until you explicity ask for unknown dates, e.g., [date] IS NULL.

0000-00-00 is not a date.  Databases shouldn't allow it as a column value for Date columns.  MySQL does (except in its newer, stricter mode), but MySQL is wrong here. '', on the other hand, is a string.

Pap:

Are you asking for practical examples of why you would want to differentiate between NULL and 0 or NULL and an empty date, such as 0000-00-00?

 

I was asking for an empty representation in other datatypes. So far, all I've seen is using some value as empty placeholder and I'd ask myself why this can't be done with strings. Regarding your date example, you are aware that you can not put invalid date values inside a data type, right? so in your example, you'd have to use 0001-01-01 (or any other value). Again, there's no "empty" value for this data type.

l. 

Angstrom:

0000-00-00 is not a date.  Databases shouldn't allow it as a column value for Date columns.  MySQL does (except in its newer, stricter mode), but MySQL is wrong here.

Overzealous date verification actually leads to rejecting valid dates. Try this on your favorite date-nazi database, I bet it will get it wrong too.

mysql> insert into t values ('1500-02-29');
ERROR 1292 (22007): Incorrect date value: '1500-02-29' for column 'd' at row 1

1500 was a leap year, under the Julian calendar of the time. The rules for years divisible by 100 and 400 were introduced with the Gregorian calendar in 1582. Also, to make up for the extra leap days that had been accumulating under the Julian calendar, the 10 days from 1582-10-05 through 1582-10-14 were skipped, but MySQL fails to reject them.

Representational issue.  My poison of choice explicitly says that it's using the gregorian system for date input and output, so your suggested string is not, in fact, a valid (gregorian) date to it.  That doesn't mean it can't represent that day -- just that you can't use the julian calendar date (which happens to look a lot *like* the gregorian calendar date) to enter it.  FWIW postgres accepts gregorian dates between 1582-10-05 and 1582-10-14 (inclusive -- I tested all 10), calling them Tuesday (10-05) and Thursday (10-14) respectively.

Nothing is ever as easy as it looks, I think is the moral here.  Humans' date notations are all inflected by lunar calendars, farming cycles, an obsession with whole units, and all sorts of complex, illogical cruft.  Timestamps are not fundamentally all that hard unless you want to discuss relativity; for practical purposes you can treat a timestamp as identifying a specific point on a continuous number line.  We persist in using notations and units to describe that point that almost, but not quite, line up with reality.  Those *days* weren't skipped, for instance, but the coordinate system used to describe them has a discontinuity such that the days 1582-10-05 and 1582-10-14 are adjacent.

MJD, man.  It's the only way to fly.
 

First - great analogy with the blank CD versus no CD :-) 

A string is a string, even if it has no length; and a zero-length string is simply a string with no characters.

NULL, however, can mean more than an absence of data. It can mean that there isn't any applicable data (SSN for a foreign student) or the data is missing (SSN wasn't inserted/updated).

The fact that Oracle treats an empty string as NULL is tragic.

Many have argued over the differences and whether or not NULL should even be supported by relational database systems. I think it has its place, if used wisely, and not haphazardly.

FYI: http://sqlfight.com has a set of forus dedicated to controversial issues like this.

debettap:

A string is a string, even if it has no length; and a zero-length string is simply a string with no characters.

I'm still curious what the equivalent of a zero-length string in another datatype would be. If there isn't any, why bother with zero-length strings?

l.