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/ ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
