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/
