============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================
Your name : Michael Davis
Your email address : mdavis@sevainc.com
Category : runtime: back-end: SQL
Severity : serious
Summary: Functions in SQL statements that use aggregations (i.e. group by) causes an abort in psql
System Configuration
--------------------
Operating System : Red Hat 5.1 linux 2.0.34 ELF
PostgreSQL version : 6.5
Compiler used : gcc version 2.7.2.3
Hardware:
---------
Dual Pentium II, 128M RAM
Linux linus 2.0.34 #12 Tue Oct 6 23:24:13 MDT 1998 i686 unknown
Versions of other tools:
------------------------
--------------------------------------------------------------------------
Problem Description:
--------------------
Functions in SQL statements that use aggregations (i.e. group by) causes an abort in psql
--------------------------------------------------------------------------
Test Case:
----------
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 it where i.InventoryID = $1 and i.InventoryTypeID =
it.InventoryTypeID'LANGUAGE 'sql';
--------------------------------------------------------------------------
Solution:
---------
--------------------------------------------------------------------------
