----- Original Message -----
From: "D. Richard Hipp" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Thursday, September 08, 2005 1:24 PM
Subject: Re: [sqlite] SUM and NULL values
Can somebody come up with a realistic scenario where they would
actually want SUM() to return NULL instead of 0? NULL is such
a profoundly broken return value for sum() in my mind that I'm
thinking of ignoring the standard and just coding SQLite to do
the Right Thing. But I am open to the possibility that there
are some cases outside of my imagination where returning zero
might be considered "wrong".
If nobody can suggest a scenario where SUM() returning NULL is
actually helpful, then I will likely return SQLite to its former
mode of operation which is to have SUM() return 0 when it has no
input.
If 0 does not answer the question, then 0 should not be proffered as the
answer.
SUM without any rows should return NULL. It had no type affinity with which
to obtain an answer for the question, and no meaningful rows were available
with which to glean an answer.
If SUM returns 0 when no rows were evaluated, then SQLite is making an
assumption about what the answer should be, which is incorrect.
SELECT SUM(temperature) FROM antartica WHERE temperature < -150 LIMIT 1
Does it makes sense for that query to return 0 if no rows matched the
criteria? If SQLite doesn't know the answer definitively, then it shouldn't
return one.
Robert
