RE: [sqlite] SUM and NULL values

[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

[sqlite] Re: Need help with a query

[Igor Tandetnik]

Nemanja Corlija wrote:

Here's an example table:
CREATE TABLE t1 (fname TEXT, vers TEXT, chng INT, UNIQUE(fname,vers));
INSERT INTO t1 VALUES('file1', '1', 0);
INSERT INTO t1 VALUES('file1', '2', 1);
INSERT INTO t1 VALUES('file1', '3', 2);
INSERT INTO t1 VALUES('file2', '1', 0);

SELECT fname, MAX(vers), chng FROM t1
WHERE fname LIKE 'file%'
GROUP BY fname

Above query returns:

fname  max(vers)  chng
--
file2 10
file1 30

fname and vers returned are OK, but chng is not. I need to have latest
chng in each group.
How can I get chng form the row with highest rowid in the group?


Something like this perhaps?

select fname, vers, chng
from t1 first
where fname like 'file%' and not exists
   (select * from t1 second
where first.fname=second.fname and first.vers < second.vers)

Igor Tandetnik 

Re: [sqlite] SUM and NULL values

[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 : :
+---+-+

[sqlite] Need help with a query

[Nemanja Corlija]

Hi all,

I guess this isn't really all that complex, but I just can't think of
a query that does what I need.

Here's an example table:
CREATE TABLE t1 (fname TEXT, vers TEXT, chng INT, UNIQUE(fname,vers));
INSERT INTO t1 VALUES('file1', '1', 0);
INSERT INTO t1 VALUES('file1', '2', 1);
INSERT INTO t1 VALUES('file1', '3', 2);
INSERT INTO t1 VALUES('file2', '1', 0);

SELECT fname, MAX(vers), chng FROM t1
WHERE fname LIKE 'file%' 
GROUP BY fname  

Above query returns:

fname  max(vers)  chng
--
file2 10
file1 30

fname and vers returned are OK, but chng is not. I need to have latest
chng in each group.
How can I get chng form the row with highest rowid in the group?
Can I even influence the order of records in a group?
AFAIK ORDER BY is done after GROUP BY so it can't be used here. HAVING
operates on the group but how do I put it to use here?

This needs to work on SQLite 2.

Thanks

-- 
Nemanja Corlija <[EMAIL PROTECTED]>

Re: [sqlite] SUM and NULL values

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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] 2.8.16

[Ted Unangst]

Mark Drago wrote:


I don't seem to have the 'md5sum' program installed on the mac that I
have here, but getting the md5sum on linux returns the following.  So,
if you have some means of getting the md5sum of the file, make sure it
matches this:

9c79b461ff30240a6f9d70dd67f8faea  sqlite-2.8.16.tar.gz


md5 should be available, no?


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

Re: [sqlite] need to write during a long read

[D. Richard Hipp]

On Thu, 2005-09-08 at 10:48 -0400, Mark Drago wrote:
> However, it seems that for every rollback that I do there is a file left
> in the directory with the databases.  I have 30-something files named
> like the following: 'ame_log.db-mj0E2E1262'.  ame_log.db is the filename
> of the main log database.  The contents of the file are the full path to
> the main log database's journal and the full path to the attached queue
> database's journal.  Should something be getting rid of these files?
> Has anyone else seen this?
> 

Those are the "master journal" files.  They are used to make
sure that commits to multiple databases occurs atomically.
They should be deleted automatically.  I do not know why they
are not being removed for you.  I will look into it.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] SUM and NULL values

[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] 2.8.16

[Puneet Kishor]

On Sep 8, 2005, at 1:24 PM, Mark Drago wrote:


Richard,

You can use 'curl' to download the file instead of wget.  So, run the
following command instead:

curl -o sqlite-2.8.16.tar.gz http://www.sqlite.org/sqlite-2.8.16.tar.gz


wget is not available by default on Panther. On the other hand, as Mark 
wrote above, curl -o works just fine.




I don't seem to have the 'md5sum' program installed on the mac that I
have here, but getting the md5sum on linux returns the following.  So,
if you have some means of getting the md5sum of the file, make sure it
matches this:

