Date: Fri, 15 Jul 2005 09:52:01 -0600
From: Luca Pireddu <lucap@shaw.ca>
Subject: Re: problem (bug?) with "in (subquery)"
In-reply-to: <20050715133404.GA30210@winnie.fuhr.org>
To: pgsql-sql@postgresql.org
Cc: Michael Fuhr <mike@fuhr.org>, Tom Lane <tgl@sss.pgh.pa.us>
Message-id: <200507150952.01732.lucap@shaw.ca>
MIME-version: 1.0
Content-type: text/plain; charset=iso-8859-1
Content-transfer-encoding: 7bit
Content-disposition: inline
References: <200507140134.21133.luca@cs.ualberta.ca>
	<20050715133404.GA30210@winnie.fuhr.org>
User-Agent: KMail/1.7.2

On July 15, 2005 07:34, Michael Fuhr wrote:
> On Thu, Jul 14, 2005 at 01:34:21AM -0600, Luca Pireddu wrote:
> > I have the following query that isn't behaving like I would expect:

Thanks for creating the reduced test case Michael.  My apologies for not doing 
it myself.

> > 
> > select * from strains s where s.id in (select strain_id from 
pathway_strains);
> 
> Any reason the subquery isn't doing "SELECT DISTINCT strain_id"?

because I don't need to according to the specification of "in".  However, it 
does generate the correct output.  So does

select distinct * from strains s where s.id in (select strain_id from 
pathway_strains);

> 
> > I would expect each strain record to appear only once.  Instead I get 
output 
> > like this, where the same strain id appears many times:
> > 
> >   id   |     name     | organism
> > -------+--------------+----------
> >     83 | common       |       82 
> >     83 | common       |       82 
> >     83 | common       |       82 
> 
> What happens when you try each of the following?  Do they give the
> expected results?  I did some tests and I'm wondering if the planner's
> hash join is responsible for the duplicate rows.
> 
> SELECT * FROM strains WHERE id IN (
>   SELECT strain_id FROM pathway_strains ORDER BY strain_id
> );

With the "order by"  it works as it should, not generating duplicate rows.

> 
> CREATE TEMPORARY TABLE foo AS SELECT strain_id FROM pathway_strains;
> SELECT * FROM strains WHERE id IN (SELECT strain_id FROM foo);

This one's interesting.  It only returns the unique rows.  

> 
> SET enable_hashjoin TO off;
> SELECT * FROM strains WHERE id IN (SELECT strain_id FROM pathway_strains);

With hashjoin off the query returns the correct output.

On July 15, 2005 08:58, Tom Lane wrote:
> Ah-hah: this one is the fault of create_unique_path, which quoth
> 
>     /*
>      * If the input is a subquery whose output must be unique already, we
>      * don't need to do anything.
>      */
> 
> Of course, that needs to read "... unique already, *and we are using all
> of its output columns in our DISTINCT list*, we don't need to do
> anything."
> 
> 			regards, tom lane

In any case, it looks like Tom has already found the problem :-)  Thanks guys!

Luca