Thread

joins on the same table with aggregates

Darcy Buskermolen <darcy@ok-connect.com> — 2000-06-29T19:01:44Z

============================================================================
                        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:
---------------------------------------------------------------------