9c79b461ff30240a6f9d70dd67f8faea  sqlite-2.8.16.tar.gz


md5app available at http://www.enigmarelle.com/md5app.py can be used to 
check the integrity of the downloaded file.





If you can't get the md5sum, at the very least make sure that the file
size is exactly 981834 bytes.

Mark.

On Thu, 2005-09-08 at 13:57 -0400, Richard Nagle wrote:

On September 08 2005, Cam Crews <[EMAIL PROTECTED]> wrote:

Ok...  try downloading the sqlite source again.  Maybe the mac 
browser

isn't downloading correctly or your unzip utility is corrupting
things.  Try performing these operations (downloading and unzipping)
through the command line:
--
wget http://www.sqlite.org/sqlite-2.8.16.tar.gz
gunzip sqlite-2.8.16.tar.gz
tar -xvf sqlite-2.8.16.tar
cd sqlite-2.8.16
su
--

now what happens when you configure/install from here?




Umm.
Welcome to Darwin!
FastMAC:~ rn$ man wget
No manual entry for wget
FastMAC:~ rn$ wget --version
-bash: wget: command not found
FastMAC:~ rn$

I don't have a wget command..

Richard




--
Puneet Kishor

Re: [sqlite] 2.8.16

[Richard Nagle]

Nope,
done that, still the same errors.

Have another person, whom is trying to figure this out as well,
in fact, he thinks, something in my shell is mess up..


> 

Wow, your results are completely different. When the shell trys to
read the here-document, it gets the garbage.
So the bug is in the shell reading the doc. How odd.

What locale setting do you use?

((US English))   / And what keyboard setting etc...

A
===

With that in mind, I very confused, on what he_l is going,
since I wipe and re-installed twice.

is a corrupt file on MacOS install disk?
grasping at straws.

Richard

Re: [sqlite] 2.8.16

[Mark Drago]

Richard,

You can use 'curl' to download the file instead of wget.  So, run the
following command instead:

curl -o sqlite-2.8.16.tar.gz http://www.sqlite.org/sqlite-2.8.16.tar.gz

I don't seem to have the 'md5sum' program installed on the mac that I
have here, but getting the md5sum on linux returns the following.  So,
if you have some means of getting the md5sum of the file, make sure it
matches this:

9c79b461ff30240a6f9d70dd67f8faea  sqlite-2.8.16.tar.gz

If you can't get the md5sum, at the very least make sure that the file
size is exactly 981834 bytes.

Mark.

On Thu, 2005-09-08 at 13:57 -0400, Richard Nagle wrote:
> On September 08 2005, Cam Crews <[EMAIL PROTECTED]> wrote:
> 
> >Ok...  try downloading the sqlite source again.  Maybe the mac browser
> >isn't downloading correctly or your unzip utility is corrupting
> >things.  Try performing these operations (downloading and unzipping)
> >through the command line:
> >--
> >wget http://www.sqlite.org/sqlite-2.8.16.tar.gz
> >gunzip sqlite-2.8.16.tar.gz
> >tar -xvf sqlite-2.8.16.tar
> >cd sqlite-2.8.16
> >su
> >--
> >
> >now what happens when you configure/install from here?  
> >
> 
> 
> Umm.
> Welcome to Darwin!
> FastMAC:~ rn$ man wget
> No manual entry for wget
> FastMAC:~ rn$ wget --version
> -bash: wget: command not found
> FastMAC:~ rn$ 
> 
> I don't have a wget command..
> 
> Richard
> 
> 


signature.asc
Description: This is a digitally signed message part

Re: [sqlite] 2.8.16

[Richard Nagle]

On September 08 2005, Cam Crews <[EMAIL PROTECTED]> wrote:

>Ok...  try downloading the sqlite source again.  Maybe the mac browser
>isn't downloading correctly or your unzip utility is corrupting
>things.  Try performing these operations (downloading and unzipping)
>through the command line:
>--
>wget http://www.sqlite.org/sqlite-2.8.16.tar.gz
>gunzip sqlite-2.8.16.tar.gz
>tar -xvf sqlite-2.8.16.tar
>cd sqlite-2.8.16
>su
>--
>
>now what happens when you configure/install from here?
>


