Ah, I see it: your join against relationship_wanted isn't unique.

globalmatch=# select count(*) from personal_data pd
globalmatch-# where pd.gid = 17111 AND pd.gender = 0;
 count 
-------
     1
(1 row)

globalmatch=# select count(*) from relationship_wanted rw
globalmatch-# where rw.gid = 17111 AND rw.gender = 0;
 count 
-------
     5
(1 row)

globalmatch=# 

So that inflates the number of rows coming out of the join by 5.

			regards, tom lane

Sat, 12 May 2001 21:34:37 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: bug in JOIN or COUNT or ... ? 
On Sat, 12 May 2001, Tom Lane wrote:

> Ah, I see it: your join against relationship_wanted isn't unique.
>
> globalmatch=# select count(*) from personal_data pd
> globalmatch-# where pd.gid = 17111 AND pd.gender = 0;
>  count
> -------
>      1
> (1 row)
>
> globalmatch=# select count(*) from relationship_wanted rw
> globalmatch-# where rw.gid = 17111 AND rw.gender = 0;
>  count
> -------
>      5
> (1 row)
>
> globalmatch=#
>
> So that inflates the number of rows coming out of the join by 5.

Okay, then I'm lost ... why wouldn't that show up without the COUNT()?  I
doubt doubt your analysis, I just want to understand why ...


Sat, 12 May 2001 22:39:29 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: bug in JOIN or COUNT or ... ? 
The Hermit Hacker <scrappy@hub.org> writes:
>> So that inflates the number of rows coming out of the join by 5.

> Okay, then I'm lost ... why wouldn't that show up without the COUNT()?  I
> doubt doubt your analysis, I just want to understand why ...

You had DISTINCT on your query, which hid the duplicated rows from you.
But that happens *after* aggregate processing, so it doesn't hide the
dups from COUNT().

			regards, tom lane

Sat, 12 May 2001 21:44:03 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: bug in JOIN or COUNT or ... ? 
On Sat, 12 May 2001, Tom Lane wrote:

> The Hermit Hacker <scrappy@hub.org> writes:
> >> So that inflates the number of rows coming out of the join by 5.
>
> > Okay, then I'm lost ... why wouldn't that show up without the COUNT()?  I
> > doubt doubt your analysis, I just want to understand why ...
>
> You had DISTINCT on your query, which hid the duplicated rows from you.
> But that happens *after* aggregate processing, so it doesn't hide the
> dups from COUNT().

Ahhhh, okay, that makes sense ... thanks for taking the time to check it
for me ... and explaining what I was missing ...


Sat, 12 May 2001 22:51:47 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: bug in JOIN or COUNT or ... ? 