1 17 18 19 20 21 385 Replies Latest reply on Aug 20, 2007 12:26 PM by Alessandro Rossi Go to original post
• 270. Re: Treatment of zero-length strings as NULLs?
What would you expect for the SUM to give you when
some values are NULL and what
if all values are NULL ?
The same. If you want to sum non-null values only
then
select sum(comm) from scott.emp ;
must be written as
```select sum(comm) from scott.emp where comm is
not null```
.
Aggregate functions are not part of relational theory either, so ditto the argument about arithmetic combination of attributes. The inconsistencies around behaviour are a consequence of their addition to SQL, and this is likely to have been what spawned the troublesome NULL.
• 271. Re: Treatment of zero-length strings as NULLs?
You cannot argue the behaviour of an opertor as
designed to be an inconsitency just because you think
the other way. When the operator is introduced, it is
documented to behave in a certain way (like all the
other operators do) and that is exactly what this
operatror is doing. it has nothing to do with
handling NULL in different ways at different times.
Excellent point. Arugment over the function of || operator is akin to the much used, totally incorrect, and I'm surprised not yet dragged up in this thread argument that DECODE() is inconsistent in it's treatment of null. Which goes along the lines that it evaluates (NULL = NULL) to TRUE.
• 272. Re: Treatment of zero-length strings as NULLs?
I'm surprised not yet dragged up in this thread argument that DECODE() is inconsistent in it's treatment of null.
More fodder for the takers? Thread is still young.

http://dictionary.reference.com/search?q=fodder
• 273. Re: Treatment of zero-length strings as NULLs?
Hmm. The "law of the excluded middle" says that for
a truth proposition P that

P V ~P
is always true. We will disprove this by
counter-example. Consider the following
proposition:

x = 4
f x is null then we obtain

(x=4) V ~(x=4)
(unknown) V ~(unknown)
(unknown) V (unknown)
(unknown)
Because unknown is not true the law of the excluded
middle does not hold. Now would you please stop with
the distractions. You're not exactly portraying
yourself well here.
Scott, why do you think you should be a bigger Pope than Pope himself? Folks here have their own heads. It's very easy to end up this discussion. If nobody responds, nobody will be "distracted". You don't expect me to be asking and responding myself, do you?

But if you insist, you are the only one who really distracts people here. Unless being a joke, your "disproof" could eventually be accepted only as a part of some topic in the "Intellectual Self-Abuse: The Complete Reference Guide". And that is a typical distraction.

Regards

Albert
• 274. Re: Treatment of zero-length strings as NULLs?
You asked for a proof, I gave you a proof. Oh, and this is simple logic -- hence you can't just call it a dis-proof but you are rather expected to point out a logical fallacy in one or more of the steps. That's how math works.
• 275. Re: Treatment of zero-length strings as NULLs?
You asked for a proof, I gave you a proof.
Ach so? Oh, sorry Scott. I've just forgotten it. Forgive me.

Cheers

Albert
• 276. Re: Treatment of zero-length strings as NULLs?
You asked for a proof, I gave you a proof.
Ach so? Oh, sorry Scott. I've just forgotten it.
Forgive me.

Cheers

Albert
Yes, in fact I believe you said you would "fly away over the moon from the forum" if one were produced...

I'm look out of my window here, and I have pretty good view of the moon.
• 277. Re: Treatment of zero-length strings as NULLs?
> > In addition you can't compare a mathematical "+" to
a string concatenation (||).

Hmm... To compare not. But to make equivalence
between them - YES.
They are totally different operators.
Absolutely right, since they operates on different
datatypes. But they are equivalent operators.

Very easy to disprove this.

Using the same argument, the operator IS and = are then also "equivalent operators".

They are not:
``````SQL> select case when NULL is NULL then 'TRUE' else 'FALSE' end as "NULL is NULL" from dual;

NULL is NULL
-----------------
TRUE

SQL> select case when NULL = NULL then 'TRUE' else 'FALSE' end as "NULL = NULL" from dual;

NULL = NULL
-----------------------
FALSE

SQL>  ``````
Clearly these two operators function different. One is a "state operator" and the other is a "value operator". (which makes sense as NULL is not a value)

Arguing that the concatenation operator is similar to the plus operator is therefore flawed.
• 278. Re: Treatment of zero-length strings as NULLs?
Ok so let me create a nice example on Ingres...

INGRES TERMINAL MONITOR Copyright 2003 Computer Associates Intl, Inc.
Ingres Microsoft Windows 32-bit Version II 2.6/0305 (int.w32/00) login
Ingres! Say, I haven't upgraded my old system since version 6.4, but does it still
distinguish between dates that are NULL and those that are empty strings? My
old Ingres/OpenROAD code is littered with if this_date = '' then this_date is NULL
endif; because someone foolishly touched the field on the form....
As far as I know, it still does the same, but to be honest I can't be bothered to test it and I don't have an OpenRoad or even ABF development environment to knock up a test screen for the "touching". I don't think they would have taken out such erm.... valuable(?) functioanlity. ;)

The only use of the Ingres database I have nowadays is to extract the data from the databases and process it in Oracle to generate stuff.
• 279. Re: Treatment of zero-length strings as NULLs?
1. I am not confused. Search in your heart, you know you are out of depth here.

