1 8 9 10 11 12 385 Replies Latest reply: Aug 20, 2007 7:26 AM by Alessandro Rossi Go to original post
• ###### 135. Re: Treatment of zero-length strings as NULLs?
> The point is: empty-string is NOT NULL, just as 0 (zero) is NOT NULL

I've heard and read some smelly stuff, but this statement just plain reeks.

How on earth can an empty string - a string without a value - be compared to a number with the numeric value zero!?

Zero is a legitimate numeric value!!

On the other hand though, there is no character value for an empty string. You actually admitted as much, saying "empty-string has not its own ascii representation code".

Thus an empty string has no value!!

And now you want to claim it is just like a number data type variable that has the numeric value zero!??

Birdseed.
• ###### 136. Re: Treatment of zero-length strings as NULLs?
The point is: empty-string is NOT NULL, just as 0 (zero) is NOT NULL
I've heard and read some smelly stuff, but this statement just plain reeks.

How on earth can an empty string - a string without a value - be compared to a number with
the numeric value zero!?
Cannot be compared directly, eg ('' = 0), ('' <> 0 ) etc., since they are different datatypes, but they are nice equivalents in two different nice worlds (alphanumeric and numeric), real twins.
Zero is a legitimate numeric value!!
So is zero-length-string in alphanumeric world. But what means "legitimate"?
On the other hand though, there is no character value for an empty string. You actually admitted as
much, saying "empty-string has not its own ascii representation code".
Right. But it has nothing to do with your problem. If you really consider THIS to be the problem, never mind - unicode has enough room to do it, as opposed to 8-bit ascii, though the problem is solvable (and is already solved) even in ascii.
Thus an empty string has no value!!
It HAS a value '', it is a known value, but you can't see it.
And now you want to claim it is just like a number data type variable that has the numeric value
zero!??
Right, JUST LIKE. They are not IDENTICAL or EQUAL, but they are equivalents.

Billy and William,
------------------

with all due respect I've got a feeling that one of possible causes of your confusion could be the fact that all we see on our physical screens are only ascii characters, internally represented by its ascii code. When we talk about numbers, dates, strings etc, we just talk about them in the context of operations which can be done on them (mathematical op, dates op, string op etc), but they are always represented on the screen as characters. Please follow me. I'll try to be as simple as possible.

What is this?

125

Is this number 125, or sequence of characters: '1'||'2'||'5' = '125'? If I take it as the number, I presume that mathematical operations are possible with this number, eg (125 * 5) = 625. If I take it as a string of three characters, no math operations are possible, only string operations, eg LENGTH('125') = 3.

In the first case of being the number, what is the LENGTH(125)? Is the LENGTH(125) less than LENGTH(2147483648)? Is the LENGTH(125) unknown or inapplicable or maybe...? Or the length, as an attribute of numbers, is totally senseless, meaninless, pointless ? Even saying "the length of number doesn't exist" is sounding as obtuse tautology. So, what is an "empty number"? Obviously, the number whose "length" is zero. Give me that number.

The same applies to the DATE datatypes. What is this?

12-DEC-2005

Is this the date 12-DEC-2005, or the string whose length is 11 characters: '12-DEC-2005'? If I consider it to be the date, what is the LENGTH(12-DEC-2005)? May I say 24 hours? Sounds pretty idiotic. Maybe NULL? But in 3VL, NULL means the length of the date exists but we do not know it, since the Lord is not willing to tell us. So, it cannot even be NULL. What could then be an "empty date"? The date whose "length" is zero? Give me that date.

But if you ask me to give you a string whose length is zero, I'll give you: ''. Because strings are ordered sequences of dummy characters:
```'               Di*jks5%43@zccHY9K>}(A((99-+\$3#!2jshQ'
'              Di*jks5%43@zccHY9K>}(A((99-+\$3#!2jshQ'
'             Di*jks5%43@zccHY9K>}(A((99-+\$3#!2jshQ'
'            Di*jks5%43@zccHY9K>}(A((99-+\$3#!2jshQ'
'           Di*jks5%43@zccHY9K>}(A((99-+\$3#!2jshQ'
'          Di*jks5%43@zccHY9K>}(A((99-+\$3#!2jshQ'
'         Di*jks5%43@zccHY9K>}(A((99-+\$3#!2jshQ'
'        Di*jks5%43@zccHY9K>}(A((99-+\$3#!2jshQ'
'       Di*jks5%43@zccHY9K>}(A((99-+\$3#!2jshQ'
'      Di*jks5%43@zccHY9K>}(A((99-+\$3#!2jshQ'
'     Di*jks5%43@zccHY9K>}(A((99-+\$3#!2jshQ'
'    Di*jks5%43@zccHY9K>}(A((99-+\$3#!2jshQ'
'   Di*jks5%43@zccHY9K>}(A((99-+\$3#!2jshQ'
'  Di*jks5%43@zccHY9K>}(A((99-+\$3#!2jshQ'
' Di*jks5%43@zccHY9K>}(A((99-+\$3#!2jshQ'
'Di*jks5%43@zccHY9K>}(A((99-+\$3#!2jshQ'```
Look at this set of sequences and don't tell me that you cannot "see" the empty-string or more better: the zero-length-string.

