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

Michael Fuhr <mike(at)fuhr(dot)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