Re: Odd error in complex query (7.2): Sub-SELECT uses un-GROUPed... - Mailing list pgsql-hackers

From Tom Lane
Subject Re: Odd error in complex query (7.2): Sub-SELECT uses un-GROUPed...
Date
Msg-id 11118.1004384169@sss.pgh.pa.us
Whole thread Raw
In response to Odd error in complex query (7.2): Sub-SELECT uses un-GROUPed...  (Philip Warner <pjw@rhyme.com.au>)
Responses Re: Odd error in complex query (7.2): Sub-SELECT
Re: Odd error in complex query (7.2): Sub-SELECT
List pgsql-hackers
Philip Warner <pjw@rhyme.com.au> writes:
> Sorry for the convoluted example:

A simplified example is 
create table t1(n text, f1 int);create table s1(f1a int, x int);create view v1 as select x,  (select t1.n from t1 where
t1.f1= s1.f1a) as afrom s1;select a from v1 group by 1;ERROR:  Sub-SELECT uses un-GROUPed attribute s1.f1a from outer
query

The expanded-out equivalent of the problem query is
select (select t1.n from t1 where t1.f1 = s1.f1a) as a from s1group by 1;

which I believe is indeed illegal.  But it seems like it ought to be
legal with the view in between ... ie, a view isn't purely a macro.

The implementation issue here is how to decide not to pull up the view
subquery (ie, not to flatten the query into the illegal form).  We
already do that for certain conditions; we just have to figure out what
additional restriction should be used to preclude this case.  The
restriction should be as tight as possible to avoid losing the ability
to optimize queries using views.

A simplistic idea is to not pull up views that contain subselects in
the targetlist, but I have a feeling that's not the right restriction.
Or maybe it is --- maybe the point is that the view targetlist is
logically evaluated *before* the outer query executes, and we can't do
a pullup if evaluating it later would change the results.

Comments?  I suspect this is trickier than it looks :-(
        regards, tom lane


pgsql-hackers by date:

Previous
From: "Serguei Mokhov"
Date:
Subject: Re: External Database Connection
Next
From: Tom Lane
Date:
Subject: Re: 7.2b1 ...