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

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   |/