insert into.. (select count(*),sum(xx) from <> where <>) giving wrong aggregate values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Arun Srinivasan - 26 Feb 2008 15:42 GMT

Hi
I have a sql that I use within an SP..
INSERT INTO xx.yyyy( c1,cnt1,sum1 )
(SELECT COUNT(*), sum(yyy) FROM xx.yyyy_dtl WHERE<> GROUP BY <>)

Every time I run this, I get different values for the cnt1 variable. I
am able to get the coorect values on execution of the select statement
alone, thro my DB client, but when I try to insert, the count is just
wrong. I did change it to use an order by clause,

INSERT INTO xx.yyyy( c1,cnt1,sum1 ) SELECT COUNT(*), sum(yyy) FROM
xx.yyyy_dtl WHERE <> GROUP BY <> order by c1;
and it works fine. I can think of only one reason this could happen,
buffers that this agent uses, and the aggregation being done on the
buffered rows only, instead of the full table. I did google about this
but in vein. If this has anything to do with basic SQL programming or
other fundamental concepts, please let me know. I am just intrigued.

Yours confused,
Arun

Reply to this Message

Serge Rielau - 26 Feb 2008 21:27 GMT

> Hi
> I have a sql that I use within an SP..
> INSERT INTO xx.yyyy( c1,cnt1,sum1 )
> (SELECT COUNT(*), sum(yyy) FROM xx.yyyy_dtl WHERE<> GROUP BY <>)

Your number of columns doesn't match up!
There actually has been an APAR around DB2 accepting mismatched column
lists on INSERT with the obvious interesting side-effects.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Arun Srinivasan - 27 Feb 2008 16:15 GMT

Hi Sarge
Thanks for the reply, but the issue is not the column list, but
wrong values being populated in the insert .. select statement for the
column with aggregate functions, count(*) and sum(*).. I found
something in sqlserver forum along lines of these violating 1st nf in
db, and that we should do using
insert into <> ( select * from table ( select .... count(*),sum(*)) as
x )
Hope you got what I am referring. Nothing pertaining to syntax..

Need help.
Arun

Reply to this Message

Serge Rielau - 27 Feb 2008 21:22 GMT

> Hi Sarge
> Thanks for the reply, but the issue is not the column list, but
[quoted text clipped - 5 lines]
> x )
> Hope you got what I am referring. Nothing pertaining to syntax..

As I said there is an APAR. Assuming that your example is correct(!)
Then COUNT() goes into c1, SUM() goes into count and whatever you
grouped by likely ended up in sum (!)
I can see how these are wrong results ....

If your example was not accurate please post an accurate example so we
can help you.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Arun Srinivasan - 27 Feb 2008 21:56 GMT

Example :

1 . insert into t1 (c1,c2,c3) (select b1, count(b2), sum(b3) from t2
group by b4,b5);
- this gives wrong values
2. insert into t2 (d1,d2,d3) (select * from table (select b1,
count(b2), sum(b3) from t2 group by b4,b5) as x ) this gives correct
values for the aggregate function colums.

Basically the select is the same statement, but written in a different
way, in (2) we are building the temp table and just throwing
everything in it to the t2, while in (1) we are inserting as we
calculate the count and sum functions.

This does not happen all the time, just a few times, thus we are
finding it a non-deterministic calculation that the db2 engine does..

Reply to this Message

The Boss - 27 Feb 2008 22:39 GMT

> Example :
>
[quoted text clipped - 12 lines]
> This does not happen all the time, just a few times, thus we are
> finding it a non-deterministic calculation that the db2 engine does..

ALL the examples you provided until now are syntactically wrong.
Please, please, provide your EXACT real queries.
It would also be nice to see your table definitions.

Signature

Jeroen

Reply to this Message

Serge Rielau - 28 Feb 2008 00:11 GMT

>> This does not happen all the time, just a few times, thus we are
>> finding it a non-deterministic calculation that the db2 engine does..
>
> ALL the examples you provided until now are syntactically wrong.
> Please, please, provide your EXACT real queries.
> It would also be nice to see your table definitions.

Indeed. Maybe also time to tell the exact version platform of DB2.
Here is what happens on DB2 9.5 (and I'm confident on all DB2 for lUW
versions):
db2 => create table t(c1 int, c2 int, c3 int);
DB20000I The SQL command completed successfully.
db2 => create table s(c1 int, c2 int, c3 int, c4 int, c5 int);
DB20000I The SQL command completed successfully.
db2 => insert into t select c1, count(*), sum(c2) from s group by c3,
c4, c5;
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0119N An expression starting with "C1" specified in a SELECT clause,
HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or
it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column
function and no GROUP BY clause is specified. SQLSTATE=42803

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message