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

Philip Warner <pjw(at)rhyme(dot)com(dot)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 a
from 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 s1
group 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