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 (bro
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 co
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 effo
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 co
> 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
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
hich, 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 value
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
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
uneet 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 &q
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
>
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 di
t 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: sq
- 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
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 sale
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
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."
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 CO
MAIL 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
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
- 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 m
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.
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 cl
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
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 abou
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 wit
- 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?
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 totali
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,
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 wit
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 sal
> 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 su
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 inconsis
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 co
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).
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 possibi
> 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 appro
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
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=#
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 i
> 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 *so
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 Octo
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
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' a
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 everyth
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
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 cou
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
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
> 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
51 matches
Mail list logo
