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

At 14:36 29/10/01 -0500, Tom Lane wrote:
>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.

FWIW, MS SQL/Server won't even allow the view to be defined

Dec/RDB does, and it allows the query as well, with the following plannner
output:

Reduce Sort
Cross block of 2 entries
Cross block entry 1
Get Retrieval sequentially of relation S1
Cross block entry 2
Aggregate Conjunct Get
Retrieval sequentially of relation T1

It also allows:

select (select t1.n from t1 where t1.f1 = s1.f1a) as a from s1
group by (select t1.n from t1 where t1.f1 = s1.f1a);

with the same plan. Which does not, on the face of it, seem illegal to me.

RDB usually rewrites column-select-expressions as cross-joins (with
appropriate checking for multiple/no rows). Which seems to work well with
my expectations for both queries, although I presume this it not what the
spec says?

>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).

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?

>Or maybe it is --- maybe the point is that the view targetlist is
>logically evaluated *before* the outer query executes,

This is very nasty, and would really hurt the utility of views.

> and we can't do
>a pullup if evaluating it later would change the results.

Huh?

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/