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