From: Luca Pireddu <luca@cs.ualberta.ca>
To: pgsql-sql@postgresql.org
Subject: problem (bug?) with "in (subquery)"
Date: Thu, 14 Jul 2005 01:34:21 -0600
Message-ID: <200507140134.21133.luca@cs.ualberta.ca> (raw)
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: luca@cs.ualberta.ca
Subject: Re: problem (bug?) with "in (subquery)"
In-Reply-To: <200507140134.21133.luca@cs.ualberta.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox