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

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

From: "Marcus Torres" <marcsf23(at)yahoo(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #3958: Self-Join Group-By Clause Produces Incorrect Results
Date: 2008-02-13 02:17:37
Message-ID: 200802130217.m1D2HbLQ098042@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      3958
Logged by:          Marcus Torres
Email address:      marcsf23(at)yahoo(dot)com
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;

Responses

pgsql-bugs by date

Next:From: Renaud DiezDate: 2008-02-13 09:53:57
Subject: BUG #3959: Huge calculation error
Previous:From: Alvaro HerreraDate: 2008-02-12 21:28:17
Subject: Re: BUG #3957: make: *** [all] Error 2 / libpq.so.5.1

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