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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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;
From | Date | Subject | |
---|---|---|---|
Next Message | Renaud Diez | 2008-02-13 09:53:57 | BUG #3959: Huge calculation error |
Previous Message | Alvaro Herrera | 2008-02-12 21:28:17 | Re: BUG #3957: make: *** [all] Error 2 / libpq.so.5.1 |