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

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