2. Your straw man appeal to authority falls on educated ears my friend, thus your arrogant but false claims are wasted breath. Please refrain from outspoken stupidity. For fooling yourself is not consecutive with fooling those you seek as a colleague.

3. So is it the "international term" or the "academic" term? Wether you pretend to lofty height or refer by common name, principle is the same, and not hard to understand. A third is not given. False, or True. Evaluation of a predicate cannot yield information beyond this horizon in the bounds of the logic. There is nothing between. This is 2VL, and yet contrary to your initial claim that you require 4VL, not 3VL as already shown.

4. You don't even understand what you are talking about. You are now just making things up as you go along, in desperate hope that your foolish agrandisement impress.

5. No. Unknown does not have the domain (true,false). You are making a fool of yourself now. For your own dignity please stop. Anyone with any form of education will see you now as a poor crestfallen fellow.

6. This is the most complete load of nonsense I have had to digest in some time. For this I congratulate you. Perhaps you are bedfellows with in desperate self promoting circle with Joe Celko.
Just because you drop out of university because you cannot understand course, please do not try to take out your frustration of world with you on the others around.
It really is impossible to even begin to contradict this argument in a rational way because you stray so far from the bounds of rational debate.
You are fooling no one, and discredit any merit in your original post with this idiotic rambling.

T h e F l y i n g S p o o n ! ! ! Where are you?

Your 24 hours expired. I cannot see your disproof .

What is? You are not familiar with mathematics and logic? I understand.

I'm giving you 24 hours G R A T I S to make me F O O L !!!

This is an offer you cannot refuse.

( unless being a miserable coward and swindler )

Albert
• 280. Re: Treatment of zero-length strings as NULLs?
Very easy to disprove this.
Using the same argument, the operator IS and = are then also "equivalent operators".
ERROR!!! The error in proposition, Billy - 'using the same argument, the operator IS and = are then also "equivalent operators".'

It's not the same argument.

Operators "IS" and "=" are not equivalent operators. The "=" is exact operator. The "IS" is not exact operator. Furthermore, the "IS" is not operator at all. Its is a "compromis operator", a "describing operator" in the meaning "since we cannot state that NULL=NULL, we'll say NULL IS NULL". I cannot see the equivalence between them.

Albert
• 281. Re: Treatment of zero-length strings as NULLs?
> It's not the same argument.

Which is why this is just like Animal Farm.

In this forum discussion all scalar data types are equal, except for some who are more equal than others. So too now with arguments.

It is perfectly valid for you to state that || and + are "equivalent operators", and that you expect (consistent) output X.

But by the same token, using the same premise of equivalence comparison, it is not valid to state that IS and = are equivalent and to expect (consistent) output Y.

Despite that this is indeed equivalent in some languages as one can code "var = NULL" and this will behave exactly the same as "var is NULL".

Again, echoing exactly your argument that is based on the fact that in some languages the || is done using a +.

The equivalence argument of IS and = is flawed in Oracle. IS and = are not equivalent as I have shown. Despite that and the direct comparisons between your argument and the flawed IS and = argument, your argument is fine. More equal than the flawed argument.

Why am I not surprised...
• 282. Re: Treatment of zero-length strings as NULLs?
Operators "IS" and "=" are not equivalent operators. The "=" is exact operator.
The "IS" is not exact operator. Furthermore, the "IS" is not operator at all. Its is
a "compromis operator", a "describing operator" in the meaning "since we
cannot state that NULL=NULL, we'll say NULL IS NULL". I cannot see the
equivalence between them.
This is no more than a terms jugglery. What do you mean "is not operator at all" ?

The model specifies NULL as "unknown value" and allows to assign NULL to the
variable, and moreover, IS NULL returns TRUE or FALSE.
So it can be considered as comparison operator.

Rgds.
• 283. Re: Treatment of zero-length strings as NULLs?
It is perfectly valid for you to state that || and +
are "equivalent operators", and that you expect
(consistent) output X.
Maybe we are both confused with the term "equivalence". I consider that "to be equivalent" is not the same as "to be equal to" (operator "="). For that reason, a better word than "equivalence" might be "analogy".
Despite that this is indeed equivalent in some
languages as one can code "var = NULL" and
this will behave exactly the same as "var is
NULL
".
I know that. But keep in mind that in C language the "=" is the assignment operator, not the "equal to" operator that is the "==". Anyway, here we are talking about relational databases at the logical level (not physical, storage level).

>
Again, echoing exactly your argument that is based on
the fact that in some languages the || is done using
a +.
It doesn't matter. They are only signs (the character '+' and the string "||") and that fact doesn't make analogy between them. What makes anology is their meaning (semantics) in our heads.

Albert
• 284. Re: Treatment of zero-length strings as NULLs?
It doesn't matter. They are only signs (the
character '+' and the string "||") and that fact
doesn't make analogy between them. What makes anology
is their meaning (semantics) in our heads.
like "||" means 'take the string of character bytes on the left and append the string of character bytes on the right, whereas "+" means "carry out a bitwise addition of these binary values"

Sounds equivalent to me.....erm.... NOT.
1 17 18 19 20 21