Обсуждение: ERROR: ExecEvalAggref: no aggregates in this expression context

Hello,

Can someone tell me what this error message means:
==========
ERROR: ExecEvalAggref: no aggregates in this expression context
==========

Does it mean that there aren't any aggregate in the expression
context when there should be. Does it mean there are and there
shouldn't be?

For that matter, what are aggregates and in what expression
context should they be or not be in?

Could it be because I am trying to group around something likeentry_view.minprice,entry_view.maxprice
where minprice and maxprice are defined in asmin (item.item_price) as minprice,max (item.item_price) as maxprice
when the entry_view view is defined?
Thanks

-- 
Paul McGarry            mailto:paulm@opentec.com.au 
Systems Integrator      http://www.opentec.com.au 
Opentec Pty Ltd         http://www.iebusiness.com.au
6 Lyon Park Road        Phone: (02) 9878 1744 
North Ryde NSW 2113     Fax:   (02) 9878 1755

Paul McGarry <paulm@opentec.com.au> writes:
> Can someone tell me what this error message means:
> ERROR: ExecEvalAggref: no aggregates in this expression context

> Does it mean that there aren't any aggregate in the expression
> context when there should be.

Yup.  ExecEvalAggref is supposed to fetch the result of a (previously
computed) aggregate function.  It's unhappy because it's not finding
anything to return.  This is a bug --- can we see a complete example
that causes it?

> For that matter, what are aggregates

SUM(), COUNT(), MIN(), that sort of thing...
        regards, tom lane

Hi Tom,

Tom Lane wrote:
> > ERROR: ExecEvalAggref: no aggregates in this expression context
> > Does it mean that there aren't any aggregate in the expression
> > context when there should be.
> Yup.  ExecEvalAggref is supposed to fetch the result of a (previously
> computed) aggregate function.  It's unhappy because it's not finding
> anything to return.  This is a bug --- can we see a complete example
> that causes it?

The view that I am experiencing this error on is rather nasty, so
much so that I am going to solve the problem it was supposed to
solve in another way.

On another (possibly related) note, the same view seems to pose
problems when I do a 'SELECT count(*) from view_name'.

If I do a 'SELECT * from view_name' and get 8 rows of data, when Ido a 'SELECT count(*) from view_name' I get 8 rows of
the
 
number 1 returned. 

In any case I'll try and distill both of these down to a slightly 
less horrific testcase and send it in.

-- 
Paul McGarry            mailto:paulm@opentec.com.au 
Systems Integrator      http://www.opentec.com.au 
Opentec Pty Ltd         http://www.iebusiness.com.au
6 Lyon Park Road        Phone: (02) 9878 1744 
North Ryde NSW 2113     Fax:   (02) 9878 1755

Paul McGarry wrote:

> If I do a 'SELECT * from view_name' and get 8 rows of data, when I
>  do a 'SELECT count(*) from view_name' I get 8 rows of the
> number 1 returned.

I've attached a script which will generate a bunch of tables and
a view then do a select * and select count(*) on that view. It
is almost certainly to do with the GROUPing used in the view.

The ouput I get is:

select * from entry_view;
 id | description_text | parent_id | minamount
----+------------------+-----------+-----------
  1 | entry one        |         0 |         1
  2 | entry two        |         1 |         2
  3 | entry three      |         2 |         7
(3 rows)

select count(*) from entry_view;
 count
-------
     2
     2
     1
(3 rows)

That doesn't make much sense to me...

Postgres 7.0.2 by the way.

I'll work on the more complex case where I got the
ERROR: ExecEvalAggref error message now.

--
Paul McGarry            mailto:paulm@opentec.com.au
Systems Integrator      http://www.opentec.com.au
Opentec Pty Ltd         http://www.iebusiness.com.au
6 Lyon Park Road        Phone: (02) 9878 1744
North Ryde NSW 2113     Fax:   (02) 9878 1755

Paul McGarry <paulm@opentec.com.au> writes:
>> Yup.  ExecEvalAggref is supposed to fetch the result of a (previously
>> computed) aggregate function.  It's unhappy because it's not finding
>> anything to return.  This is a bug --- can we see a complete example
>> that causes it?

> The view that I am experiencing this error on is rather nasty, so
> much so that I am going to solve the problem it was supposed to
> solve in another way.

View?  Hmm, we know that views involving GROUP BY or aggregates
don't work very well.  We hope to fix those problems in 7.2, but
right now there's probably not much that can be done about it.
I'd recommend avoiding views that use those features for now.
        regards, tom lane

Tom Lane wrote:
> View?  Hmm, we know that views involving GROUP BY or aggregates
> don't work very well.  We hope to fix those problems in 7.2, but
> right now there's probably not much that can be done about it.
> I'd recommend avoiding views that use those features for now.

Yes, I've just discovered the Todo list :) Oh well, it probably
needed rethinking anyway.....I want fast retrieval over fast
update so I probably want to do my min() and max() magic with
triggers on insert/update/delete rather than on each select
with the view.

In any case I have attached some SQL which sets up tables and
generates the ExecEvalAggref I was getting in case it is of
any use to anyone.

Cheers.

--
Paul McGarry            mailto:paulm@opentec.com.au
Systems Integrator      http://www.opentec.com.au
Opentec Pty Ltd         http://www.iebusiness.com.au
6 Lyon Park Road        Phone: (02) 9878 1744
North Ryde NSW 2113     Fax:   (02) 9878 1755