Regards

Albert
• ###### 137. Re: Treatment of zero-length strings as NULLs?
Albert, forget for a moment about the internal representation of data types, the ASCII set, UNICODE, etc.

I'm not arguing that there is a technical difference inside the computer between a NULL variable and a zero-length variable. As I mentioned, there are even one or two more such states when dealing with COM objects in Win32. I'm familiar with these technical issues.

That is not what I (nor William I think) have a problem with.

The problem I have is from a relational design point of view that says (and this has been stated more than once in this forum):
A NULL attribute in a tuple has a different meaning (business interpretation) than the same attribute with a zero length.

I state that this is a flawed approach. As flawed as removing an attribute from the tuple and making that attribute part of the name of the relation (I used the example of removing YEAR from an INVOICE relation and changing the relation to INVOICES_2006)

Yes, one needs to understand (as a developer) how to treat both (zero length strings and NULLs) in the language and RDBMS product used. Yes, complain and moan about it if you want, but realise that is how the product work and you have no choice but to use as designed. As with any other feature (or lack thereof) in any computer product or language.

As for Oracle treating zero length strings as NULL. For me it make sense as a zero length string has no value, just as a NULL string has no value. Again, from an abstract point of view - not from the difference of how this is represented internally.

Arguing that "no value" (empty string) is different than an "unknown value" (null)... well that seems to me more of a philosophical argument. Something that has very little relevance when dealing with business requirements as no value or null value means the same thing - the data is not there.

And if the reason for that data not being there needs to be known, then that data about the data has to be carried as attribute(s) - not as some kind of philosophical interpretation of The True Meaning of An Empty Value And A Null Value where an empty value is a value that cannot be seen.
• ###### 138. Re: Treatment of zero-length strings as NULLs?
> Billy and William,
with all due respect I've got a feeling that one of possible causes of your confusion could be the fact that
all we see on our physical screens are only ascii characters, internally represented by its ascii code.

Albert, I am not confused at all and I am not thinking about the issue in terms of ASCII characters, although now you mention it I do wonder how Sybase etc write zero-length strings out to CSV files while keeping them separate from null values. I mentioned empty dates earlier and as I said then I would not expect them to have a zero length, but instead the database would have a way of recording that a known nonvalue was present as distinct from null. I am sure Oracle could come up with something. What I have been saying if you bothered to read my posts is that you clearly manage well enough in the case of dates (for example in the case of the GUI screen painter you mentioned), so why is it such a big deal in the case of one datatype and not another?

As I said earlier I can see some logic in the zero-length string approach, even though I don't personally like the extra complication. Codd seems to have seen a distinction (going by the extended Rule 3 rather than the Wikipedia version), though I'd be interested in hearing his reasoning. I can even just about go along with it being the equivalent of numeric zero. There is no point quoting examples of random strings to me or (Alessandro) explaining how nulls behave. The only aspect of this I don't understand is why you think it's such a black and white issue with you being obviously right, Oracle obviously wrong, and everyone who doesn't agree with you misguided and confused.
• ###### 139. Re: Treatment of zero-length strings as NULLs?
> I cannot conceive of the notion of an "empty
date", or "empty number" I don't believe such a thing
is possible.

I gave
geID=1620879#1620879]an example earlier,
although I can see why you might miss it in this

The requirement originally given for an empty string
was the case where someone has no phone number and we
want to record this information as a fact (there is
definitely no phone number, not merely a gap where we
wish we knew it). So, why should it be so hard to
conceive of the case where there is definitely no
date, as distinct from an unknown date? Alright,
"empty" and "zero-length" don't make sense for a date
so there would have to be some other internal magic
value, but the point is that if it's so vital to be
able to record "there is no phone number here", why
is nobody demanding a way to record "there is no date
here"? Because we can manage without, as we can with
strings.
Give us the equivalent in date or numer datatype of null in oracle then!!!

I tell you if '' is the equivalent of null in the varchar2 datataype it is wrong because '' is a string.

I don't know if don't want to understand the following but it seem that the '' string in oracle is the null value.
```SQL> select 1
2  from dual
3  where sysdate = '';

Nessuna riga selezionata

SQL>
SQL> select 1
2  from dual
3  where 1 = '';

Nessuna riga selezionata

SQL>
SQL>
SQL> select 1
2  from dual
3  where sysdate = 0;
where sysdate = 0
*
ERRORE alla riga 3:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

SQL>
SQL>
SQL> select 1
2  from dual
3  where sysdate = 'not null string';
where sysdate = 'not null string'
*
ERRORE alla riga 3:
ORA-01858: a non-numeric character was found where a numeric was expected

SQL>
SQL> select 1
2  from dual
3  where 1 = 'not null string';
where 1 = 'not null string'
*
ERRORE alla riga 3:
ORA-01722: invalid number

SQL>```
Bye Alessandro

Message was edited by:
Alessandro Rossi
• ###### 140. Re: Treatment of zero-length strings as NULLs?
You illustrated that nicely by calling is a "third logic value". There is nothing like that.

