>>>>> "Michael" == Michael Peppler <[EMAIL PROTECTED]> writes:
Michael> There was a fairly long thread in comp.databases.sybase on
Michael> this issue. Joe Celko weighed in with this comment:
Found it.
http://www.deja.com/[ST_rn=ps]/threadmsg_ct.xp?AN=714120405&fmt=text
Michael> 4) Attempting to put an empty string into a VARCHAR(n) column
Michael> will produce a single blanks. Attempting to put a string
Michael> that is too long into a VARCHAR(n) column, will raise an
Michael> exception.
Ok, so this addresses the storage concerns. (Although I agree with
the viewpoint of one of the other participants in that thread:
'', ' ', and NULL are all distinct values in my head, and this scheme
doesn't allow this to be gracefully encoded.)
One of his other points actually fixes my complaint with Oracle:
| 1) A NULL is a NULL and not an empty string. They behave differently
| in all the operators, as for example:
|
| '' || 'a' = 'a'
| NULL || 'a' = NULL
This is the problem I was having. More specifically, I had two tables
that I was joining, and Informix would join them since
'' = ''
would be true. When we went to Oracle, however, we discovered that
this expression was treated as
NULL = NULL
which came up false, and broke the join.
Michael> (The thread is "Empty String Converted to NULL" started on
Michael> 1/4/2001 in case you're interested.)
It was rather interesting reading.
Michael> So it seems that in this case Oracle is not ANSI compliant
Michael> (if I understood what you quoted correctly :-)
I would agree that current Oracle (or, at least, 8.1.6) certainly
seems to break Joe's point (1), and it probably breaks (4) (since
empty strings are stored as NULLs in nullable columns, and raises an
exception otherwise).
I see at least two different views here:
1. Logical View. Nulls are different from empty strings, and both of
those are different from a string consisting of exactly one blank.
This matches the Perl view, where undef isn't the same as '' which
isn't the same as ' '.
2. Physical View. It make sense to store NULL varchar fields as
zero-length strings, and the arguments against modifying on-disk DB
formats to accomodate some mechanism to distinguish zero-length
strings from true NULLs is a valid one (even if I, personally,
would prefer to have an unambiguous representation for each of
these three values).
There's even a third view:
3. How does a given DB's SQL parser handle ''?
It's been my experience that it's the SQL parser that actually
translates '' into NULLs. This means that, if Oracle ever fixes
its issues w.r.t. Joe's (1) above, that the SQL parser will have to
change its behavior as well.
For instance:
SQL> SELECT 'Hi!' FROM DUAL WHERE '' = '';
no rows selected
SQL> SELECT 'Hi!' FROM DUAL WHERE '' IS NULL;
'HI
---
Hi!
Uhg.
Anyway. This is getting off-topic for dbi-users. It's been
interesting, either way, tho. Thanks for the leads!
t.
