Jay Sprenkle wrote:
So, in some cases (explicite addition), NULL + 0 = NULL. (we all know
that NULL != NULL). However, the "sum" function skips NULLs. This
seems inconsistant.
NULL + 0 is not valid since you can't do a binary operation on only one
number (zero is
a number, NULL is not). So it returns an appropriate result when you ask it
to do something
it can't do.
Exactly. NULL is the appropraite result for the exact reason that you
gave. To me this seems similar to mixing up units, like saying that the
store is 3.5 km + 10 minutes + 2 radians from my house. The units just
don't mix.
I understand the rational for "NULL + anything" returning NULL (makes
sense mathematically). I understand the utility of having "sum" skip
NULLs. However, we can now construct a math in the sql engine where
a+b+c != a+b+c. Yucky.
Only where one of the variables is different than the other side of the
equation.
The point being that if I sum up the rows using "sum()", I could get one
answer. However, if I iterate the cursor in a stored procedure and
manually sum them up using " accum += value" I would get a different
answer. The math is inconsistant (in postgresql anyway). I actaully
did not test sqlite :). I was just posting this to bring up the point
about consistnecy. I'll let those with more knowledge than me hash it
out. (no pun there).
