Thread: Functions with aggregations (i.e. group by) causes an abort

The following works in psql:
  SELECT MemberID, InvoiceID, sum(quantity * unitprice) as InvAmount,
si_InventoryCategory(InventoryID) as CategoriesID  FROM InvoiceLines  WHERE memberid = 685  GROUP BY MemberID,
InvoiceID,InventoryID;
 

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;
 

Here is the abort message:

pqReadData() -- backend closed the channel unexpectedly.       This probably means the backend terminated abnormally
  before or while processing the request.
 
We have lost the connection to the backend, so further processing is
impossible.  Terminating.

There is nothing in the postgreSQL log files.

InvoiceLines is a table.  Here is si_InventoryCategory():

CREATE FUNCTION si_InventoryCategory(int4) RETURNS int4 AS '  'select it.CategoriesID from Inventory i, InventoryType
itwhere
 
i.InventoryID = $1 and i.InventoryTypeID = it.InventoryTypeID' LANGUAGE
'sql';


I am using Red Hat 5.1, PostgreSQL version 6.5 as of this morning.  Any
suggestions on what I can do to work around this?

Thanks, Michael

Michael J Davis <michael.j.davis@tvguide.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