problem (bug?) with "in (subquery)"
От | Luca Pireddu |
---|---|
Тема | problem (bug?) with "in (subquery)" |
Дата | |
Msg-id | 200507140134.21133.luca@cs.ualberta.ca обсуждение исходный текст |
Ответы |
Re: problem (bug?) with "in (subquery)"
Re: problem (bug?) with "in (subquery)" |
Список | pgsql-sql |
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); 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 83 | common | 82 83 | common | 82 83 | common | 82 83 | common | 82 83 | common | 82 83 | common | 82 83 | common | 82 506 | common | 487 506 | common | 487 ... continues By the way, this output is the same as if running the query: select * from strains s join pathway_strains ps on ps.strain_id = s.id; ===================================== Table "public.strains" Column | Type | Modifiers ---------------+--------------+---------------------------------------------------------id | integer | notnull name | text | not null default 'common'::textorganism | integer | not null Indexes: "strains_pkey" PRIMARY KEY, btree (id) ================================== View "public.pathway_strains" Column | Type | Modifiers ------------+---------+-----------pathway_id | integer |strain_id | integer | View definition:SELECT DISTINCT p.id AS pathway_id, c.strain_id FROM catalyst_associations c JOIN pathway_edges e ON c.pathway_edge_id= e.id RIGHT JOIN pathways p ON p.id = e.pathway_id ORDER BY p.id, c.strain_id; The contents of pathways_strains are likepathway_id | strain_id ------------+----------- 2083 | 76 2083 | 80 2083 | 83 2083 | 95 2084| 76 2084 | 80 2084 | 83 2084 | 95 2084 | 162 ...etc So, am I wrong in expecting each strain record to appear only once in the result set? Or is there something wrong with PostgreSQL? I would be happy to provide more information if it's needed. Thank you! Luca ps: # select version(); version -----------------------------------------------------------------------------------------------------------------PostgreSQL 8.0.3on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-42)
В списке pgsql-sql по дате отправления: