Re: Using aggregate functions .. (a BUG?)

On Thu, 24 Oct 2002, Darren Ferguson wrote:

> You should alias the Aggregate function such as
>
> CREATE OR REPLACE FUNCTION sp_test() RETURNS BOOLEAN AS '

[snip]

Perfect, Darren!  (he says sheepishly).

But for some reason postgres is doing something strange.  Here's
a simple test case:

--

CREATE TABLE dummyTable (
id              int,
qty             int,
strng           text
);
INSERT INTO dummyTable VALUES (1,3,'abc');
INSERT INTO dummyTable VALUES (1,7,'def');
INSERT INTO dummyTable VALUES (1,-2,'ghi');
INSERT INTO dummyTable VALUES (2,3,'Abc');
INSERT INTO dummyTable VALUES (2,7,'Def');
INSERT INTO dummyTable VALUES (2,-2,'Ghi');
INSERT INTO dummyTable VALUES (3,3,'abC');
INSERT INTO dummyTable VALUES (3,7,'deF');
INSERT INTO dummyTable VALUES (3,-2,'ghI');

CREATE OR REPLACE FUNCTION testF(int) RETURNS int AS '
        DECLARE
                xid ALIAS FOR   $1;
                rec     record;
        BEGIN
                SELECT INTO rec sum(qty),id AS asum,id FROM dummyTable WHERE id= xid GROUP BY id;
                IF NOT FOUND THEN
                        RETURN ''Nothing found'';
                END IF;
                RETURN rec.asum;
        END;
' LANGUAGE 'plpgsql';

SELECT * FROM dummyTable WHERE id=2;    -- correct rows
SELECT sum(qty) FROM dummyTable WHERE id=2;     -- correct sum
SELECT testF(2);        -- wrong sum

DROP TABLE dummyTable;
DROP FUNCTION testF(int);

--

The first select works properly, returns all appropriate rows,
The second returns the correct total (8).
The last (function call) returns 2 (!).  The function in fact
appears to return the value of id, whatever that might be.

If the SELECT statement is reworded, eliminating the ",id" (twice):
   SELECT INTO rec sum(qty) AS asum FROM dummyTable WHERE id= xid GROUP BY id;
then the correct total is returned.  My guess is that the indexing on
the record gets 'off by 1' somehow.

I'm running Debian postgres (7.2.1-2; not sure what Oliver has
patched this to) on x86. Is this a known bug?  Or am I doing something
characteristically stupid?

Thanks for all your help!

    -frank