This discussion is archived
1 20 21 22 23 24 26 385 Replies Latest reply: Aug 20, 2007 5:26 AM by Alessandro Rossi Go to original post
• ###### 315. Re: Treatment of zero-length strings as NULLs?
Currently Being Moderated
No, you are wrong. For me, from my point of view, this thread is finished.
Don't ask and I will not be responding.
Looks like you were wrong again.
No Scott, that's NOT how math works.
Not on planet Albert anyway.
That's how a half-breed intellectual works.
And a dash of casual racism, nice. Keep it up you are really impressing people here.
• ###### 316. Re: Treatment of zero-length strings as NULLs?
Currently Being Moderated
We all know that

(x - x) = 0

is always true.

We will disprove this by considering the following expression:

x - x

If x is NULL then we obtain
```       x  -  x

(unknown) - (unknown)

(unknown)```
CONCLUSION: since x is unknown, (x - x) is also unknown.

I'd say that (x - x) is always zero regardless of whether x is KNOWN or UNKNOWN.
• ###### 317. Re: Treatment of zero-length strings as NULLs?
Currently Being Moderated
I'd say that (x - x) is always zero regardless of whether x is KNOWN or UNKNOWN
Suppose x is ∞, Would you say infinity(∞) - ∞ is zero?
• ###### 318. Re: Treatment of zero-length strings as NULLs?
Currently Being Moderated
CONCLUSION: since x is unknown, (x - x) is also unknown.
Yes.
I'd say that (x - x) is always zero regardless of whether x is KNOWN or UNKNOWN.
I am assuming here you are using NULL and UNKNOWN interchangeably and that they are equivalent. So if you say

(x - x) = 0

and when x = NULL that

(NULL - NULL) = 0

Then you haven't you just proved that NULL = NULL?
• ###### 319. Re: Treatment of zero-length strings as NULLs?
Currently Being Moderated
I'd say that (x - x) is always zero regardless of whether x is KNOWN or UNKNOWN
Suppose x is ∞, Would you say infinity(∞) - ∞ is zero?
Saying "x is infinity" is wrong. We can say then

LIMIT (x - x) = 0
x -> ∞
• ###### 320. Re: Treatment of zero-length strings as NULLs?
Currently Being Moderated
Well, you can argue with this guy.
http://www.netscape.com/viewstory/2007/01/07/math-proof-infinity-subtracted-from-infinity-does-not-equal-to-zero/?url=http%3A%2F%2Fwww.philforhumanity.com%2FInfinity_Minus_Infinity.html++&frame=true
• ###### 321. Re: Treatment of zero-length strings as NULLs?
Currently Being Moderated
Well, you can argue with this guy.
http://www.netscape.com/viewstory/2007/01/07/math-proof-infinity-subtracted-from-infinity-does-not-equal-to-zero/?url=http%3A%2F%2Fwww.philforhumanity.com%2FInfinity_Minus_Infinity.html++&frame=true
I've just done it. Guy is OK. Here is his conclusion:

"Woops! It is impossible for infinity subtracted from infinity to be equal to one and zero. Using
this type of math, we can get infinity minus infinity to equal any real number. Therefore, infinity
subtracted from infinity is undefined."

Hence we should write the equation as I've already shown:

LIMIT (x - x) = 0
x-> oo
• ###### 322. Re: Treatment of zero-length strings as NULLs?
Currently Being Moderated
Nearly right. It's rather the case that when x is null we have

x - x
null - null
null

Hence it is not the case that x-x=0 if x may be null. Similarly, the law of the excluded middle does not hold for values that may be null -- apart from the operators IS NULL and IS NOT NULL. From this we conclude that the law of the excluded middle doesn't contribute anything meaningful to a discussion of the implementation details of NULL in one RDBMS or another.
• ###### 323. Re: Treatment of zero-length strings as NULLs?
Currently Being Moderated
> I'd say that (x - x) is always zero regardless of whether x is KNOWN or UNKNOWN.

And that is your Failure To Grok The Meaning Of Null.

You are treating NULL as a value. A value you can assign to a variable and a value that you can use for mathematical operation.

WRONG!

Null describes the STATE of the variable. Not a value!

It describes whether or not that variables has a known state (VAR IS NULL) or an unknown state (VAR IS NOT NULL).

Again, you claim that your arguments are more equal than others. When the counter-argument is used that in this case then (Infinity - Inifity) should also then equal zero, you claim it is not applicable.

Bull.

Does Infinity describe a value? OF COURSE NOT! As in that case we will have the flawed concept of (Infinity+1). Infinity describes a state. Not a value.

Just like NULL.

For the same reasons that (Infinity - Infinity) != 0, so too (NULL - NULL) != 0.

And if you cannot grok that, you will simply just "never get it" when it comes to nulls.
• ###### 324. Re: Treatment of zero-length strings as NULLs?
Currently Being Moderated
Does Infinity describe a value? OF COURSE NOT! As in
that case we will have the flawed concept of
(Infinity+1). Infinity describes a state. Not a
value.

Just like NULL.

For the same reasons that (Infinity - Infinity) !=
0
, so too (NULL - NULL) != 0.
And if you cannot grok that, you will simply just
"never get it" when it comes to nulls.
Absolutely spot on Billy. I think Albert doesn't understand the state of his own mind let alone the state of data.

Albert,
What is domain of attribute (column) "AGE" in an employees table?
I'd say 18-65 ages. If a scalar is UNKNOWN, is it "totally unknown" or
just "partially unknown"? I'd say partially, since we exactly know that
250 years old man cannot be an employee in our company.
The problem with your explanation here is that you are mixing database level concepts and business level concepts. Just because you create a column on your database that is called age, this will simply be a numeric datatype. The database has no concept of what "age" is and doesn't know that 250 would be an unreasonable value. The domain of reasonable values is something that is determined by business logic that is applied at a level above the database level. As far as the database is concerned if a value isn't entered for the age then it is just "unknown", not "partially unknown" or "totally unknown", just plain old "unknown". You obviously have some understanding of programming concepts but obviously a total lack of understanding or confusion when it comes to systems design concepts.
• ###### 325. Re: Treatment of zero-length strings as NULLs?
Currently Being Moderated
Null describes the STATE of the variable. Not a value!
Just a bit of irony here:
Consequently Oracle should then not allow statements as
```INSERT INTO t
VALUES (NULL)```
```INSERT INTO t
STATE (NULL)```
;)
• ###### 326. Re: Treatment of zero-length strings as NULLs?
Currently Being Moderated
> Just a bit of irony here:

Good point as that shows how confusing it can be.

Oracle is correct ito "purity" to force one to code if var IS null instead of if var = null.

However.. it is a lot easier to code the latter. A lot simpler for designing predicates too as NVL functions and OR conditions will no longer be needed.

But then, if the "less pure" method is used then programmers tend to think of NULL as just another value - expecting it to behave like a value when doing standard data type operations on it, like addition, subtraction, concatenation and so on.

Even, so the confusion remains for some.

BTW, Oracle also has a 3rd state when it comes to (NULL) and (NOT NULL). Simply not for scalar data types like a string. A locator (Oracle speak for a pointer) can be initialised and point to an unitialised (null) CLOB, BLOB or BFILE. In this case the locator is "empty" and not null. And once can check a locator's state for "isNull" and "isEmpty".

This also illustrates quite clearly the technical reason for why a zero length string is null and not something else. A malloc is a malloc. I.e. the string var exist in memory - the only state it can be is have-value and have-no-value. There is no state in-between.

A string is a scalar. Not a pointer that references a scalar.
• ###### 327. Re: Treatment of zero-length strings as NULLs?
Currently Being Moderated
In summary:

When using sql oracle treats '' the same as NULL.

Other databases don't do this.

Some people think oracle is correct, others think it is wrong, most of us don't care and are simply enjoying the bun fight.

Also PL/SQL handles '' slightly differently, some people thing this is a crime against humanity, some think it is odd but as its documented thats ok, most of us just work with what we have and get over it.

There is some disagrement over the exact meaning of NULL and something called the excluded middle, but it really isn't that important for most people as we just want to get paid.

If you are enjoying this discussion you may like these gems from the archives as well.

The code/identifier class database Oracle apparently should be a mind reading database and just work when you rename columns and tables.

How to build a Concrete Elephant
SIMPLE Database Design Problem !

A simple query what hint should I use? where is the FAST=TRUE switch?
SQL tunning
• ###### 328. Re: Treatment of zero-length strings as NULLs?
Currently Being Moderated
I'd say that (x - x) is always zero regardless of
whether x is KNOWN or UNKNOWN.
You might say that, but you would be wrong.

I think the repeated theme here is that you are incapable of seeing your assumed or acquired fallacious beliefs or opinions as anything other than universal facts, in the face of compelling argument, or even logical proof.

Furthermore you are now changing your argument, probably I expect because you cannot overcome those opposed to your view. Personally, it's my belief that when you start a new argument you should do so in a new topic thread. However I'm open to be persuaded otherwise.

However, this is slightly a shame, because you are not wrong fundamentally that NULLs necessarily imply inconsistent behaviour.

You are wrong that Oracle is alone in being inconsistent, or that SQL Server is some how more correct in it's own brand of inconsistency.

You are also mistaken that others here can't see that some logical inconsistency is implied. It is merely that we have learnt where the inconsistencies are and are careful to avoid them.

I also believe your attitude towards others who are taking the time to discuss this matter with you is disgracefully disrespectful, and that you should modify this behaviour.
• ###### 329. Re: Treatment of zero-length strings as NULLs?
Currently Being Moderated
Not mention IS DANGLING.
1 20 21 22 23 24 26