I was of course inaccurate to say "a third logic value" since logic values have a type: BOOLEAN with only the domain TRUE of FALSE.

We cannot now suddenly assign a 3rd mysterious value and say something is neither TRUE or FALSE.

Exactly. This is my point, use of NULL leads to the need for a third logic value {true,false,undetermined} (or 4,5,6 logic values,...unknown,unset,unentered,not projected,not applicable,...)

Which can't be resolved using predicate logic. Hence the difficulties ensuing in SQL.
• ###### 141. Re: Treatment of zero-length strings as NULLs?
1. closed under the given operation (i.e.string1 || string2 is a string)
2. has an identity
3. associativity: (a||b)||c = a||(b||c)

...and the identity of string algebra is?
• ###### 142. Re: Treatment of zero-length strings as NULLs?
Codd seems to have seen a distinction

Codd changed his mind as far as I understand it. Atleast twice.

He firstly eschewed the null, then believed it was necessary, but then we needed two kinds of null.

I believe the anti null brigade then went crazy on prove the many various kind of null implied as a kind of reductio ad absurdum.

I think shortly after that he died.

I'm not saying there was a causal relation there, but who know?
• ###### 143. Re: Treatment of zero-length strings as NULLs?
Alessandro, you're doing it again.

I know that Oracle does not currently support an empty date. The point I have been trying unsuccessfully to make is that you don't seem to be demanding one, but you are demanding an empty string. I was wondering why you need this "specified with no value" facility so badly for one datatype but not another. Surely if you cope in the case of dates you can cope just as well in the case of strings.

> I don't know if don't want to understand the following but it seem that the '' string in oracle is the null value.
Correct, that is the documented behaviour. It is just one of the logicallly possible ways of doing things but it is the way Oracle chose. It brings a mix of advantages and disadvantages, as would any alternative.
• ###### 144. Re: Treatment of zero-length strings as NULLs?
> Exactly. This is my point, use of NULL leads to the
need for a third logic value
{true,false,undetermined} (or 4,5,6 logic
values,...unknown,unset,unentered,not projected,not
applicable,...)

Granted. But in attempt to lessen this type of confusion that (for example) a BOOLEAN can now have 3 values, TRUE, FALSE and UNKNOWN (and thus ny reference to Schrödinger's Cat that is neither alive or dead), I rather not describe NULL as a value. I see it instead as variable state.

In a non-null state a variable can have a value. In a null state, it has no value.

And this is also why I view a zero terminated string in the same light. It in in a value-less state.

> Which can't be resolved using predicate logic. Hence
the difficulties ensuing in SQL.

Agree with that. I think the bottom line for many on both sides of the fence in this debate is that we want consistency in the end.
• ###### 145. Re: Treatment of zero-length strings as NULLs?
> Agree with that. I think the bottom line for many on both sides of the fence in this debate is that we want consistency in the end.

Well, a reasonable degree of consistency perhaps. It might be consistent for the concatenation operator to return null if one of its arguments is null, for DECODE null comparisons not to work and for unique multi-column indexes to allow more than one occurrence of ('X', NULL), but I for one am reasonably happy with the way it is.
• ###### 146. Re: Treatment of zero-length strings as NULLs?
I rather not describe NULL as a value. I see it instead as variable state.

I agree with this. I don't think this is an unusual defintion.

This doesn't imply that an "empty string" is the same thing as a NULL. "Empty string" is not a variable state, it is a value. A string. It is Empty. This is it's defined value.

Two empty strings whatever the representation can be compared, and we can say "yes, they are the same thing: two null terminated strings, two structures that are present and are emtpy, two pascal strings of length zero."

Two NULLs are not equal since undetermined = undetermined is undetermined.
• ###### 147. Re: Treatment of zero-length strings as NULLs?
Or how about thing like this don't happen:
```SQL> create table empty ( A number );

Table created.

SQL> select 1 result from dual where ( select * from empty ) is null;

RESULT
----------------------
1

SQL>--or

SQL>create type point as object ( x number, y number );

Type created.

SQL> select 1 from dual where point(null,null) is NULL;

no rows selected```
• ###### 148. Re: Treatment of zero-length strings as NULLs?
To every "hot" contributor in this thread:

One same thing is possible to see from different points of view and "the thing"
looks every time different. Some people saying one same thing is possible to see
from 12 different aspects! And every of this "points of view" (aspects) is right, all of
them are OK.

In this sense, the truth is relative thing as I already said in one of previous threads.

"THE Problem" appear only when someone say: My point of view is right one, and others are wrong.

Cheers!
• ###### 149. Re: Treatment of zero-length strings as NULLs?
> This doesn't imply that an "empty string" is the same thing as a NULL. "Empty
string" is not a variable state, it is a value. A string. It is Empty. This is
it's defined value.

Hmm.. that is like saying that a glass without water (does not contain a value) is not empty (null) as it is full of air.

That argument does not hold water (pun intended ;-) ) with me.

We'll have to just differ on this point. An empty string is valueless and equivalent to null to me as it contains no data that I can write code for to process.
1 8 9 10 11 12