Date: Thu, 14 Jul 2005 01:34:21 -0600
From: Luca Pireddu <luca@cs.ualberta.ca>
Subject: problem (bug?) with "in (subquery)"
To: pgsql-sql@postgresql.org
Message-id: <200507140134.21133.luca@cs.ualberta.ca>
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: 7bit
Content-disposition: inline
User-Agent: KMail/1.7.2

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)