Thread

General Bug Report: Functions in SQL statements that use aggregations (i.e. group by) causes an abort in psql

Unprivileged user <nobody> — 1999-04-26T00:04:22Z

============================================================================
                        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:
---------


--------------------------------------------------------------------------