Robert Simpson wrote:
Lets take the October sales further. Lets say if there were any sales
in October, that you had to subtract $100 from the total sales for
October to account for ... lets say "shipping costs".
SELECT SUM(amt) - 100 from sales where month = 'october'
If there were no sales, under your query plan, I'd still have been in
the hole $100.
This is vastly oversimplified in order to show that "0" does not always
answer the question and can cascade into an even worse scenario.
I would think "SELECT SUM(amt)" means "How much did I sell?" or "What
was my income?", and a better query here would be "SELECT SUM(amt) -
SUM(costs)". Just because there were no sales doesn't mean that the
profit (or loss) is indeterminate. If your boss asked you "How much did
you make in October?" the answer is not "I can't tell."
Exacting correctness aside, I would expect 0 to confuse fewer people.
Is it more likely an SQL expert relies on NULL being different than 0,
or that a non-expert selects sum() without considering the NULL possibility?
Perhaps a pragma is the right answer?
--
Ted Unangst www.coverity.com Coverity, Inc.
