Skip site navigation (1) Skip section navigation (2)

Re: BUG #3958: Self-Join Group-By Clause Produces Incorrect Results

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 (view raw or flat)
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

In response to

pgsql-bugs by date

Next:From: Marc MunroDate: 2008-02-13 18:55:52
Subject: psql malloc problem
Previous:From: Rodriguez FernandoDate: 2008-02-13 15:02:34
Subject: Re: BUG #3959: Huge calculation error

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group