From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Heikki Linnakangas" <heikki(at)enterprisedb(dot)com> |
Cc: | "Marcus Torres" <marcsf23(at)yahoo(dot)com>, pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #3958: Self-Join Group-By Clause Produces Incorrect Results |
Date: | 2008-02-13 15:40:32 |
Message-ID: | 15115.1202917232@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
"Heikki Linnakangas" <heikki(at)enterprisedb(dot)com> writes:
> Marcus Torres wrote:
>> I wrote a simple self-join query to sum the transaction count of different
>> types of records in a audit table and the result set for the different sum
>> totals was the same which is incorrect.
> Looks perfectly correct to me.
Me too. The underlying data before grouping/aggregation is
regression=# select
A1.AUDIT_DATE, P.CONTENT_POLICY_NAME, A1.TXN_COUNT, A2.TXN_COUNT
FROM T_AUDIT A1,
T_AUDIT A2,
T_POLICY P
WHERE P.ID = A1.POLICY_ID
AND P.ID = A2.POLICY_ID
AND A1.POLICY_ID = A2.POLICY_ID
AND A1.AUDIT_DATE = A2.AUDIT_DATE
AND A1.AUDIT_TYPE_CODE = 'CONTENT_1'
AND A2.AUDIT_TYPE_CODE = 'CONTENT_2';
audit_date | content_policy_name | txn_count | txn_count
------------+---------------------+-----------+-----------
2008-01-01 | TEST POLICY | 1 | 1
2008-01-01 | TEST POLICY | 1 | 1
2008-01-01 | TEST POLICY | 1 | 1
2008-01-01 | TEST POLICY | 1 | 1
2008-01-01 | TEST POLICY | 1 | 1
2008-01-01 | TEST POLICY | 1 | 1
2008-01-01 | TEST POLICY | 1 | 1
2008-01-01 | TEST POLICY | 1 | 1
2008-01-01 | TEST POLICY | 1 | 1
2008-01-01 | TEST POLICY | 1 | 1
(10 rows)
from which it's clear that given all ones in txn_count, the sums *must*
be the same because they're taken over the same number of rows.
I suspect what the OP needs is two separate queries (perhaps union'ed
together) not a self-join.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Marc Munro | 2008-02-13 18:55:52 | psql malloc problem |
Previous Message | Rodriguez Fernando | 2008-02-13 15:02:34 | Re: BUG #3959: Huge calculation error |