RE: [sqlite] SUM and NULL values

2005-09-09 Thread D. Richard Hipp
On Fri, 2005-09-09 at 16:54 -0400, Ned Batchelder wrote:
> I can't follow this thread (NULLs make my head hurt), but it looks like Dr.
> Hipp has already taken action.  Yesterday he made two changes to the source:
> 

The changes yesterday brought SQLite's SUM function into compliance
with the (broken) SQL standard.  My plan is to leave things that
way.  I'll likely introduce some non-standard functions (perhaps
named "_sum" or "sqlite_sum") that work in a useful way - which is
to say the way that Martin Engelschalk and several others have
suggested:

sum() == 0
sum() == NULL
sum() == the correct sum


-- 
D. Richard Hipp <[EMAIL PROTECTED]>



RE: [sqlite] SUM and NULL values

2005-09-09 Thread Ned Batchelder
I can't follow this thread (NULLs make my head hurt), but it looks like Dr.
Hipp has already taken action.  Yesterday he made two changes to the source:

SUM returns NULL when it has no inputs:
http://www.sqlite.org/cvstrac/chngview?cn=2678

A SUM() of all NULLs returns NULL. A SUM() of nothing return 0. A SUM() of a
mixture of NULLs and numbers returns the sum of the numbers:
http://www.sqlite.org/cvstrac/chngview?cn=2677

--Ned.
http://nedbatchelder.com
 

-Original Message-
From: Jay Sprenkle [mailto:[EMAIL PROTECTED] 
Sent: Friday, 09 September, 2005 4:22 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SUM and NULL values

Dr. Hipp,

If my opinion is worth anything I'd prefer to stay with the standard, even 
though it might be a pain, etc. I've had too much bad experience with people

writing code that depends on the quirks in other people's code.

The decision is ultimately yours, and thanks for putting so much effort into

it!


RE: [sqlite] SUM and NULL values

2005-09-09 Thread Andrew Shakinovsky
I'll second that opinion, FWIW.

>>-Original Message-
>>From: Jay Sprenkle [mailto:[EMAIL PROTECTED] 
>>Sent: Friday, September 09, 2005 4:22 PM
>>To: sqlite-users@sqlite.org
>>Subject: Re: [sqlite] SUM and NULL values
>>Importance: Low
>>
>>Dr. Hipp,
>>
>>If my opinion is worth anything I'd prefer to stay with the 
>>standard, even though it might be a pain, etc. I've had too 
>>much bad experience with people writing code that depends on 
>>the quirks in other people's code.
>>
>>The decision is ultimately yours, and thanks for putting so 
>>much effort into it!
>>


Re: [sqlite] SUM and NULL values

2005-09-09 Thread Jay Sprenkle
Dr. Hipp,

If my opinion is worth anything I'd prefer to stay with the standard, even 
though it might be a pain, etc. I've had too much bad experience with people 
writing code that depends on the quirks in other people's code.

The decision is ultimately yours, and thanks for putting so much effort into 
it!


Re: [sqlite] SUM and NULL values

2005-09-09 Thread Puneet Kishor


On Sep 9, 2005, at 2:59 PM, Cam Crews wrote:

I heard someone say that SUM is a binary operation. It is not, 
because you

can feed it any number of values. It is not the same as "+".


While I don't believe anyone has claimed SUM represents a binary
operation, the function's behavior has been contrasted to the binary
'+' operator.  Certainly, I hope we can all agree, when SUM is fed 2
values it should arrive at the same result as the '+' operator.  The
similarity between the two is worth exploring further:

As Jay explained earlier so well, NULL + 0 = NULL expressly because
NULL is valueless, leaving the binary operation with only one value,
which results in the expression not being able to evaluate properly.
When you can't evaluate something, this results in an absence of value
(i.e. NULL).


there is a potential problem here...

any set operation can be reduced eventually to a binary operation.

  SUM(4, 3, 2, 1)
= SUM(4, 3, 3)
= SUM(4, 6)
= 4 + 6
= 10

still, most would agree that

  SUM(4, NULL, 2, 1)
= 7

even though

  SUM(NULL, NULL, NULL, NULL)
= NULL

the above would be the common expectation for most folks for all 
aggregate operations except for COUNT whereby

  COUNT(NULL, NULL, NULL, NULL)
= 4

without any controversy.



Similarly, the SUM of a valueless entity (NULL) should evaluate to a
valueless entity (NULL).  If we instead determine that the SUM of an
empty set or series of NULLs evaluates to 0, we are inferring value
where none exists.

For example, let's say we want to calculate a value of "light
intensity" for a pixel, which we'll determine by taking the SUM of a
pixel's RGB hex values.  Now, let's say we try calculating the
intensity of a non-existent pixel.  If SUM( NULL, NULL, NULL)
evaluates to 0, this indicates a black pixel when in reality there is
no pixel at all.  Which prompts the question, how can "nothing"
possess a color?  Would we really want to assume the absence of color
is equivalent to pitch black?


actually, when you are dealing with RGB, absence of "color" is actually 
absence of light, which is in effect, pitch black. Now, if you were 
talking CMYK... still, you make a valid point.






Can somebody come up with a realistic scenario where they would
actually want SUM() to return NULL instead of 0?


Yes; lets say I manage a system for a company that issues credit cards
to customers.  As they use their credit line, they accrue debt to my
company, and this is reflected in the database.

Now, lets say I want my staff to be able to access the amount a
customer owes, searching by a customer ID, and returning the SUM of
the customer's debts for each card she has been issued.  If she's paid
all her balances off (like a smart spender) we'd expect the SUM should
be 0.  But let's say that she owes us $20k and the staff member enters
in an incorrect ID # when looking up the balance.  The system takes
the SUM of NULL(s) and returns 0, leading to the mistaken
interpretation that she has paid her credit line off, when in
actuality the query was erroneous and she is deep in the red.

Would you not agree that NULL value should have been returned for this
case?  How else can we discriminate between erroneous cases and actual
zero-sums?  Yes, you can argue that the error here is the result of
the staff member mistyping an ID, but it's important to recognize we
are obfuscating the matter by returning a valid-appearing result when
the system should be returning an error.  It's these cases to watch
out for where the zero-sum shortcut will be potentially dangerous.



well, the system should be returning an error only if someone programs 
it to recognize that condition as an error. The question is whether or 
not the following should be programmed in


SUM|AVE|MIN|MAX(of all NULL values) = NULL
SUM|AVE|MIN|MAX(of some NULL and some NON NULL values) = SUM(of all NON 
NULL values)
COUNT(of all NULL OR some NULL and some NON NULL values) = count of all 
elements in the set, null or not


I say, yes, as it makes sense to me.


--
Puneet Kishor



Re: [sqlite] SUM and NULL values

2005-09-09 Thread Cam Crews
> I heard someone say that SUM is a binary operation. It is not, because you
> can feed it any number of values. It is not the same as "+".

While I don't believe anyone has claimed SUM represents a binary
operation, the function's behavior has been contrasted to the binary
'+' operator.  Certainly, I hope we can all agree, when SUM is fed 2
values it should arrive at the same result as the '+' operator.  The
similarity between the two is worth exploring further:

As Jay explained earlier so well, NULL + 0 = NULL expressly because
NULL is valueless, leaving the binary operation with only one value,
which results in the expression not being able to evaluate properly. 
When you can't evaluate something, this results in an absence of value
(i.e. NULL).

Similarly, the SUM of a valueless entity (NULL) should evaluate to a
valueless entity (NULL).  If we instead determine that the SUM of an
empty set or series of NULLs evaluates to 0, we are inferring value
where none exists.

For example, let's say we want to calculate a value of "light
intensity" for a pixel, which we'll determine by taking the SUM of a
pixel's RGB hex values.  Now, let's say we try calculating the
intensity of a non-existent pixel.  If SUM( NULL, NULL, NULL)
evaluates to 0, this indicates a black pixel when in reality there is
no pixel at all.  Which prompts the question, how can "nothing"
possess a color?  Would we really want to assume the absence of color
is equivalent to pitch black?

> Can somebody come up with a realistic scenario where they would
> actually want SUM() to return NULL instead of 0? 

Yes; lets say I manage a system for a company that issues credit cards
to customers.  As they use their credit line, they accrue debt to my
company, and this is reflected in the database.

Now, lets say I want my staff to be able to access the amount a
customer owes, searching by a customer ID, and returning the SUM of
the customer's debts for each card she has been issued.  If she's paid
all her balances off (like a smart spender) we'd expect the SUM should
be 0.  But let's say that she owes us $20k and the staff member enters
in an incorrect ID # when looking up the balance.  The system takes
the SUM of NULL(s) and returns 0, leading to the mistaken
interpretation that she has paid her credit line off, when in
actuality the query was erroneous and she is deep in the red.

Would you not agree that NULL value should have been returned for this
case?  How else can we discriminate between erroneous cases and actual
zero-sums?  Yes, you can argue that the error here is the result of
the staff member mistyping an ID, but it's important to recognize we
are obfuscating the matter by returning a valid-appearing result when
the system should be returning an error.  It's these cases to watch
out for where the zero-sum shortcut will be potentially dangerous.


Lastly, consider that SUM exhibits the identity property when passed a
single value.  ex:
SUM(4) = 4
SUM(0) = 0
SUM(NULL) = NULL

If we pass SUM a NULL value (i.e. no value) we should still expect it
to return what we put in (i.e. no value).  Otherwise, we're
manufacturing zero-values which, while this may often be what *think*
we want, represents a confusing twist on standard expectations that
may mask deeper problems within a system.


RE: [sqlite] SUM and NULL values

2005-09-09 Thread F.W.A. van Leeuwen
If NULL means "I don't know" then IMHO the only sensible and logic correct 
thing to do is:

SUM() = 0,
since that conforms to common-sense algebra. Like
   SUM(9, 9, 9) = 3x9 = 27
   SUM(9, 9) = 2x9 = 18
   SUM(9) = 1x9 = 9
   SUM() = 0x9 = 0 and certainly not NULL (I don't know).

I heard someone say that SUM is a binary operation. It is not, because you 
can feed it any number of values. It is not the same as "+".

Not however that this is not valid for all operations. For example:
   MIN() = NULL
   MAX() = NULL
   AVG() = NULL
   COUNT() = 0

However:
SUM() = NULL
since 1 of the values is unknown, we can't know the answer

Best regards,
Frank.


RE: [sqlite] SUM and NULL values

2005-09-08 Thread Mark de Vries
On Thu, 8 Sep 2005, Marcus Welz wrote:

> Yes, the NULL as it is returned by SUM means "No data to answer your
> question".
>
> If that query returned 0 (as SQLite currently does), rather than NULL (as
> the standard specifies), it would not allow me to figure out whether I sold
> $0 worth of items or if I didn't sell anything at all.

Exactly! I was reading through the thread wondering when this point would
be made (and getting ready to make it myself.)

The SUM() of non-NULL values can be 0! If SQL(ite) would return 0 for all
NULL input there would be no way do make the distiction between a SUM()
that's 0 and 'no data to be summed'. Which can be an important
distinction.

Please! Make SQLite return NULL for all NULL input.

I totally agree that null handeling seems/is inconsistent as hell at
times... It seems hackish here and there. Which, IMHO, is the ALL THE
REASON jou need to to just follow the standard!

Rgds,
Mark.


> -Original Message-
> From: Puneet Kishor [mailto:[EMAIL PROTECTED]
> Sent: Thursday, September 08, 2005 6:50 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] SUM and NULL values
>
>
> On Sep 8, 2005, at 5:45 PM, Marcus Welz wrote:
>
> > If "SELECT SUM(amt)" means "How much did I sell?"
> >
> > Then the "NULL" should mean, "You didn't sell anything.", no?
> >
>
> no... NULL means, "I don't have any data to answer that question" which
> is very different from "I sold $0 worth of things" or "I didn't sell
> anything."
>
> --
> Puneet Kishor
>
>


Regards,
Mark



Re: [sqlite] SUM and NULL values

2005-09-08 Thread Klint Gore
On Thu, 08 Sep 2005 16:24:14 -0400, "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:
> 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".

Isn't the meaning of 0 part of the application logic?  If you really are
going to do it, can there be a pragma to switch the behaviour?


> 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.


What about when the sum is on a field in a left joined table?  

create table items 
  (item_code char(10), description varchar(30));
  
insert into items values('WIDGETS','WIDGET 3.5 inch');
insert into items values('BOXES','Box 2 inch');
insert into items values('ETCHA','Etch-a-sketch');

create table transactions 
   (item_code char(10), 
customer_code char(10), 
purchase_date date,
amt numeric);

-- customer buys widget

insert into transactions('WIDGETS','CASH','20050905',11.00);

-- customer brings widget back
insert into transactions('WIDGETS','CASH','20050907,'-11.00);

-- customer from last month brings box back
insert into transactions('BOXES','CASH','20050907,'-20.00);

-- monthly profitiblity report

select items.item_code, 
   sum(transactions.amt), count (transactions.item_code)
>from items
left outer join transactions on transactions.item_code = items.item_code
where date >= '20050901' and date <= '20050930'
order by 3 desc, 4 desc

I want the result to be descending money, then descending counts, with
no activity items at the end.

klint.

+---+-+
: Klint Gore: "Non rhyming:
: EMail   : [EMAIL PROTECTED]   :  slang - the:
: Snail   : A.B.R.I.:  possibilities  :
: Mail  University of New England   :  are useless"   :
:   Armidale NSW 2351 Australia : L.J.J.  :
: Fax : +61 2 6772 5376 : :
+---+-+


Re: [sqlite] SUM and NULL values

2005-09-08 Thread Ted Unangst

Robert Simpson wrote:


Perhaps a pragma is the right answer?


There shouldn't have to be a pragma to enforce SQL conformance.


I should have clarified it should default to conforming, if there were 
to be such a thing.  Like pragma friendly_null, defaulting to off.  But 
your salary example is much more compelling than the October sales, so I 
change my vote.  Standards are good. :)



--
Ted Unangst www.coverity.com Coverity, Inc.



RE: [sqlite] SUM and NULL values

2005-09-08 Thread Marcus Welz
Yes, the NULL as it is returned by SUM means "No data to answer your
question".

I was talking in context with the example of the fictional sales situation,
rather than the technical description of what SUM() exactly does, because,
as I see it, we're trying to figure out whether the technical
description/implementation makes sense.

If that query returned 0 (as SQLite currently does), rather than NULL (as
the standard specifies), it would not allow me to figure out whether I sold
$0 worth of items or if I didn't sell anything at all.

-Original Message-
From: Puneet Kishor [mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 08, 2005 6:50 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SUM and NULL values


On Sep 8, 2005, at 5:45 PM, Marcus Welz wrote:

> If "SELECT SUM(amt)" means "How much did I sell?"
>
> Then the "NULL" should mean, "You didn't sell anything.", no?
>

no... NULL means, "I don't have any data to answer that question" which 
is very different from "I sold $0 worth of things" or "I didn't sell 
anything."

--
Puneet Kishor



Re: [sqlite] SUM and NULL values

2005-09-08 Thread Glen Nakamura
On Thu, Sep 08, 2005 at 05:16:30PM -0400, D. Richard Hipp wrote:
> So SQL sez:
> 
> SUM()  ==  NULL
> SUM()  ==  NULL
> SUM() ==  Sum of non-null values
> 
> Martin sez:
> 
> SUM()  == 0
> SUM()  == NULL
> SUM() == NULL
> 
> SQLite 3.2.5 does this:
> 
> SUM()  == 0
> SUM()  == 0
> SUM() == Sum of non-null values
> 
> I can understand Martin's point of view.  The SQL standard
> point of view makes no sense to me at all.  

Note that all three of the above systems define SUM()
to be an identity element such that:
SUM(SUM(X), SUM()) = SUM(X)

However, the following system breaks the identity:

SUM()  ==  0
SUM()  ==  NULL
SUM() ==  Sum of non-null values

SUM(SUM(), SUM()) = SUM(NULL, 0) = 0 != SUM()

So for a mathematically consistent definition of SUM, it makes sense that
SUM() == NULL if SUM() == NULL.

- glen


Re: [sqlite] SUM and NULL values

2005-09-08 Thread Puneet Kishor


On Sep 8, 2005, at 5:45 PM, Marcus Welz wrote:


If "SELECT SUM(amt)" means "How much did I sell?"

Then the "NULL" should mean, "You didn't sell anything.", no?



no... NULL means, "I don't have any data to answer that question" which 
is very different from "I sold $0 worth of things" or "I didn't sell 
anything."


--
Puneet Kishor



RE: [sqlite] SUM and NULL values

2005-09-08 Thread Marcus Welz
If "SELECT SUM(amt)" means "How much did I sell?"

Then the "NULL" should mean, "You didn't sell anything.", no?

To me, there is a difference between 0 ("You sold merchandise worth $0"
perhaps because of sweepstakes, giveaway, rebate coupons, etc) and NULL
("You didn't sell anything.").


I think that Robert Simpson nailed on the head with his salary example.


If you want a quick report on sales per month:

SELECT month, COALESCE(SUM(amount), 'No Sales') from table GROUP BY month


If you insist that you want the result to be zero rather than NULL:

SELECT month, COALESCE(SUM(amount), 0) from table GROUP BY month


The behavior according to the SQL standard allows for flexibility.



-Original Message-
From: Ted Unangst [mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 08, 2005 6:21 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SUM and NULL values

*snip*

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.



Re: [sqlite] SUM and NULL values

2005-09-08 Thread Robert Simpson
- Original Message - 
From: "Ted Unangst" <[EMAIL PROTECTED]>

To: 
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




Re: [sqlite] SUM and NULL values

2005-09-08 Thread Puneet Kishor


On Sep 8, 2005, at 5:20 PM, Ted Unangst wrote:


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."




oh yes, it is the correct answer. "I can't tell because I don't have 
the data." Therein lies the problem... if we contrive concrete 
examples, we can go both ways -- the way of NULL or the way of 0. The 
fact is, NULL is the absence of data. Hence, I can't tell is a valid 
answer.



--
Puneet Kishor



Re: [sqlite] SUM and NULL values

2005-09-08 Thread Ted Unangst

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.



Re: [sqlite] SUM and NULL values

2005-09-08 Thread Puneet Kishor

NULL is the absence of value.

Any aggregation operation on a set where ALL the elements of the set 
are "absences of values" should return an "absence of value." If ALL 
the elements are NOT NULL then the aggregation operation should be 
applied to the elements that have a "presence of value."


The only exception should be COUNT as it would return the number of 
elements in the set without peeking in at the values of the members of 
the set, hence never even encountering the absence or presence of 
values of them.




--
Puneet Kishor



Re: [sqlite] SUM and NULL values

2005-09-08 Thread Robert Simpson
If SQLite doesn't conform to the SQL standard, it'll break several other 
dependencies on the standard:


CREATE TABLE salaries (empid int, hourlywage money, yearlywage money, 
emptype int)


INSERT INTO salaries VALUES(1, 12.95, null, 1)
INSERT INTO salaries VALUES(2, null, 8.00, 2)

SELECT COALESCE(SUM(hourlywage * 40 * 52), SUM(yearlywage)) from salaries 
GROUP BY emptype



In SQL Server, this returns:


- 
26936.

8.


In SQLite it returns:


- 
26936

0




RE: [sqlite] SUM and NULL values

2005-09-08 Thread Marcus Welz
For some reason I have to agree with the SQL standard, which I would
interprete as the following (and this may not be all that bullet proof):

"If the result set contains numeric values, sum them up, ignoring NULLs. If
there are no numeric values present (the result set is either empty or
contains only NULLs) return NULL."

There's a fine difference between NULL and 0. If I have an unordered result
set of:

a|b
--
NULL |NULL
NULL |NULL
NULL |NULL
0|NULL
NULL |NULL

I would want SUM(a) to be zero, but I'd want SUM(b) to be NULL. Just like
I'd want MIN(a) to return zero, but MIN(b) to be NULL.

If SUM() were to always return 0, wouldn't it break the logic of how it
behaved compared to its MIN(), MAX() and AVG() siblings?

Always returning at least 0 because it makes sense to me would not be enough
for me to justify breaking away from the standard, particularly because it's
so easy to ensure that the result will be 0 and not NULL as Martin mentioned
using "sum(coalesce(row, 0))". 

And after all -- it's the standard. And absolutely I love using SQLite
simply because it (seems to) follow the standard so closely. The introduced
oh-so-subtle difference for developers ("MySQL, PostgreSQL, etc. etc. return
NULL, but SQLite actually defies the SQL standard and returns 0") could be
rather confusing.

I'm by no means an expert or authority when it comes to SQL, but NULL is
rather interesting. Since "NULL is always false even to itself" I think that
by having SUM(b) return NULL this "quirkiness" or "speciality" behavior of
NULL is preserved. Having the SUM() of a bunch of NULLs be 0 seems a bit too
magic to me.

-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 08, 2005 5:17 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SUM and NULL values

*snip*

So SQL sez:

SUM()  ==  NULL
SUM()  ==  NULL
SUM() ==  Sum of non-null values

*snip*

SQLite 3.2.5 does this:

SUM()  == 0
SUM()  == 0
SUM() == Sum of non-null values




Re: [sqlite] SUM and NULL values

2005-09-08 Thread Robert Simpson


SELECT SUM(temperature) FROM antartica WHERE temperature < -150 LIMIT 1



Ignore the obvious logic error in this statement, since it will always 
return 1 row ... the meaning was that 0 does not always answer every 
question.


Robert




Re: [sqlite] SUM and NULL values

2005-09-08 Thread Robert Simpson
- Original Message - 
From: "Martin Engelschalk" <[EMAIL PROTECTED]>

To: 
Sent: Thursday, September 08, 2005 2:23 PM
Subject: Re: [sqlite] SUM and NULL values


SUM() itself does imply an numerical type return and does not need any 
rows
from which to get a type - even more so in SQLite, where types are not a 
big issue.

To return to the example "sales in october":
If there are no records for october, then there were no sales, and the sum 
of - say - profits is obviously 0.
If there is a record for october, but is contains a NULL value for the 
profits, this
means there is no data for an existing october sale, and sum() should 
return NULL.



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.



Robert






RE: [sqlite] SUM and NULL values

2005-09-08 Thread Cariotoglou Mike
There is one other thing, though. Allthough the sql standard is 
(insert your favorite 4-letter word), and although I personally hate
nulls, I try to write applications where the sql is as portable as it
can be. So, in the name of portability, you should follow the sql
standard.



Re: [sqlite] SUM and NULL values

2005-09-08 Thread Martin Engelschalk

D. Richard Hipp schrieb:


On Thu, 2005-09-08 at 23:05 +0200, Martin Engelschalk wrote:
 

I have found that, when dealing with NULL values, it helps to think of a 
Null as "I don't know, I have no data".

So, if the where clause returns no records, I do know the result: It is 0.
If there where clause returns records with NULL values, that means I did 
not know the values and so can

not know the sum, too. Sum() should therefore return NULL.
When I want to regard a NULL value as 0 in this (or any) context, I use 
a sum(coalesce(row, 0)).


   



Martin, your argument makes good sense.  Unfortunately, that
is not what the SQL standard says should happen.  According
to SQL (or at least the authorities on the subject I have
access to) any NULL values in the series input to SUM are
to be ignored.  SUM returns the summation of the non-null
values.  Or if there are no non-NULL values SUM returns NULL.

So SQL sez:

   SUM()  ==  NULL
   SUM()  ==  NULL
   SUM() ==  Sum of non-null values

Martin sez:

   SUM()  == 0
   SUM()  == NULL
   SUM() == NULL

SQLite 3.2.5 does this:

   SUM()  == 0
   SUM()  == 0
   SUM() == Sum of non-null values

I can understand Martin's point of view.  The SQL standard
point of view makes no sense to me at all.  
 

Yes, I see - The  case escaped my notice in the heat of the 
argument :-)

In any case, I would never build my queries to depend on the DB Engine
(not even the fantastic SQLite) in such a difficult case, but perhaps 
check for null values first and use coalesce / nvl


Martin




Re: [sqlite] SUM and NULL values

2005-09-08 Thread D. Richard Hipp
On Thu, 2005-09-08 at 14:12 -0700, Robert Simpson wrote:

> 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?  

Temperature is an intensive property, not an extensive property, and
is therefore not additive.  SUM(temperature) not a sensible thing to
do.  The correct query should omit the SUM like this:

   SELECT temperature FROM antartica WHERE temperature < -150 LIMIT 1

Which gives the result you seek.

Thank you for the suggestion, though...
-- 
D. Richard Hipp <[EMAIL PROTECTED]>



Re: [sqlite] SUM and NULL values

2005-09-08 Thread Martin Engelschalk

Robert Simpson wrote:

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


I am sorry, but I do not agree.
SUM() itself does imply an numerical type return and does not need any rows
from which to get a type - even more so in SQLite, where types are not a 
big issue.

To return to the example "sales in october":
If there are no records for october, then there were no sales, and the 
sum of - say - profits is obviously 0.
If there is a record for october, but is contains a NULL value for the 
profits, this
means there is no data for an existing october sale, and sum() should 
return NULL.


Martin

PS:  I am aware that the discussion about NULL is an old one and divides 
the world in warring factions :-)





Re: [sqlite] SUM and NULL values

2005-09-08 Thread D. Richard Hipp
On Thu, 2005-09-08 at 23:05 +0200, Martin Engelschalk wrote:
> I have found that, when dealing with NULL values, it helps to think of a 
> Null as "I don't know, I have no data".
> So, if the where clause returns no records, I do know the result: It is 0.
> If there where clause returns records with NULL values, that means I did 
> not know the values and so can
> not know the sum, too. Sum() should therefore return NULL.
> When I want to regard a NULL value as 0 in this (or any) context, I use 
> a sum(coalesce(row, 0)).
> 

Martin, your argument makes good sense.  Unfortunately, that
is not what the SQL standard says should happen.  According
to SQL (or at least the authorities on the subject I have
access to) any NULL values in the series input to SUM are
to be ignored.  SUM returns the summation of the non-null
values.  Or if there are no non-NULL values SUM returns NULL.

So SQL sez:

SUM()  ==  NULL
SUM()  ==  NULL
SUM() ==  Sum of non-null values

Martin sez:

SUM()  == 0
SUM()  == NULL
SUM() == NULL

SQLite 3.2.5 does this:

SUM()  == 0
SUM()  == 0
SUM() == Sum of non-null values

I can understand Martin's point of view.  The SQL standard
point of view makes no sense to me at all.  
-- 
D. Richard Hipp <[EMAIL PROTECTED]>



Re: [sqlite] SUM and NULL values

2005-09-08 Thread Robert Simpson
- Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>

To: 
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




RE: [sqlite] SUM and NULL values

2005-09-08 Thread D. Richard Hipp
On Thu, 2005-09-08 at 16:43 -0400, Thomas Briggs wrote:
>  
> > Can somebody come up with a realistic scenario where they would
> > actually want SUM() to return NULL instead of 0?  
> 
>I think your example of totaling sales in October makes the argument
> itself - you didn't make sales totaling $0 in October, you just didn't
> make any sales.  A subtle but irritatingly big difference. :)
> 

No.  The question I asked was what was the total value of
all sales in October.  That answer is 0.  If I had wanted to
know whether or not there were any sales in October I would
have used COUNT instead of SUM.

By your argument, the official meaning of SUM in SQL is some
strange and not particularly useful comingling of the
concepts of magnitude (sum) and cardinality (count).  It
seems much more useful to keep these concepts distinct and
in separate functions, which is what I am proposing to do
in SQLite in defiance of the SQL standard.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>



Re: [sqlite] SUM and NULL values

2005-09-08 Thread Martin Engelschalk

D. Richard Hipp wrote:


Can somebody come up with a realistic scenario where they would
actually want SUM() to return NULL instead of 0? 

I have found that, when dealing with NULL values, it helps to think of a 
Null as "I don't know, I have no data".

So, if the where clause returns no records, I do know the result: It is 0.
If there where clause returns records with NULL values, that means I did 
not know the values and so can

not know the sum, too. Sum() should therefore return NULL.
When I want to regard a NULL value as 0 in this (or any) context, I use 
a sum(coalesce(row, 0)).


Martin



RE: [sqlite] SUM and NULL values

2005-09-08 Thread Bob Dankert
I would think that if you are looking to know the total of something
(which is what SUM provides), and there is nothing to total, the total
should be 0.  If you want to know if any sales were made, it seems you
should be using COUNT and not SUM.

Just my opinion, of course.

Bob

Envision Information Technologies
Associate
[EMAIL PROTECTED]
v. 608.256.5680
f. 608.256.3780
 

-Original Message-
From: Thomas Briggs [mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 08, 2005 3:43 PM
To: sqlite-users@sqlite.org
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

   I think your example of totaling sales in October makes the argument
itself - you didn't make sales totaling $0 in October, you just didn't
make any sales.  A subtle but irritatingly big difference. :)

   -Tom


Re: [sqlite] SUM and NULL values

2005-09-08 Thread Jay Sprenkle
On 9/8/05, Thomas Briggs <[EMAIL PROTECTED]> wrote:
> 
> 
> 
> > Can somebody come up with a realistic scenario where they would
> > actually want SUM() to return NULL instead of 0? NULL is such
> 
> I think your example of totaling sales in October makes the argument
> itself - you didn't make sales totaling $0 in October, you just didn't
> make any sales. A subtle but irritatingly big difference. :)



Null is a big pain sometimes! :)


