Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] Functions with aggregations (i.e. group by) causes an abort

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Michael J Davis <michael(dot)j(dot)davis(at)tvguide(dot)com>
Cc: hackers(at)postgreSQL(dot)org
Subject: Re: [HACKERS] Functions with aggregations (i.e. group by) causes an abort
Date: 1999-04-26 00:46:56
Message-ID: 29666.925087616@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Michael J Davis <michael(dot)j(dot)davis(at)tvguide(dot)com> writes:
> The following causes psql to abort:

>    SELECT MemberID, InvoiceID, sum(quantity * unitprice) as InvAmount,
> si_InventoryCategory(InventoryID) as CategoriesID
>    FROM InvoiceLines
>    WHERE memberid = 685
>    GROUP BY MemberID, InvoiceID, CategoriesID;

The proximate cause of the coredump is that replace_agg_refs is finding
a variable that isn't in its target list.  I've added a test for that
condition, so that you get an error rather than a coredump; but that's
not much help for Michael.

It's possible to duplicate the problem with a much simpler test case.
All you need is a GROUP BY on an expression.  For example:

regression=> create table aggtest1 (ID int4, quantity float8);
CREATE
regression=> select sum(quantity), ID+1
regression-> from aggtest1 group by ID;
sum|?column?
---+--------
   |
(1 row)

regression=> select sum(quantity), ID+1
regression-> from aggtest1 group by ID+1;
ERROR:  replace_agg_clause: variable not in target list

(That last converts to a coredump if your sources are older than this
email...)

I think the answer is that we need to add ID to the target list for the
agg node, but I'm not really sure.  Maybe the target list is OK and the
real problem is that replace_agg_clause needs to be able to recognize
targetlist matches on whole expressions (so that it would do something
with the "ID+1" expression instead of recursing down to "ID").  Anyone
understand this stuff?

			regards, tom lane

pgsql-hackers by date

Next:From: Tatsuo IshiiDate: 1999-04-26 02:53:04
Subject: Re: [HACKERS] A patch for FATAL 1:btree: BTP_CHAIN flag was expected
Previous:From: Michael J DavisDate: 1999-04-25 21:08:54
Subject: Functions with aggregations (i.e. group by) causes an abort

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group