The following bug has been logged online:
Bug reference: 3914
Logged by: Marcus Torres
Email address: marcsf23@yahoo.com
PostgreSQL version: 8.19
Operating system: Linux - Ubuntu
Description: Self-Join Group-By Clause Produces Incorrect Results
Details:
I wrote a simply 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 highly incorrect. If I copy the same records
from t_audit to another table and run the same query which is not a
self-join, it works....very strange
Query:
select a1.audit_julian_date AS DATE,
t.cont_pol_name as CONT_POLICY,
sum(a1.txn_count) as CONTENT_COUNT1,
sum(a2.txn_count) as CONTENT_COUNT2
from t_audit a1,
t_audit a2,
tmp_cp_ref t
where t.cont_exp_id = a1.txn_ent_id
and t.cont_exp_id = a2.txn_ent_id
and a1.txn_ent_id = a2.txn_ent_id
and a1.audit_julian_date = a2.audit_julian_date
and a1.audit_type_code = 'CONTENT_1'
and a1.audit_julian_date >= 2454476
and a2.audit_type_code = 'CONTENT_2'
and a2.audit_julian_date >= 2454476
GROUP BY a1.audit_julian_date, t.cont_pol_name
DATE CONT_POLICY CONTENT_COUNT1 CONTENT_COUNT2
------------------------------------------------------
2454483|CP1|6|6
2454484|CP1|143,480|143,480
2454485|CP1|137,133|137,133
2454487|CP1|30,036|30,036
2454488|CP1|889,344|889,344
2454489|CP1|735,556|735,556
2454492|CP1|271,572|271,572
2454493|CP1|719,496|719,496
2454494|CP1|306,867|306,867
2454495|CP1|530,868|530,868