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

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