Re: Odd error in complex query (7.2): Sub-SELECT

On Tue, 30 Oct 2001 11:49:28 +1100
Philip Warner wrote:

> 
> It's not clear to me that it should be illegal - for every row in s1, it
> should return the result of the column-select (which may be NULL) - or is
> that what 'not flattening the query' does?
> 
> >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.
> 
> How about whenenever it will throw this error? ;-).,
> 
> >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.
> 
> That does seem excessive. I'm way over my head here, but can a column
> select be implemented as a special JOIN that always returns 1 row (maybe
> NULL), and throws an error if more than one row? 
> 

Hi,

I wouldn't think most people need a query like this, but also
had been in puzzle as to how not to pull up. Finally the 
problem could be solved by using a statement of an ORDER BY.
Therefore, if you add an ORDER BY to a view of your complex
query, it will work correctly. 

And, as long as each of correlative subselects which are 
in columns always returns one row, I feel it is legal 
rather than illegal that its subselects can be GROUPed.



-- on 7.1.2

create table t1(n text, f1 int, f2 int);
create table g1(n text, t1n text);
create table s1(k1 text, f1a int, f1b int, f2 int, x int, d timestamp);


create view v1 as
select k1, d,    (select g1.n from g1, t1         where g1.t1n=t1.n and t1.f1 = s1.f1a and t1.f2 = s1.f2 limit 1) as a,
  (select g1.n from g1, t1         where g1.t1n=t1.n and t1.f1 = s1.f1b and t1.f2 = s1.f2 limit 1) as b,     x   from
s1  order by 1   -- *** an additional statement ***
 
;


explain
select coalesce(a, b, 'other') as name, k1, sum(x) as tot   from v1   where d > '28-oct-2001 12:00' and d <
current_timestamp  group by 1,2   order by tot desc limit 40;
 




Regards,
Masaru Sugawara