Date: Fri, 15 Jul 2005 07:34:04 -0600
From: Michael Fuhr <mike@fuhr.org>
To: Luca Pireddu <luca@cs.ualberta.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: problem (bug?) with "in (subquery)"
Message-ID: <20050715133404.GA30210@winnie.fuhr.org>
References: <200507140134.21133.luca@cs.ualberta.ca>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <200507140134.21133.luca@cs.ualberta.ca>

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:
> 
> 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"?

> 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
);

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

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

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/