I sent this message to pgsql-sql but got no reply and since I'm not
sure if it's a missing feature, bug or something else I'll try
sending it here. Briefly, I'm getting the error
    ERROR:  ExecAgg: Bad Agg->Target for Agg 0
when layering views to get "nested" aggregates.

I tried the following SQL under PostgreSQL 6.3 and 6.4:

    create table contents (
            id              int             not null,
            ix              int             not null,
            volid           int             not null,
            level           int             not null,
            bdate           datetime        not null
    );

    create view freecount as
        select c1.id as id, c1.ix as ix, count(c2.ix) as freeness
        from contents c1, contents c2
        where c1.volid = c2.volid
          and c1.bdate <= c2.bdate
          and c1.level >= c2.level
        group by c1.id, c1.ix;


Under 6.3, doing the view creation as an ordinary users I got
    ERROR:  pg_rewrite: Permission denied.
which, if I recall, means postgres view support wasn't quite up to
letting everyone creates views. Doing the view creation as the
postgres superuser succeeded but doing
    select * from freecount;
then crashed the backend.

So I installed the recently announced postsgres 6.4 RPM for Linux and
tried again. This time, I could create the view as a normal user and
it worked fine for that simple select. However, what I actually want
to do on top of that view is

    create view freetapes as
        select id, min(freeness) - 1
        from freecount
        group by id;

(i.e. do the nested aggregation that SQL syntax won't let me do
directly.) That view creates successfully but doing a
    select * from freetapes
produces the error message

    ERROR:  ExecAgg: Bad Agg->Target for Agg 0

and doing the explicit query

    select id, min(freeness) - 1
    from freecount
    group by id;

gives the same message. I'm not familiar with postgres internals but
it looks as though the internal handling of views is still having
trouble with those two levels of aggregations despite the underlying
queries being OK. As a data point, the view creation and queries work
fine under Informix IDS 7.3 and Sybase. Is this problem with postgres
something which is a fixable bug, a missing feature request that is
planned to arrive soon (maybe it's in 6.5?) or a missing feature which
isn't going to happen any time soon?

--Malcolm

-- 
Malcolm Beattie <mbeattie@sable.ox.ac.uk>
Unix Systems Programmer
Oxford University Computing Services

Tue, 22 Jun 1999 10:36:35 +0100 (BST)
Malcolm Beattie <mbeattie@sable.ox.ac.uk>
Views which lead to nested aggregates
Malcolm Beattie wrote:

> ...
> gives the same message. I'm not familiar with postgres internals but
> it looks as though the internal handling of views is still having
> trouble with those two levels of aggregations despite the underlying
> queries being OK. As a data point, the view creation and queries work
> fine under Informix IDS 7.3 and Sybase. Is this problem with postgres
> something which is a fixable bug, a missing feature request that is
> planned to arrive soon (maybe it's in 6.5?) or a missing feature which
> isn't going to happen any time soon?

    Up  to  now  (v6.5)  this  kind  of  nested  aggregates isn't
    supported.  Not directly over SQL, nor by views. To  be  sure
    anything  is  fine,  your  views (and however you select from
    them) should be expressable with a  regular  SELECT  too.  In
    fact  the rewrite system has to try to build such a query for
    it - so if you can't how should the rewriter can?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #



Tue, 22 Jun 1999 12:22:16 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] Views which lead to nested aggregates
wieck@debis.com (Jan Wieck) writes:
>     Up  to  now  (v6.5)  this  kind  of  nested  aggregates isn't
>     supported.  Not directly over SQL, nor by views. To  be  sure
>     anything  is  fine,  your  views (and however you select from
>     them) should be expressable with a  regular  SELECT  too.  In
>     fact  the rewrite system has to try to build such a query for
>     it - so if you can't how should the rewriter can?

Still, it ought to either do it or produce a useful error message.
6.4's error message doesn't qualify as useful in my book.  But 6.5's
behavior is far worse: it accepts the query and cheerfully generates
a wrong result!  That's definitely a bug.

Looking ahead to the larger problem, I believe that the executor is
perfectly capable of handling nested aggregate plans --- the trick is
to get the planner to produce one.  Maybe we need an extension to the
parsetree language?  It doesn't seem like this ought to be hard to
support, it's just that there's no parsetree configuration that
represents what we want done.  Or, maybe we should rethink the division
of labor between the rewriter and planner --- if the rewriter could
output a partially-converted plan tree, instead of a parse tree, then
it could do as it pleased, but still leave the messy details of lowlevel
plan optimization to the planner.

			regards, tom lane

Tue, 22 Jun 1999 10:30:30 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Views which lead to nested aggregates 