X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id B94A252BA6
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 15 Jul 2005 11:58:32 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 66250-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 15 Jul 2005 14:58:25 +0000 (GMT)
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by svr1.postgresql.org (Postfix) with ESMTP id B1B5052B88
	for <pgsql-sql@postgresql.org>; Fri, 15 Jul 2005 11:58:22 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j6FEwPbL011561;
	Fri, 15 Jul 2005 10:58:25 -0400 (EDT)
To: Michael Fuhr <mike@fuhr.org>
Cc: Luca Pireddu <luca@cs.ualberta.ca>, pgsql-sql@postgresql.org
Subject: Re: problem (bug?) with "in (subquery)" 
In-reply-to: <20050715141037.GA30461@winnie.fuhr.org> 
References: <200507140134.21133.luca@cs.ualberta.ca>
	<10971.1121435967@sss.pgh.pa.us>
	<20050715141037.GA30461@winnie.fuhr.org>
Comments: In-reply-to Michael Fuhr <mike@fuhr.org>
	message dated "Fri, 15 Jul 2005 08:10:37 -0600"
Date: Fri, 15 Jul 2005 10:58:25 -0400
Message-ID: <11560.1121439505@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.11 tagged_above=0 required=5 tests=AWL,
	UPPERCASE_25_50
X-Spam-Level: 
X-Archive-Number: 200507/144
X-Sequence-Number: 22122

Michael Fuhr <mike@fuhr.org> writes:
> I've been reverse-engineering and simplifying this.  Here's something
> that I think is close:

> CREATE TABLE foo (id integer);
> CREATE TABLE bar (id1 integer, id2 integer);

> INSERT INTO foo VALUES (1);

> INSERT INTO bar VALUES (1, 1);
> INSERT INTO bar VALUES (2, 2);
> INSERT INTO bar VALUES (3, 1);

> SELECT *
> FROM foo
> WHERE id IN (SELECT id2 FROM (SELECT DISTINCT id1, id2 FROM bar) AS s);
>  id 
> ----
>   1
>   1
> (2 rows)

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