problem (bug?) with "in (subquery)"

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)

Responses

Browse pgsql-sql by date

  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