RE: [sqlite] SUM and NULL values

2005-09-08 Thread Thomas Briggs
 

> Can somebody come up with a realistic scenario where they would
> actually want SUM() to return NULL instead of 0?  NULL is such

   I think your example of totaling sales in October makes the argument
itself - you didn't make sales totaling $0 in October, you just didn't
make any sales.  A subtle but irritatingly big difference. :)

   -Tom


Re: [sqlite] SUM and NULL values

2005-09-08 Thread Jay Sprenkle
On 9/8/05, Dennis Jenkins <[EMAIL PROTECTED]> wrote:

> 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).



Depends on if you start with accum = 0;
If you don't it throws an exception, if you do, then you get zero (for an 
empty set).
That's the crux of the difference in philosophies.





-- 
---
The Castles of Dereth Calendar: a tour of the art and architecture of 
Asheron's Call
http://www.lulu.com/content/77264


Re: [sqlite] SUM and NULL values

2005-09-08 Thread Jay Sprenkle
On 9/8/05, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
> 
> 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.


Look at NULL as an error message. When you ask it to do a binary operation 
(sum is addition)
on an empty set it can't since it doesn't have two values, so it returns an 
error indication.
It's probably a lot more practically useful to return zero, but it's not 
standard.


Re: [sqlite] SUM and NULL values

