============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================
Your name : Darcy
Your email address : Darcy@wavefire.com
System Configuration
---------------------
Architecture (example: Intel Pentium) :Intel Pentium III
Operating System (example: Linux 2.0.26 ELF) : FreeBSD 3.4-STABLE
PostgreSQL version (example: PostgreSQL-7.0): PostgreSQL-7.0.2
Compiler used (example: gcc 2.8.0) : gcc version 2.7.2.3
Please enter a FULL description of your problem:
------------------------------------------------
When doing a join on the same table involving count there apears to be a
cartsian product happeing on the counts returned
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
CREATE TABLE "logging_real" (
"propertyid" int4 NOT NULL,
"search" bool,
"service" bool
);
INSERT INTO "logging_real" ("propertyid","search","service") VALUES
(1,'t','f');
INSERT INTO "logging_real" ("propertyid","search","service") VALUES
(1,'t','f');
INSERT INTO "logging_real" ("propertyid","search","service") VALUES
(1,'t','f');
INSERT INTO "logging_real" ("propertyid","search","service") VALUES
(1,'t','f');
INSERT INTO "logging_real" ("propertyid","search","service") VALUES
(1,'t','f');
INSERT INTO "logging_real" ("propertyid","search","service") VALUES
(1,'f','f');
INSERT INTO "logging_real" ("propertyid","search","service") VALUES
(1,'f','f');
INSERT INTO "logging_real" ("propertyid","search","service") VALUES
(2,'f','f');
INSERT INTO "logging_real" ("propertyid","search","service") VALUES
(3,'f','f');
INSERT INTO "logging_real" ("propertyid","search","service") VALUES
(3,'t','f');
SELECT propertyid,count(propertyid),search,service FROM logging_real GROUP
BY propertyid,search,service;
-- notice the corret values
CREATE VIEW current AS SELECT propertyid,count(propertyid),search,service
FROM logging_real GROUP BY propertyid,search,service;
CREATE VIEW current2 AS SELECT propertyid,count(propertyid),search,service
FROM logging_real GROUP BY propertyid,search,service;
SELECT current.propertyid,current.count AS searchtrue ,current2.count AS
searchfalse FROM current,current2 WHERE
current.propertyid=current2.propertyid AND current.search='t' AND
current2.search='f' AND current.service='f' AND current2.service='f';
-- the results expected here are:
-- propertyid | searchtrue | searchfalse
-- ------------+------------+-------------
-- 1 | 5 | 2
-- 3 | 1 | 1
SELECT
a.propertyid AS apropertyid ,count(a.propertyid) AS acount ,a.search AS
asearch ,a.service AS aservice,
b.propertyid AS bpropertyid ,count(b.propertyid) AS bcount ,b.search AS
bsearch ,b.service AS bservice
FROM
logging_real A, logging_real B
WHERE
a.propertyid = b.propertyid
AND a.search = 't'
AND b.search = 'f'
AND a.service= 'f'
AND b.service= 'f'
GROUP BY apropertyid,bpropertyid,asearch,bsearch,aservice,bservice;
-- the results expected here are:
-- apropertyid | acount | asearch | aservice | bpropertyid | bcount |
bsearch | bservice
--
----------------------------------------------------------------------------
----------
-- 1 | 5 | t | f | 1 | 2 |
f | f
-- 3 | 2 | t | f | 3 | 1 |
f | f
If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------