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 | not null
name | text | not null default 'common'::text
organism | 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 like
pathway_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.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-42)
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
