From: Luca Pireddu <lucap@shaw.ca>
To: pgsql-sql@postgresql.org
Cc: Michael Fuhr <mike@fuhr.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: problem (bug?) with "in (subquery)"
Date: Fri, 15 Jul 2005 09:52:01 -0600
Message-ID: <200507150952.01732.lucap@shaw.ca> (raw)
In-Reply-To: <20050715133404.GA30210@winnie.fuhr.org>
References: <200507140134.21133.luca@cs.ualberta.ca>
<20050715133404.GA30210@winnie.fuhr.org>
On July 15, 2005 07:34, Michael Fuhr wrote:
> On Thu, Jul 14, 2005 at 01:34:21AM -0600, Luca Pireddu wrote:
> > I have the following query that isn't behaving like I would expect:
Thanks for creating the reduced test case Michael. My apologies for not doing
it myself.
> >
> > select * from strains s where s.id in (select strain_id from
pathway_strains);
>
> Any reason the subquery isn't doing "SELECT DISTINCT strain_id"?
because I don't need to according to the specification of "in". However, it
does generate the correct output. So does
select distinct * 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
>
> What happens when you try each of the following? Do they give the
> expected results? I did some tests and I'm wondering if the planner's
> hash join is responsible for the duplicate rows.
>
> SELECT * FROM strains WHERE id IN (
> SELECT strain_id FROM pathway_strains ORDER BY strain_id
> );
With the "order by" it works as it should, not generating duplicate rows.
>
> CREATE TEMPORARY TABLE foo AS SELECT strain_id FROM pathway_strains;
> SELECT * FROM strains WHERE id IN (SELECT strain_id FROM foo);
This one's interesting. It only returns the unique rows.
>
> SET enable_hashjoin TO off;
> SELECT * FROM strains WHERE id IN (SELECT strain_id FROM pathway_strains);
With hashjoin off the query returns the correct output.
On July 15, 2005 08:58, Tom Lane wrote:
> Ah-hah: this one is the fault of create_unique_path, which quoth
>
> /*
> * If the input is a subquery whose output must be unique already, we
> * don't need to do anything.
> */
>
> Of course, that needs to read "... unique already, *and we are using all
> of its output columns in our DISTINCT list*, we don't need to do
> anything."
>
> regards, tom lane
In any case, it looks like Tom has already found the problem :-) Thanks guys!
Luca
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: lucap@shaw.ca, mike@fuhr.org, tgl@sss.pgh.pa.us
Subject: Re: problem (bug?) with "in (subquery)"
In-Reply-To: <200507150952.01732.lucap@shaw.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