From: | Frank Miles <fpm(at)u(dot)washington(dot)edu> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Using aggregate functions .. (a BUG?) |
Date: | 2002-10-25 20:51:30 |
Message-ID: | Pine.A41.4.44.0210251329150.55346-100000@homer40.u.washington.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | DeJuan Jackson | 2002-10-25 20:55:40 | 7.2.3 and MD5 checksum |
Previous Message | Brian K. Quade | 2002-10-25 20:36:54 | Can't start rhdb-admin |