Re: [Q] Porting SQL to Oracle

(This is andrewst appearing in a different guise due to problems with DBFormums)

Billy Verreynne <vslabs_at_onwe.co.za> wrote in message news:<bc95hp$4dj$1_at_ctb-nnrp2.saix.net>...
> > Logically, '' is not the same as NULL, any more than 0 is the same as
> > NULL for a NUMBER column. '' is a value, NULL is not.
>
> Hey, you're not allowed to wear your pointy ears to the next Trekkie
> Convention. Not when you make illogical statements like that.
>
> Define what value is in between the quotes for ''.

It is the empty string. It is the character analogue of the number 0.  It is a string of 0 characters.

> There is nothing in there. What is nothing in DB-speak? A null. Or a
> manager. But seeing that we deal here with SQL, management is not a factor.

So NULL and 0 are equivalent values for a NUMBER column, are they?

> > Oracle has had to violate some of the rules for NULLs to accommodate
> > this confusion, e.g. 'ABC'||NULL returns 'ABC' when it should return
> > NULL (but 'ABC'||'' should return 'ABC', as it does).
>
> Why should 'ABC'||NULL return null? Unless you a Ferengi dealing with the
> 66th Law of Acquisition (the one that deals with making money disappear),
> it makes no sense.
>
> You are not multiplying 'ABC' with NULL. You are not dividing it. You are
> saying : "ADD NOTHING TO THE STRING 'ABC'"
>
> And that is _exactly_ what Oracle is doing. It adds _nothing_ to the string
> and give you the string back, with nothing added to it.

(sigh) So for numbers, 123+NULL = 123 does it? According to your definition of NULL it should. But it doesn't does it? Because NULL means "value unknown".
SQL says:

123+NULL = 123+[some unknown value] = [some unknown value] = NULL

To be consistent then it should also say:

'ABC'||NULL = 'ABC'||[some unknown value] = [some unknown value] = NULL I believe this is what some other DBMSs do (SQL Server maybe, where the original questioner started from?)

> > I suggest it is you that have been confused by Oracle's conflation of
> > '' and NULL!
>
> As for your thinking that '' is not the same as a NULL. That is plain
> _wrong_.

Oh no I'm not!!! etc. etc.

> If it is not NULL, then you are implying that there is a value in between
> those quotes.

Yes, an empty string of 0 characters.

> So if there is.. please show it to me. Provide me with proof that there is
> indeed some value in an empty string depicted as a ''.

I am not suggesting that there is any value in '' other than an empty string. I don't think you and I have any disagreement whatsoever about what is really in '', and what should happen if you concatenate '' with 'ABC'.

But we probably disagree over how many characters '' contains. I say it contains 0 characters, you presumably say it contains NULL characters like Oracle says it does:

SQL> select length('') from dual;

LENGTH('')

You like that? You find that useful and sensible? Why is it not 0?

You want a formal proof? I'm no C J Date, but how about this for a starting point (perhaps someone with more formal training can improve):

Axiom: LENGTH(s1||s2) = LENGTH(s1)+LENGTH(s2)

Let s1='ABC', s2=''

=> LENGTH('ABC'||'') = LENGTH('ABC')+LENGTH('') => LENGTH('ABC') = LENGTH('ABC')+LENGTH('') -- Since 'ABC'||'' = 'ABC'

=> 3 = 3+LENGTH('') => LENGTH('') = 0 -- contrary to what Oracle says

=> '' has a property, length=0

If NULL = '' then it follows that NULL is a VALUE with a property length=0 also.

But NULL is not a value, it marks the ABSENCE of a value. Contradiction!

=> NULL != '' QED (maybe)

But I can see you are a lost cause. Long ago you read the Book of Oracle, and it said that '' and NULL were the Same Thing, Now and Forever. You are therefore blinded to the logical inconsistencies and traps that follow from such a false equality (equality for heaven's sake!!! NULL = '' is "true", i.e. there exists something that is equal to NULL!!!)

And just for the record, I am actually an Oracle lover myself - I'm not writing all this because I want to do Oracle down. It is just one glaring fault in Oracle that, unlike you, I am not too blind to see. Received on Thu Jun 12 2003 - 08:49:11 CDT