Tim

><< Final point before I stop wasting your time - there's an interesting edge
>condition if all of the rows selected contain NULL. In this case it actually
>returns NULL when according to your definition above, and the manual I would
>have expected 0. >>
>
>Your expectations would be wrong. You can't sum unknown values and
>come up with zero, so the result of such an operation would be NULL, or
>unknown.

From your manual, which I presume reflects the standard

"Any time the numeric or interval expression is NULL, it is excluded from the sum calculation"

So what unknown values? The answer logically has to be zero. If you sum nothing you get nothing which mathematically is usually represented as 0.

Roy Lambert

Roy,

<< From your manual, which I presume reflects the standard

"Any time the numeric or interval expression is NULL, it is excluded from
the sum calculation" >>

Exactly, so if it doesn't sum anything, then it can't possibly come up with
a value of zero.

I really can't argue this much longer.  It is what it is, and I'm afraid
that I just can't explain it correctly so that you'll understand.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>"Any time the numeric or interval expression is NULL, it is excluded from
>the sum calculation" >>
>
>Exactly, so if it doesn't sum anything, then it can't possibly come up with
>a value of zero.
>
>I really can't argue this much longer.

You've stuck with it longer than I expected. I had hoped that some of the NULL zealots would contribute.

>It is what it is,

I know and you'll notice I haven't once suggested changing it

>and I'm afraid
>that I just can't explain it correctly so that you'll understand.

Tim, I don't think its you (or me) I think you're trying to explain something that is not susceptible to logic.

Roy Lambert

Roy,

> If you sum nothing you get nothing which mathematically is
> usually represented as 0.

If you "sum nothing", mathematically it isn't a sum.

--
Fernando Dias
[Team Elevate]

Fernando


Good someone else joining the discussion.

>If you "sum nothing", mathematically it isn't a sum.

In mathematics, an empty product, or nullary product, is the result of multiplying no numbers. Its numerical value is 1, the multiplicative identity, just as the empty sum the result of adding no numbers is zero, or the additive identity.

Roy Lambert

Roy,

> Good someone else joining the discussion.
Not that I'm particularly qualified to or that I see myself as a zealot, but
just to don't let it die as you wished

> In mathematics, an empty product, or nullary product, is the result of
> multiplying no numbers. Its numerical value is 1, the multiplicative
> identity, just as the empty sum the result of adding no numbers is zero,
> or the additive identity.
Ok, you have a point here.
However in this case we are not talking about addition but about a function
that by his definition has no known value when the argument is an empty set.

--
Fernando Dias
[Team Elevate]

Fernando


I could hear the squeal of the brakes as you changed direction there

I know SQLers talk about sets, but I don't think they, or the operators for them realistically stand up as mathematical sets. The SQL standard seems to have decided to override key and fundamental mathematical rules. Consider

Mathematics
zero multiplied by anything equals zero

SQL
NULL multiplied by anything equals NULL (although you can't strictly say equals)

However, it doesn't matter what you eventually fill the unknown value with the result is guaranteed to be zero so in the specific case of zero multiplied by NULL the answer is not NULL it is zero.

I can just imagine the discussion when setting the standard

Bert: I know lets have something which tells the database we don't have anything to put in yet - we'll call it NULL and it means we don't know what the value might someday be.

All: Great idea, but what happens if the programmer wants to add a number to this NULL thingy

Bert: Well if we don't know what NULL is and we add something to it we still don't know what it is so it can stay as NULL

Fred: Yeah, I can go for that.

John: Err, wait a minute, the FD will want to be able to add up his accounts. Are you saying that if one of the rows has one of these NULLs in it he won't get an answer?

Bert: That's the general idea.

John: Well if I take that back he won't sign my expense form.

Fred: Ooo err I hadn't thought of that.

Bert: OK well, what we'll do is allow him to add up his columns and we'll just ignore any NULLs that are there. What do you reckon?

John: Hmm, he should go for that.

Roy: But what happens if someone wants to add up the row rather than the column?

Bert: Nah, accountants don't do that so we're safe. Now who's paying for lunch?



Roy Lambert

Roy,

> I could hear the squeal of the brakes as you changed direction there

Damn! It could have worked if with a person with no mathematical background

Well, since the way was wrong, I really must change direction before the
crash

> I know SQLers talk about sets, but I don't think they, or the operators
> for them realistically stand up as mathematical sets. > The SQL standard
> seems to have decided to override key and fundamental mathematical rules.
> Consider

Yes, I agree, but in this case it's irrelevant that sets in SQL are
mathemathical sets or not. SUM is a function that by definition has an
unknown value when the argument is an empty set, list or whatever you want
to call it. In this sense I don't see any inconsistency here.

> Mathematics
> zero multiplied by anything equals zero
I would say zero multiplied by any number equals zero

> SQL
> NULL multiplied by anything equals NULL (although you can't strictly say
> equals)
NULL is a state, not a numeric value, so you can't strictly say multiplied
..

>However, it doesn't matter what you eventually fill the unknown value with
>the result is guaranteed to be zero so in the >specific case of zero
>multiplied by NULL the answer is not NULL it is zero.
I agree that in this specific case it could be an exception, just as with
boolean values, False and NULL = False, etc .


> I can just imagine the discussion when setting the standard ...
LOL

--
Fernando Dias
[Team Elevate]

Fernando

>Yes, I agree, but in this case it's irrelevant that sets in SQL are
>mathemathical sets or not. SUM is a function that by definition has an
>unknown value when the argument is an empty set, list or whatever you want
>to call it. In this sense I don't see any inconsistency here.

Goody - where's the definition published please.


OK try this scenario with a sort of pseudo code:

what I think should be there

initialise total to 0
processrow:
if row value is null loop otherwise total = total plus row value
if no more rows exit
report total


what is there

initialise total to 0
initialise rows processed to 0
processrow:
if row value is null loop otherwise total = total plus row value
if no more rows exit
if rows processed greater than 0 then report total otherwise report null

Roy Lambert

Roy,

<< I know SQLers talk about sets, but I don't think they, or the operators
for them realistically stand up as mathematical sets. The SQL standard seems
to have decided to override key and fundamental mathematical rules. >>

I think you're wrong there.  What exactly isn't valid ?

<< However, it doesn't matter what you eventually fill the unknown value
with the result is guaranteed to be zero so in the specific case of zero
multiplied by NULL the answer is not NULL it is zero. >>

No, it isn't.  You're attempting to posit that something is known when it
isn't known. It doesn't matter what the mathematical rules are for a certain
operator - the result is unknown if one of the operands is unknown.  You
can't proceed from there and say "but, if the value was known", because it
isn't known.  Period...stop there...don't go any further.

<< John: Err, wait a minute, the FD will want to be able to add up his
accounts. Are you saying that if one of the rows has one of these NULLs in
it he won't get an answer? >>

Wrong.  Accounts would be rows, and rows would add all non-NULL values.

--
Tim Young
Elevate Software
www.elevatesoft.com