From: | Luca Pireddu <luca(at)cs(dot)ualberta(dot)ca> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | problem (bug?) with "in (subquery)" |
Date: | 2005-07-14 07:34:21 |
Message-ID: | 200507140134.21133.luca@cs.ualberta.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 | 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)
From | Date | Subject | |
---|---|---|---|
Next Message | Mark J Camilleri | 2005-07-14 07:46:59 | Error on dynamic code. |
Previous Message | Vivek Khera | 2005-07-13 21:05:48 | Re: getting back autonumber just inserted |