2005-09-08 Thread Dennis Jenkins

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).




Re: [sqlite] SUM and NULL values

2005-09-08 Thread D. Richard Hipp
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.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>



Re: [sqlite] SUM and NULL values

2005-09-08 Thread Jay Sprenkle
> 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.


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 Castles of Dereth Calendar: a tour of the art and architecture of 
Asheron's Call
http://www.lulu.com/content/77264


Re: [sqlite] SUM and NULL values

2005-09-08 Thread Nemanja Corlija
On 9/8/05, D. Richard Hipp <[EMAIL PROTECTED]> wrote:

> PostgreSQL docs say that NULL is returned if all inputs
> to sum are NULL.
> 
> So then, if there are no input rows at all (if no rows
> match the WHERE clause) then SUM returns 0. 
Actually PostgreSQL 8.0.3 and FirebirdSQL 1.5.2 return NULL in this case too.

> (This makes
> sense because if you say:
> 
> SELECT sum(amt) FROM sales WHERE month='october';
> 
I agree that this does make sense in some cases, and it can also be
quite handy to distinguish between 'no rows matched WHERE clause' and
'all matching rows were NULL'.
But it appears it's not very consistent with other DB engines.


-- 
Nemanja Corlija <[EMAIL PROTECTED]>


Re: [sqlite] SUM and NULL values

2005-09-08 Thread Dennis Jenkins

From postgresql 8.0.1 on FreeBSD:

syslog-ng=# select NULL is null;
?column?
--
t
(1 row)

syslog-ng=# select (0+NULL) is null;
?column?
--
t
(1 row)

syslog-ng=# select (0) is null;
?column?
--
f
(1 row)


syslog-ng=# create table tmp1 ( a int4 );
CREATE TABLE
syslog-ng=# insert into tmp1 values (2);
INSERT 16949751 1
syslog-ng=# insert into tmp1 values (0);
INSERT 16949752 1
syslog-ng=# insert into tmp1 values (NULL);
INSERT 16949756 1
syslog-ng=# select * from tmp1;
a
---
2
0

(3 rows)

syslog-ng=# select a, a is null from tmp1;
a | ?column?
---+--
2 | f
0 | f
  | t
(3 rows)

syslog-ng=# select sum(a) from tmp1;
sum
-
  2
(1 row)

syslog-ng=# drop table tmp1;
DROP TABLE
syslog-ng=# \q




So, in some cases (explicite addition), NULL + 0 = NULL. (we all know 
that NULL != NULL).  However, the "sum" function skips NULLs.  This 
seems inconsistant.


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.





Re: [sqlite] SUM and NULL values

2005-09-08 Thread Jay Sprenkle
On 9/8/05, Thomas Briggs <[EMAIL PROTECTED]> wrote:
> 
> You (and I) may want an answer of 0, but I unfortunately believe NULL
> is the correct answer. The answer isn't 0 - the answer is "there is no
> answer", because there were no inputs. If that translates to 0 in
> reality that's up to you to interpret. :)



Yeah, I've cursed nulls occasionally because I had to jump through hoops to 
get
the results I wanted, but used them too when people wanted to know how many 
records
had missing data. That's why we get paid the big bucks! ;)


RE: [sqlite] SUM and NULL values

2005-09-08 Thread Thomas Briggs
 
> So then, if there are no input rows at all (if no rows
> match the WHERE clause) then SUM returns 0.  (This makes
> sense because if you say:
> 
> SELECT sum(amt) FROM sales WHERE month='october';
> 
> and you didn't sell anything in October, you want an
> answer of 0, not NULL.)  Or if *some* of the entries are

   You (and I) may want an answer of 0, but I unfortunately believe NULL
is the correct answer.  The answer isn't 0 - the answer is "there is no
answer", because there were no inputs.  If that translates to 0 in
reality that's up to you to interpret. :)

   I think the set-theoretical explanation is that an operation on a
NULL set is NULL.  COUNT seems to be an exception there, though...
Hrmph.


> The more I learn about NULLs in SQL the less sense they
> make...

   I have never intentionally declared a column that allowed NULLs. :)

   -Tom


Re: [sqlite] SUM and NULL values

2005-09-08 Thread Jay Sprenkle
On 9/8/05, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
> 
> 
> So then, if there are no input rows at all (if no rows
> match the WHERE clause) then SUM returns 0. (This makes
> sense because if you say:
> 
> SELECT sum(amt) FROM sales WHERE month='october';
> 
> and you didn't sell anything in October, you want an
> answer of 0, not NULL.) Or if *some* of the entries are
> NULL, then the answer is the sum of the non-NULL entries.
> But if the number of entries is greater than zero and
> they are all NULL, then the answer is NULL.
> 
> Logical, right



A sum is the addition of a set of numbers. 

Your logic is correct if you make an assumption that there's always a zero 
to start with.
Then the sum of an empty set is: zero + (nothing) = zero. An empty set can't 
have a binary operation applied to it.

You could also argue "The Since there are no numbers, there is no sum."

Isn't this specified in cj date's SQL standards book someplace?


Re: [sqlite] SUM and NULL values

2005-09-08 Thread D. Richard Hipp
At http://www.oreilly.com/catalog/sqlnut/chapter/ch04.html
I read:

   The AVG function computes the average of values in
   a column or an expression.  SUM computes the sum.
   Both functions ... ignore NULL values.

PostgreSQL docs say that NULL is returned if all inputs
to sum are NULL.

So then, if there are no input rows at all (if no rows
match the WHERE clause) then SUM returns 0.  (This makes
sense because if you say:

SELECT sum(amt) FROM sales WHERE month='october';

and you didn't sell anything in October, you want an
answer of 0, not NULL.)  Or if *some* of the entries are
NULL, then the answer is the sum of the non-NULL entries.
But if the number of entries is greater than zero and
they are all NULL, then the answer is NULL.

Logical, right

The more I learn about NULLs in SQL the less sense they
make...
-- 
D. Richard Hipp <[EMAIL PROTECTED]>



Re: [sqlite] SUM and NULL values

2005-09-08 Thread Vladimir Zelinski
Just for information:

Oracle returns NULL's in both cases: for SUM() and for
AVG().

I checked it for ver. 8.1.7 and 9.2.0



--- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote:

> On Thu, 2005-09-08 at 10:24 -0700, Will Leshner
> wrote:
> > Say I have a column called 'b' in a table called
> 'test' and every  
> > field in 'b' has a NULL value. What is the
> expected behavior of the  
> > following query:
> > 
> > SELECT SUM(b) FROM TEST;
> > 
> > I guess I'm thinking I'll get back NULL. Instead I
> get 0 (or, in the  
> > 3.2.5 code, 0.0).
> > 
> 
> SUM ignores NULLs.  So if it ignores everything, it
> returns the
> sum of nothing, which is 0.  (Not the 0.0 issue is
> fixed in CVS HEAD.)
> 
> > On the other hand, the following query does return
> NULL:
> > 
> > SELECT AVG(b) FROM TEST;
> > 
> 
> AVG is implemented as SUM/COUNT.  But the count is
> zero.  So
> you get a NULL.
> -- 
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 



RE: [sqlite] SUM and NULL values

2005-09-08 Thread Thomas Briggs

   Actually, every database I'm aware of returns NULL for any aggregate
whenever the inputs are NULL, and also when no input rows are processed
(i.e. if no rows match the criteria in the WHERE clause, the result of
the aggregation is NULL).

   -Tom

> -Original Message-
> From: Will Leshner [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, September 08, 2005 2:51 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] SUM and NULL values
> 
> 
> On Sep 8, 2005, at 11:33 AM, D. Richard Hipp wrote:
> 
> > SUM ignores NULLs.  So if it ignores everything, it returns the
> > sum of nothing, which is 0.  (Not the 0.0 issue is fixed in 
> CVS HEAD.)
> 
> As a data point, MySQL returns NULL for a SUM over fields that are  
> all NULL. I am not suggesting, of course, that SQLite should imitate  
> MySQL.
> 


Re: [sqlite] SUM and NULL values

2005-09-08 Thread Jay Sprenkle
On 9/8/05, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
> 
> 
> SUM ignores NULLs. So if it ignores everything, it returns the
> sum of nothing, which is 0. (Not the 0.0 issue is fixed in CVS HEAD.)


I would expect the sum of nothing is nothing, not zero, but that's 
interpretation
and I can see how others could easily expect something different.

