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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-hackers by date

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