Umm.
Welcome to Darwin!
FastMAC:~ rn$ man wget
No manual entry for wget
FastMAC:~ rn$ wget --version
-bash: wget: command not found
FastMAC:~ rn$

I don't have a wget command..

Richard

Re: [sqlite] SUM and NULL values

[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

[sqlite] SUM and NULL values

[Will Leshner]

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;

Re: [sqlite] 2.8.16

[Jens Miltner]

How did you download the archive? Using which client?
I never had any problems using the source archives (may not have used  
that exact version, but I never had any problems using the .tar.gz  
archives on Mac OS X...)


Make sure you don't use any older version of Stuffit or the like to  
extract the archives!




Am 8.9.05 um 18:46 schrieb Cam Crews:


Ok...  try downloading the sqlite source again.  Maybe the mac browser
isn't downloading correctly or your unzip utility is corrupting
things.  Try performing these operations (downloading and unzipping)
through the command line:
--
wget http://www.sqlite.org/sqlite-2.8.16.tar.gz
gunzip sqlite-2.8.16.tar.gz
tar -xvf sqlite-2.8.16.tar
cd sqlite-2.8.16
su
--

now what happens when you configure/install from here?


On 9/8/05, Richard Nagle <[EMAIL PROTECTED]> wrote:



On September 08 2005, Cam Crews <[EMAIL PROTECTED]> wrote:



So, its something with the C compiler...



Well, you can test your compiler pretty easily..  Are you able to
compile anything else?  At minimum, try compiling hello world first:

create a file test.c :
---
#include 

int main( ) {
 printf("oh my, 1 step closer to using sqlite\n");
 return 0;
}

then type from the command line:

gcc test.c -o sillymac
chmod +x sillymac
./sillymac

---
if that works, your compiler is probably ok.  Next try downloading a
sample project from freshmeat.net, and see if you can configure &
compile one of those...
 I'll echo the suggestion that your source download is likely
corrupt.  Or your data is being corrupted through the unzipping
process.



Cam,

It worked:

Welcome to Darwin!
FastMAC:~ rn$ ls
Desktop Library Movies  Pictures 
Sites   test.c
Documents   MailMusic   Public   
ash tinySQL2

FastMAC:~ rn$ gcc test.c -o sillymac
FastMAC:~ rn$ chmod +x sillymac
FastMAC:~ rn$ ./sillymac
oh my, 1 step closer to using sqlite
FastMAC:~ rn$

Richard

Re: [sqlite] 2.8.16

[Cam Crews]

Ok...  try downloading the sqlite source again.  Maybe the mac browser
isn't downloading correctly or your unzip utility is corrupting
things.  Try performing these operations (downloading and unzipping)
through the command line:
--
wget http://www.sqlite.org/sqlite-2.8.16.tar.gz
gunzip sqlite-2.8.16.tar.gz
tar -xvf sqlite-2.8.16.tar
cd sqlite-2.8.16
su
--

now what happens when you configure/install from here?  


On 9/8/05, Richard Nagle <[EMAIL PROTECTED]> wrote:
> 
> On September 08 2005, Cam Crews <[EMAIL PROTECTED]> wrote:
> 
> >> So, its something with the C compiler...
> 
> Well, you can test your compiler pretty easily..  Are you able to
> compile anything else?  At minimum, try compiling hello world first:
> 
> create a file test.c :
> ---
> #include 
> 
> int main( ) {
>  printf("oh my, 1 step closer to using sqlite\n");
>  return 0;
> }
> 
> then type from the command line:
> 
> gcc test.c -o sillymac
> chmod +x sillymac
> ./sillymac
> 
> ---
> if that works, your compiler is probably ok.  Next try downloading a
> sample project from freshmeat.net, and see if you can configure &
> compile one of those...
>  I'll echo the suggestion that your source download is likely
> corrupt.  Or your data is being corrupted through the unzipping
> process.
> 
> 
> 
> Cam,
> 
> It worked:
> 
> Welcome to Darwin!
> FastMAC:~ rn$ ls
> Desktop Library Movies  PicturesSites 
>   test.c
> Documents   MailMusic   Public  ash   
>   tinySQL2
> FastMAC:~ rn$ gcc test.c -o sillymac
> FastMAC:~ rn$ chmod +x sillymac
> FastMAC:~ rn$ ./sillymac
> oh my, 1 step closer to using sqlite
> FastMAC:~ rn$
> 
> Richard
> 
> 
>

Re: [sqlite] 2.8.16

[Richard Nagle]

On September 08 2005, Cam Crews <[EMAIL PROTECTED]> wrote:

>> So, its something with the C compiler...

Well, you can test your compiler pretty easily..  Are you able to
compile anything else?  At minimum, try compiling hello world first:

create a file test.c :
---
#include 

int main( ) {
  printf("oh my, 1 step closer to using sqlite\n");
  return 0;
}

then type from the command line:

gcc test.c -o sillymac
chmod +x sillymac
./sillymac

---
if that works, your compiler is probably ok.  Next try downloading a
sample project from freshmeat.net, and see if you can configure &
compile one of those...
  I'll echo the suggestion that your source download is likely
corrupt.  Or your data is being corrupted through the unzipping
process.



Cam,

It worked:

Welcome to Darwin!
FastMAC:~ rn$ ls
Desktop Library Movies  PicturesSites   
test.c
Documents   MailMusic   Public  ash 
tinySQL2
FastMAC:~ rn$ gcc test.c -o sillymac
FastMAC:~ rn$ chmod +x sillymac
FastMAC:~ rn$ ./sillymac
oh my, 1 step closer to using sqlite
FastMAC:~ rn$

Richard

Re: [sqlite] 2.8.16

[Cam Crews]

> So, its something with the C compiler...

Well, you can test your compiler pretty easily..  Are you able to
compile anything else?  At minimum, try compiling hello world first:

create a file test.c :
---
#include 

int main( ) {
  printf("oh my, 1 step closer to using sqlite\n");
  return 0;
}

then type from the command line:

gcc test.c -o sillymac
chmod +x sillymac
./sillymac

---
if that works, your compiler is probably ok.  Next try downloading a
sample project from freshmeat.net, and see if you can configure &
compile one of those...
  I'll echo the suggestion that your source download is likely
corrupt.  Or your data is being corrupted through the unzipping
process.


On 9/8/05, Richard Nagle <[EMAIL PROTECTED]> wrote:
> 
> Downloaded this, version, and got the same kind of output.
> 
> VÒ†—jx?Âê#MÉfl#?sjKöyæ9>[EMAIL 
> PROTECTED]…ºhUˆZ£/SŸ?÷é(c)='¿l?/ëÿ:˜ìRb}öVÙ¥"(Å[¿<.Q`ƒ
hYF¢+›¨÷ùáü!"ÀLOÎ
> í k?ïë¢xeÃb?e·Œfi,Œ[\¡"N¨>=
> Ã?Ê3}ì?âò<=În'T£?å.
> Â1 ÜÙ
> ˜ÅÕô/+yÑ]Ò{Ÿ¨câ´qîÔhÏ!=Na"Z
> 8:B¶áâ?è+#W>=rm?pVÒ†—jx?Âê#MÉfl#?sjKöyæ9>[EMAIL 
> PROTECTED]…ºhUˆZ£/SŸ?÷é(c)='¿l?/ëÿ:˜ìRb}öVÙ¥"(Å[¿<.Q`ƒ
hYF¢+›¨÷ùáü!"ÀLOÎ
>   í k?ïë¢xeÃb?e·Œfi,Œ[\¡"N¨>=
> Ã?Ê3}ì?âò<=În'T£?å.
> Â1 ÜÙ
> ˜ÅÕô/+yÑ]Ò{Ÿ¨câ´qîÔhÏ!=Na"Z
> 8:B¶áâ?è+#W>=rm?pÿ(c))ÂçÚ:ZD'n¯d?»£üs™V'¡—Î+"n0(r)…hÉLÌpóI(c)úUãé3S¸‡zugF¶{/;Û,Ü{9Ãë¬ØXÏ"ÓN±Ëôã…£âõåëÆHê.:?òÃäîJ€1÷T›¶?™?ä?k„õQ?!=Ö9X
> !=WøÃ`›\Ÿ?v<=UíYí?óƒ8>ƒç‚=ü:"…S [EMAIL 
> PROTECTED]™W3ÑÚxl[MúòçÅN&ü40–ú
> ‹?6G}Á?à‹¯D"y,ÿÌ?C?6?`¸
> :˜üqÂ7¡?H?…Èø¶í‹&õ>J(t
r%»ZÅ[ÑL?í?èÜdaJ\1ÚpA*ߺ?
> ±ùŸ´´(7ä?eã°ëweiØiˆ"[EMAIL PROTECTED]
> Wé-Ö\–1wñÙÔWF¯»!?è2øk)ko"]À(}ÒP6È3<[fi4õ˜-…T±_V"æ(<=âP«7ªB>=·b¶?)>v`¢yø+O_›+]ÇÍô?ÿuº¢/ìç'<=…
> jOÖ$º\'?YÂîJ™n?Ò·æ¿´?㯫Çp<ñ'ã?Z{m'Gé樖–tµT¯0ô.Å8èœ~Ê¿H?XåZê3'zâ>=
>  l'›?ütn(r)f„ tflgÛ}MDÄ¢Éq¯ÓB§ÙÆIxJ'à´´Û0ëÍ'ø
> x>=wl$Æä'uÀx'iض>=mØŒsî/?ØukjÊSÙã?´2+i¸HÍ??¯\\rÖ%¯6aàÍLO˜—ñ
>kÓÏÙ>=É<Ï›ã¨xDúf¯e+8<=%?°Ü÷Ï5´b`+5Æq#åÂDCÖw¨ó—M?E#ä?
> &Çm?QGíPATH: /bin
> PATH: /sbin
> PATH: /usr/bin
> PATH: /usr/sbin
> VÒ†—jx?Âê#MÉfl#?sjKöyæ9>[EMAIL 
> PROTECTED]…ºhUˆZ£/SŸ?÷éconfigure:1605: checking build system type
> configure:1623: result: powerpc-apple-darwin7.9.0
> configure:1631: checking host system type
> configure:1645: result: powerpc-apple-darwin7.9.0
> configure:1699: checking for gcc
> configure:1715: found /usr/bin/gcc
> configure:1725: result: gcc
> configure:1969: checking for C compiler version
> configure:1972: gcc --version &5
> gcc (GCC) 3.3 20030304 (Apple Computer, Inc. build 1671)
> Copyright (C) 2002 Free Software Foundation, Inc.
> This is free software; see the source for copying conditions.  There is NO
> warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
> 
> configure:1975: $? = 0
> configure:1977: gcc -v &5
> Reading specs from /usr/libexec/gcc/darwin/ppc/3.3/specs
> Thread model: posix
> gcc version 3.3 20030304 (Apple Computer, Inc. build 1671)
> configure:1980: $? = 0
> configure:1982: gcc -V &5
> gcc: `-V' option must have argument
> configure:1985: $? = 1
> configure:2008: checking for C compiler default output file name
> configure:2011: gccconftest.c  >&5
> conftest.c:1: error: stray '\361' in program
> conftest.c:1: error: stray '\240' in program
> conftest.c:1: error: stray '\321' in program
> conftest.c:1: error: parse error before "j"
> conftest.c:1: error: stray '\22' in program
> conftest.c:1: error: stray '\270' in program
> conftest.c:1: error: stray '\345' in program
> conftest.c:1: error: stray '\33' in program
> conftest.c:1: error: stray '\220' in program
> --
> 
> So, its something with the C compiler...
> or language problem..
> 
> Richard
> 
> 
> 
> 
> 
>

Re: [sqlite] need to write during a long read

[Mark Drago]

On Wed, 2005-09-07 at 17:20 -0400, Mark Drago wrote:
> On Tue, 2005-09-06 at 16:07 -0400, D. Richard Hipp wrote:
> > On Tue, 2005-09-06 at 15:49 -0400, Mark Drago wrote:
> > 
> > > 2. I could continue to write to the database in the single thread, but
> > > if the write fails, add the data to a queue and continue.  Then, when
> > > another piece of data has to be logged, and it succeeds, empty the queue
> > > and write all of the data from it into the sqlite DB. 
> > 
> > This is what I would do.  Except I would make the queue a
> > separate SQLite database which was connected to the primary
> > database using ATTACH.
> > 
> > Suppose the "queue" database is named "pending.db".  Then
> > when you open the primary database always immediately do
> > this:
> > 
> > ATTACH DATABASE 'pending.db' AS pending;
> > 
> > Then when you want to make a log entry do the following
> > statements:
> > 
> >INSERT INTO pending.log VALUES(...);
> >INSERT INTO main.log SELECT * FROM pending.log;
> >DELETE FROM pending.log;
> > 
> > When doing the above, abort after the first failure.
> > If the database is locked then the second statement will
> > fail, the DELETE will never occur and information will
> > accumulate in the "pending" database.  If the second
> > statement succeeds, then the information is subsequently
> > deleted from the pending database.
> > 
> > If you really want to make sure that the transfer from
> > pending to main is atomic, enclose the last two statements
> > in a transaction.

> The problem I'm seeing is that (I'm half guessing) after I run a
> rollback, when I try and run 'begin transaction' I get: 'cannot start a
> transaction within a transaction'.  As far as I can tell there is no way
> to get out of this part of my code without running either a rollback or
> a commit.  I've executed 'pragma synchronous=off' on this database as
> I'll be writing to it very often and the data isn't life-or-death
> important.  I don't think this should impact anything, but I figured I
> would share.  Any ideas?

Alright, I've solved this problem.  It makes perfect sense in hindsight.
Basically, the only part of this thing that will likely fail because the
database is busy is the 'commit transaction' statement.  However, I was
never checking to see if that failed.  Now, if the commit fails I run a
'rollback' (which shouldn't fail b/c the DB is busy) and the queue seems
to work as expected.

However, it seems that for every rollback that I do there is a file left
in the directory with the databases.  I have 30-something files named
like the following: 'ame_log.db-mj0E2E1262'.  ame_log.db is the filename
of the main log database.  The contents of the file are the full path to
the main log database's journal and the full path to the attached queue
database's journal.  Should something be getting rid of these files?
Has anyone else seen this?

Mark.


signature.asc
Description: This is a digitally signed message part

[sqlite] 2.8.16

[Richard Nagle]

Downloaded this, version, and got the same kind of output.

V҆—jx?Âê#MÉfl#?sjKöyæ9>[EMAIL 
PROTECTED]…ºhUˆZ£/SŸ?÷é©='¿l?/ëÿ:˜ìRb}öVÙ¥"(Å[¿<.Q`ƒ
hYF¢+›¨÷ùáü!"ÀLOÎ
 ík?ïë¢xeÃb?e·Œfi,Œ[\¡"N¨>=
Ã?Ê3}ì?âò<=În'T£?å.
Â1ÜÙ
˜ÅÕô/+yÑ]Ò{Ÿ¨câ´qîÔhÏ!=Na"Z
8:B¶áâ?è+#W>=rm?pV҆—jx?Âê#MÉfl#?sjKöyæ9>[EMAIL 
PROTECTED]…ºhUˆZ£/SŸ?÷é©='¿l?/ëÿ:˜ìRb}öVÙ¥"(Å[¿<.Q`ƒ
hYF¢+›¨÷ùáü!"ÀLOÎ
   ík?ïë¢xeÃb?e·Œfi,Œ[\¡"N¨>=
Ã?Ê3}ì?âò<=În'T£?å.
Â1ÜÙ
˜ÅÕô/+yÑ]Ò{Ÿ¨câ´qîÔhÏ!=Na"Z
8:B¶áâ?è+#W>=rm?pÿ©)ÂçÚ:ZD'n¯d?»£üs™V'¡—Î+"n0®…hÉLÌpóI©úUãé3S¸‡zugF¶{/;Û,Ü{9Ãë¬ØXÏ"ÓN±Ëôã…£âõåëÆHê.:?òÃäîJ€1÷T›¶?™?ä?k„õQ?!=Ö9X
!=WøÃ`›\Ÿ?v<=UíYí?óƒ8>ƒç‚=ü:"…S [EMAIL 
PROTECTED]™W3ÑÚxl[MúòçÅN&ü40–ú
‹?6G}Á?à‹¯D"y,ÿÌ?C?6?`¸
:˜üqÂ7¡?H?…Èø¶í‹&õ>J(t
r%»ZÅ[ÑL?í?èÜdaJ\1ÚpA*ߺ?
±ùŸ´´(7ä?eã°ëweiØiˆ"[EMAIL PROTECTED]
Wé-Ö\–1wñÙÔWF¯»!?è2øk)ko"]À(}ÒP6È3<[fi4õ˜-…T±_V"æ(<=âP«7ªB>=·b¶?)>v` ¢yø+O_›+]ÇÍô?ÿuº¢/ìç'<=…
jOÖ$º\'?YÂîJ™n?Ò·æ¿´?㯫Çp<ñ'ã?Z{m'Gé樖–tµT¯0ô.Å8èœ~Ê¿H?XåZê3'zâ>=l'›?ütn®f„
tflgÛ}MDÄ¢Éq¯ÓB§ÙÆIxJ'à´´Û0ëÍ'ø
x>=wl$Æä'uÀx'iض>=m،sî/?ØukjÊSÙã?´2+i¸HÍ??¯\\rÖ%¯6aàÍLO˜—ñ
   kÓÏÙ>=É<ϛã¨xDúf¯e+8<=%?°Ü÷Ï5´b`+5Æq#åÂDCÖw¨ó—M?E#ä?
&Çm?QGíPATH: /bin
PATH: /sbin
PATH: /usr/bin
PATH: /usr/sbin
V҆—jx?Âê#MÉfl#?sjKöyæ9>[EMAIL 
PROTECTED]…ºhUˆZ£/SŸ?÷éconfigure:1605: checking build system type
configure:1623: result: powerpc-apple-darwin7.9.0
configure:1631: checking host system type
configure:1645: result: powerpc-apple-darwin7.9.0
configure:1699: checking for gcc
configure:1715: found /usr/bin/gcc
configure:1725: result: gcc
configure:1969: checking for C compiler version
configure:1972: gcc --version &5
gcc (GCC) 3.3 20030304 (Apple Computer, Inc. build 1671)
Copyright (C) 2002 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

configure:1975: $? = 0
configure:1977: gcc -v &5
Reading specs from /usr/libexec/gcc/darwin/ppc/3.3/specs
Thread model: posix
gcc version 3.3 20030304 (Apple Computer, Inc. build 1671)
configure:1980: $? = 0
configure:1982: gcc -V &5
gcc: `-V' option must have argument
configure:1985: $? = 1
configure:2008: checking for C compiler default output file name
configure:2011: gccconftest.c  >&5
conftest.c:1: error: stray '\361' in program
conftest.c:1: error: stray '\240' in program
conftest.c:1: error: stray '\321' in program
conftest.c:1: error: parse error before "j"
conftest.c:1: error: stray '\22' in program
conftest.c:1: error: stray '\270' in program
conftest.c:1: error: stray '\345' in program
conftest.c:1: error: stray '\33' in program
conftest.c:1: error: stray '\220' in program
--

So, its something with the C compiler...
or language problem..

Richard

Re: [sqlite] Error (config.log) while compiling SQLite

[Jay Sprenkle]

On 9/8/05, Richard Nagle <[EMAIL PROTECTED]> wrote:
> 
> 
> 
> >
> >you have major problems. As someone else suggested, could you have
> >possibly downloaded the source wrong... could something have gotten
> >munged up? All the above gobbledegook is binary stuff.




Or bad RAM or hard disk in your computer.

Re: [sqlite] Error (config.log) while compiling SQLite

[Richard Nagle]

>
>you have major problems. As someone else suggested, could you have
>possibly downloaded the source wrong... could something have gotten
>munged up? All the above gobbledegook is binary stuff.
>
>By the way, wiping the hard drive and reinstalling everything is a
>waste of time. That is no way to solve the problems. You should really
>try and get to the bottom of what is going on.
>
>Have you been able to download and build any other software? Why don't
>you Google for Readline and download and build that. See if that works
>(Readline makes working on the command line easier...).
>
>Also, how did you unpack the tar/gzipped source? Maybe something got
>corrupted there.
>
>--
>Puneet Kishor
>
>


As stated before,
got this even with darwinports installed.
Yes, I try a few others...

Richard

RE: [sqlite] Error (config.log) while compiling SQLite

[Reid Thompson]

Richard Nagle wrote:
>> you didn't happen to ftp any files in the incorrect mode did you?
>> ascii rather than binary perhaps for the tar/tar.gz file?
>> 
> 
> Just downloaded the source, from sqlite.org
> and that it. why?
> 
> -Rick

downloading a binary file ( .tar .tgz, .bz , etc ) in ascii mode will
corrupt the file

reid

Re: [sqlite] sqlite error output

[Jay Sprenkle]

On 9/7/05, Alexander van der Merwe <[EMAIL PROTECTED]> wrote:
> 
> Hi,
> 
> I have a huge (> 40 000 lines) insert-script I want to run.
> 
> Some of the lines may have something wrong with them, and I want to
> check if there are are any errors on importing the data.
> 
> Is there a way to tell SQLite only to output the errors, instead of the
> 'successful' inserts?
> 
> Thanx,
> 


Save all the output and use grep to find the errors.

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

Re: [sqlite] need to write during a long read

[Jay Sprenkle]

On 9/7/05, Mark Drago <[EMAIL PROTECTED]> wrote:
> 
> 
> The problem I'm seeing is that (I'm half guessing) after I run a
> rollback, when I try and run 'begin transaction' I get: 'cannot start a
> transaction within a transaction'. As far as I can tell there is no way
> to get out of this part of my code without running either a rollback or
> a commit. I've executed 'pragma synchronous=off' on this database as
> I'll be writing to it very often and the data isn't life-or-death
> important. I don't think this should impact anything, but I figured I
> would share. Any ideas?


This is just a WAG but:

You said you checked that the transaction was always closed. You are doing a 
commit if it
succeeds right? Does the transaction perhaps not lock/unlock both databases?

If I were doing it I would store pending log messages in a queue in memory, 
or
design my application to wait and retry a small number of times. On my last
project I went by the rule "if you can't commit changes within 30 seconds
something is seriously broken". I would write to syslog if it got broken and 
abort.

Re: [sqlite] Question about SQLite and spatial data

[Noel Frankinet]

Rajan, Vivek K wrote:


I have a need to store spatial and connectivity data (think verilog
netlist with x, y coordinate information) in a persistence on-disk file.
I am looking at different SQL-based databases and am wondering if anyone
has recommendations/opinions on how good SQLite is for:

 - Storing spatial data. An examples, documentation and support links

 - Performing spatial queries and indexing for very large hierarchical
data set



Please advice.



Rajan


 




No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.10.18/91 - Release Date: 6/09/2005
 


Hello,

I do it. I have a collection of graphical classes (line, polylines, 
) than can be saved and retrieved from sqlite. I use the 2.8. The 
shape are saved as blob encoded in text. When drawing or selecting, 
shapes are pulled from the Geometry column via sqlite call-back mechanism.
Shape class have a static factory method that takes the blob pulled from 
db as parameter and return a well formed object.


The table schema is made from a primary key , 4 int for a bounding box, 
a the geometry blob.
I try now to make a spatial index (quadtree or rtree) but I already have 
good performance by selecting bounding box.


Best wishes

--
Noël Frankinet
Gistek Software SA
http://www.gistek.net