Last night I just upgraded to a nightly snapshot of 7.4 and noticed an 
error on queries that had previously worked (in version 7.3.x and 
previous 7.4's snapshots up to about a month old).

I have a view that I can distill into a base case of:

CREATE VIEW testing_v AS
     SELECT table_a.*
     FROM table_a
     WHERE
     (table_a.some_id IN (
         SELECT DISTINCT table_b.some_id
         FROM table_b
     ));

When I do :

select * from testing_v;

I get:

JOIN qualification may not refer to other relations

I have found that if I remove the distinct on the sub-query, it behaves 
as expected.

Yes, I know the distinct is probably useless but we are in the process 
of porting it to postgres and have lots of cleanup left.

We are migrating from a commercial database (and paid good money) and 
found postgres to be a joy (substantially fast, less gotchas, better at 
embeding business logic at db level).

Thank you very much.  I appreciate all the effort that has put into such 
a great product.


							--spt



PS:  Please cc me as I am not on list.



Fri, 17 Oct 2003 10:59:50 -0400
"Sean P. Thomas" <spt@ulanji.com>
Error with views containing sub-queries with distinct
"Sean P. Thomas" <spt@ulanji.com> writes:
> I get:
> JOIN qualification may not refer to other relations

Problem confirmed here, will look into it.  I have a feeling this is a
bad side-effect of this patch:

2003-10-13 19:48  tgl

	* src/backend/optimizer/prep/prepjointree.c: pull_up_subqueries()
	should copy the subquery before starting to modify it.	Not sure
	why I'd thought it would be a good idea to do differently way back
	when, but Greg Stark exposed the folly of doing so ...

but I don't see why as yet.

			regards, tom lane

Sat, 18 Oct 2003 01:17:10 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Error with views containing sub-queries with distinct 
"Sean P. Thomas" <spt@ulanji.com> writes:
> Last night I just upgraded to a nightly snapshot of 7.4 and noticed an 
> error on queries that had previously worked (in version 7.3.x and 
> previous 7.4's snapshots up to about a month old).

Fixed, thanks for the test case.

			regards, tom lane

Sat, 18 Oct 2003 12:54:04 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Error with views containing sub-queries with distinct 