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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster