----- Original Message -----
From: "Ted Unangst" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Thursday, September 08, 2005 3:20 PM
Subject: Re: [sqlite] SUM and NULL values
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."
You're looking at the query and injecting intent into it when it is merely
an example of what can go wrong when you deviate from the SQL standard. The
bottom line is that SQLite returns different query results than other SQL
databases because of this deviation, which is compounded when you build
other SQL-compliant code around those functions.
One of SQLite's design goals is standards conformance. Lets not give it up.
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?
There shouldn't have to be a pragma to enforce SQL conformance.
Robert
