[BUGS] BUG #3958: Self-Join Group-By Clause Produces Incorrect Results

[Marcus Torres]

The following bug has been logged online:

Bug reference:      3958
Logged by:          Marcus Torres
Email address:      [EMAIL PROTECTED]
PostgreSQL version: 8.19
Operating system:   Linux - Ubuntu
Description:        Self-Join Group-By Clause Produces Incorrect Results
Details: 

This is a repost of my original bug with self-contained sql to reproduce the
problem:

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.  

SQL:
------
DROP TABLE T_AUDIT;
DROP TABLE T_POLICY;

CREATE TABLE T_AUDIT
(
   ID integer NOT NULL, 
   POLICY_ID integer NOT NULL,
   AUDIT_DATE date NOT NULL, 
   AUDIT_TYPE_CODE character varying(50) NOT NULL, 
   TXN_COUNT integer NOT NULL DEFAULT 1
) WITHOUT OIDS
TABLESPACE pg_default;
ALTER TABLE t_audit OWNER TO postgres;
GRANT ALL ON TABLE t_audit TO public;

CREATE TABLE T_POLICY
(
   ID integer NOT NULL, 
   CONTENT_POLICY_NAME character varying(50) NOT NULL
) WITHOUT OIDS
TABLESPACE pg_default;
ALTER TABLE t_audit OWNER TO postgres;
GRANT ALL ON TABLE t_policy TO public;

INSERT INTO T_POLICY VALUES (100, 'TEST POLICY');

INSERT INTO T_AUDIT VALUES (1000, 100, '2008-01-01','CONTENT_1',1);
INSERT INTO T_AUDIT VALUES (1001, 100, '2008-01-01','CONTENT_1',1);
INSERT INTO T_AUDIT VALUES (1002, 100, '2008-01-01','CONTENT_1',1);
INSERT INTO T_AUDIT VALUES (1003, 100, '2008-01-01','CONTENT_1',1);
INSERT INTO T_AUDIT VALUES (1004, 100, '2008-01-01','CONTENT_1',1);

INSERT INTO T_AUDIT VALUES (1005, 100, '2008-01-01','CONTENT_2',1);
INSERT INTO T_AUDIT VALUES (1006, 100, '2008-01-01','CONTENT_2',1);

SELECT A1.AUDIT_DATE,
       P.CONTENT_POLICY_NAME,
       SUM(A1.TXN_COUNT) AS SUM_1,
       SUM(A2.TXN_COUNT) AS SUM_2
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'
GROUP BY A1.AUDIT_DATE, P.CONTENT_POLICY_NAME;

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings