Weird Oracle behaviour

bonkey

OK...this one is bizarre, and Im almost hoping someone will tell me its some form of bug, and not just Oracle being massively braindead.

I have a table with a column which may contain a varchar2(3) value, or which may be null. When non-null, it may have an empty string in there, a space-padded string, or an actual value. Empty string, space-filled strings and nulls are all to be treated the same in certain cases....one of which Im working on at the mo.

(Yes, this is horrid design, but its a customers DB so I dont have much say in this matter.....)

I want a count of the nulls, empty strings and space-filled strings combined, so I tried :

select count(*) from t_ema320 where rtrim(nvl(c_ic_kat, '')) = ''

This should replace NULLs with empty strings, and will also rtrim the space-padded fields. But it gives me the wrong answer - it produces a count of 0!

After some digging around, I discovered that

select count(*) from t_ema320 where nvl(c_ic_kat, '') = ''

Also produced a count of 0, even though I knew there were fields which had a c_ic_kat value of null.

What did work was :

select count(*) from t_ema320 where rtrim(nvl(c_ic_kat, '')) IS NULL
or (using the shorter query)
select count(*) from t_ema320 where nvl(c_ic_kat, '') IS NULL

Now, call me a bit thick, but empty strings are *very* different to NULL values, and NVL is supposed to remove NULL values, replacing with the non-null value specified. So why the hell wont it replace NULLs with empty strings?????

I have the query working, but I just find the oddness a bit disconcerting. Any explanations?

jc

stackboundary

Bonkey correct me if I'm wrong, but an 'empty' string is a block of memory that is supposed to contain characters.

Thus an empty string is a block of memory designated as type character, but containing no data a NULL area of memory, an area of memory that contains no data, thus the only difference between the two is the abstraction of what that area of memory is by the language you are attempting to describe that area of memory with... if that makes sense. In other words, until that area of memory has 'something' assigned to it, it is essentially null is it not? Therefore NULL and empty string are equivilent with only the datatype abstraction being what seperates them, but as datatypes are nullable in *sql there is no logical way to differenciate between NULL values is there? You would have to describe a NULL in terms of the type of data that is nulled elsewise... if you catch my drift.

For an empty string we need to say '\0' - now we can say for sure that this area of memory is an empty 'string' right? Before this in terms of memory it is simply a NULL area of memory, if that makes sense?

Thus should empty strings not be assigned '\0' as opposed to ''.

bonkey

You are confusing ASCII NULL characters with database NULL fields, which are entirely different things.

In C (and other languages), a string is stored in memory, starting at some predefined position, and continuing until an "end of string" marker is found. C uses an ASCII 0 (or ASCII NULL) for this.

Hence, all strings are null-terminated, and a string which contains "only a null" is an empty string. Only the string doesnt contain the null. The string contains everything which comes before the null. Thus, a null in position 0 indicates that there is nothing in the string.

In a database, a NULL presents a missing, unspecified, or unknown quantity. This is different to a string which has a specific value. Even a string which contains nothing has a specific value - a vlaue of nothing.

Example :

I ask for your middle initial(s).

If you say that it is "K", then it is stored (in the database) as a string of a single character

If it is "JK", then it is stored as a string of 2 characters.

If you say "I have no middle initials", then it is stored as a string of 0 characters.

I you say "sod off, thats none of your business", then it is stored as a NULL.

Oracle treats these latter two cases as being the same thing. They clearly are not the same thing. Imagine deciding that for numeric fields, 0 was equivalent to an unknown value. How stupid would that be?

I have since found that this is a known "bug" in Oracle. However, it is due to the fact that the ANSI 92 SQL standard does not allow for empty strings, for some bizarre reason. I suppose that Oracle will claim this is the reason for them being broken - standards compliance.

jc