Wierdness using SUM to add results of custom C function.

From: Konrad Scherer <kscherer(at)magma(dot)ca>
To: pgsql-general(at)postgresql(dot)org
Subject: Wierdness using SUM to add results of custom C function.
Date: 2002-02-23 02:07:53
Message-ID: 3C76F979.D6AB6EE4@magma.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello from a brand new Postgres user.
I first want to thank and congratulate all those who have done an
amazing job on Postgres. It has been a godsend for the cash strapped
univerity project I am part of.
That said I want show you something that has got me really stumped.

Machine: SunOS brancusi 5.7 Generic_106541-08 sun4u sparc SUNW,
UltraSPARC-IIi-Engine

Postgres version: PostgreSQL 7.1.3 on sparc-sun-solaris2.7, compiled by
GCC 2.95.3

Starting info: countcommas is a very simple small C function that counts
the number of commas in a text string and returns int. Compiled as .so
and added using Create Function .. Language 'C'. It works as expected.
Headwords is a text field of comma seperated words with no constraints.

The where clause is not important, it was a convenient way to restrict
output to 3 rows.

Begin ouput
wfdb=> select countcommas(headwords) from workflow where family like
'abat%';
countcommas
-------------
18
3
1
(3 rows)

Comment: These numbers are correct. I verified them by hand _many_
times.

wfdb=> select sum(countcommas(headwords)) from workflow where family
like
'abat%';
sum
-----
24
(1 row)

Comment: Repeat run

wfdb=> select countcommas(headwords) from workflow where family like
'abat%';
countcommas
-------------
18
3
1
(3 rows)

wfdb=> select sum(countcommas(headwords)) from workflow where family
like
'abat%';
sum
-----
23
(1 row)
End output

The C function returns the correct result all the time but the sum
returns the wrong result all the time. It mostly returns 23, but it
returned 24 once!

All these queries where done without any other activity on the database
(it is the test version) within 20 seconds max.

I find it hard to believe there is a bug in SUM that is why I posted
this in pgsql-general. I haven't a clue whether it is SQL, interface or
installation related.
I don't have root on the machine so I couldn't try it on 7.2. If I know
there is a fix I can presuade "them" to install it. Can someone please
help me make sense of this before I go source diving?

Thank you in advance.

Konrad Scherer

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Lee Harr 2002-02-23 02:19:54 Re: Date problem
Previous Message Bruce Momjian 2002-02-23 01:58:13 Re: Casting Varchar to Numeric