---
The Castles of Dereth Calendar: a tour of the art and architecture of 
Asheron's Call
http://www.lulu.com/content/77264


Re: [sqlite] SUM and NULL values

2005-09-08 Thread Will Leshner


On Sep 8, 2005, at 11:33 AM, D. Richard Hipp wrote:


SUM ignores NULLs.  So if it ignores everything, it returns the
sum of nothing, which is 0.  (Not the 0.0 issue is fixed in CVS HEAD.)


As a data point, MySQL returns NULL for a SUM over fields that are  
all NULL. I am not suggesting, of course, that SQLite should imitate  
MySQL.


Re: [sqlite] SUM and NULL values

2005-09-08 Thread Will Leshner


On Sep 8, 2005, at 11:33 AM, D. Richard Hipp wrote:


SUM ignores NULLs.  So if it ignores everything, it returns the
sum of nothing, which is 0.  (Not the 0.0 issue is fixed in CVS HEAD.)


Ah. Perhaps I shouldn't have filed a bug report then. Sorry about  
that. I wonder what other databases do with this?


Re: [sqlite] SUM and NULL values

2005-09-08 Thread D. Richard Hipp
On Thu, 2005-09-08 at 10:24 -0700, Will Leshner wrote:
> Say I have a column called 'b' in a table called 'test' and every  
> field in 'b' has a NULL value. What is the expected behavior of the  
> following query:
> 
> SELECT SUM(b) FROM TEST;
> 
> I guess I'm thinking I'll get back NULL. Instead I get 0 (or, in the  
> 3.2.5 code, 0.0).
> 

SUM ignores NULLs.  So if it ignores everything, it returns the
sum of nothing, which is 0.  (Not the 0.0 issue is fixed in CVS HEAD.)

> On the other hand, the following query does return NULL:
> 
> SELECT AVG(b) FROM TEST;
> 

AVG is implemented as SUM/COUNT.  But the count is zero.  So
you get a NULL.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>



Re: [sqlite] SUM and NULL values

2005-09-08 Thread Jay Sprenkle
> Say I have a column called 'b' in a table called 'test' and every
> field in 'b' has a NULL value. What is the expected behavior of the
> following query:
> 
> SELECT SUM(b) FROM TEST;
> 
> I guess I'm thinking I'll get back NULL. Instead I get 0 (or, in the
> 3.2.5 code, 0.0).
> 
> On the other hand, the following query does return NULL:
> 
> SELECT AVG(b) FROM TEST;
> 

I should think NULL would be the correct result, but it's not what I get:

D:\temp\convention>sqlite3 testnull
SQLite version 3.0.8
Enter ".help" for instructions
sqlite> create table t( b integer, c integer );
sqlite> insert into t(b) values(1);
sqlite> insert into t(b) values(2);
sqlite> insert into t(b) values(3);
sqlite> select * from t;
1|
2|
3|
sqlite> select sum(c) from t;
0
sqlite> select sum(b) from t;
6
sqlite> select count(*) from t where c is null;
3
sqlite>



-- 
---
The Castles of Dereth Calendar: a tour of the art and architecture of 
Asheron's Call
http://www.lulu.com/content/77264