Michael Fuhr <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
