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