From kyle@actarg.com Thu Mar 29 20:02:15 2001 Received: from actarg.com (host25.actarg.com [209.180.91.25] (may be forged)) by postgresql.org (8.11.3/8.11.1) with ESMTP id f2TK2FK88147 for ; Thu, 29 Mar 2001 15:02:15 -0500 (EST) (envelope-from kyle@actarg.com) Received: from chi.actarg.com (IDENT:root@chi.actarg.com [192.168.2.16]) by actarg.com (8.9.3/8.9.3) with ESMTP id NAA17187 for ; Thu, 29 Mar 2001 13:02:13 -0700 Received: from actarg.com (IDENT:kyle@localhost [127.0.0.1]) by chi.actarg.com (8.9.3/8.9.3) with ESMTP id NAA25764 for ; Thu, 29 Mar 2001 13:02:14 -0700 Message-ID: <3AC394C6.E7D2E184@actarg.com> Date: Thu, 29 Mar 2001 13:02:14 -0700 From: Kyle Organization: Action Target Inc. X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.12-20 i686) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Possible 7.1RC1 bug Content-Type: multipart/mixed; boundary="------------3C04861CA77FC0D51B8DE7CB" X-Archive-Number: 200103/618 X-Sequence-Number: 2289 This is a multi-part message in MIME format. --------------3C04861CA77FC0D51B8DE7CB Content-Type: multipart/alternative; boundary="------------08AEC5F55C95FC086C676A25" --------------08AEC5F55C95FC086C676A25 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Here's a fragment of code that works on 7.0.3 but gives a type mismatch on 7.1: Under 7.1RC1, func1 fails and func2 builds OK. The error is: ERROR: return type mismatch in function: declared to return int4, returns numeric It appears that sum(int4) returns type numeric. Shouldn't it return int4? create table ints ( inte int4 ); create function func1() returns int4 as ' select coalesce(sum(inte),0) from ints; ' language 'sql'; create function func2() returns int4 as ' select coalesce(sum(inte)::int4,0) from ints; ' language 'sql'; --------------08AEC5F55C95FC086C676A25 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Here's a fragment of code that works on 7.0.3 but gives a type mismatch on 7.1:

Under 7.1RC1, func1 fails and func2 builds OK.  The error is:

ERROR:  return type mismatch in function: declared to return int4, returns numeric

It appears that sum(int4) returns type numeric.  Shouldn't it return int4?
 

create table ints (
    inte        int4
);

create function func1() returns int4 as '
    select coalesce(sum(inte),0) from ints;
' language 'sql';

create function func2() returns int4 as '
    select coalesce(sum(inte)::int4,0) from ints;
' language 'sql';
 
  --------------08AEC5F55C95FC086C676A25-- --------------3C04861CA77FC0D51B8DE7CB Content-Type: text/x-vcard; charset=us-ascii; name="kyle.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Kyle Content-Disposition: attachment; filename="kyle.vcf" begin:vcard n:Bateman;Kyle x-mozilla-html:FALSE org:Action Target Inc. adr:;;;;;; version:2.1 email;internet:kyle@actarg.com title:President x-mozilla-cpt:;0 fn:Kyle Bateman end:vcard --------------3C04861CA77FC0D51B8DE7CB-- From tgl@sss.pgh.pa.us Fri Mar 30 15:33:58 2001 Received: from sss.pgh.pa.us ([216.151.103.158]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f2UFXwK65372 for ; Fri, 30 Mar 2001 10:33:58 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.3/8.11.3) with ESMTP id f2UFXbw01091; Fri, 30 Mar 2001 10:33:37 -0500 (EST) To: Kyle cc: pgsql-sql@postgresql.org Subject: Re: Possible 7.1RC1 bug In-reply-to: <3AC394C6.E7D2E184@actarg.com> References: <3AC394C6.E7D2E184@actarg.com> Comments: In-reply-to Kyle message dated "Thu, 29 Mar 2001 13:02:14 -0700" Date: Fri, 30 Mar 2001 10:33:36 -0500 Message-ID: <1088.985966416@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200103/648 X-Sequence-Number: 2327 Kyle writes: > It appears that sum(int4) returns type numeric. Shouldn't it return > int4? This is not a bug. It's a deliberate change in behavior: sum() and avg() on integer datatypes now return numeric, to avoid problems with overflow. regards, tom lane From josh@agliodbs.com Fri Mar 30 17:37:37 2001 Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f2UHbbK02982 for ; Fri, 30 Mar 2001 12:37:37 -0500 (EST) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 31925 for ; Fri, 30 Mar 2001 09:37:31 -0800 From: "Josh Berkus" Subject: Re: Possible 7.1RC1 bug To: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Fri, 30 Mar 2001 09:37:31 -0800 Message-ID: In-Reply-To: <1088.985966416@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200103/649 X-Sequence-Number: 2328 Tom, > This is not a bug. It's a deliberate change in behavior: sum() and > avg() on integer datatypes now return numeric, to avoid problems with > overflow. That could throw off some of my already-written views & functions. Is there a setting that can change this back to the "regular" behavior of sum(integer) = integer? -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco From tgl@sss.pgh.pa.us Sat Mar 31 05:33:14 2001 Received: from sss.pgh.pa.us ([216.151.103.158]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f2V5XEK93194 for ; Sat, 31 Mar 2001 00:33:14 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.3/8.11.3) with ESMTP id f2V5XBw22129; Sat, 31 Mar 2001 00:33:11 -0500 (EST) To: "Josh Berkus" cc: pgsql-sql@postgresql.org Subject: Re: Possible 7.1RC1 bug In-reply-to: References: Comments: In-reply-to "Josh Berkus" message dated "Fri, 30 Mar 2001 09:37:31 -0800" Date: Sat, 31 Mar 2001 00:33:10 -0500 Message-ID: <22126.986016790@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200103/654 X-Sequence-Number: 2333 "Josh Berkus" writes: >> This is not a bug. It's a deliberate change in behavior: sum() and >> avg() on integer datatypes now return numeric, to avoid problems with >> overflow. > That could throw off some of my already-written views & functions. Is > there a setting that can change this back to the "regular" behavior of > sum(integer) = integer? Sorry, no. You can coerce the result back to int4 if you care to risk overflow: "select sum(int4field)::int4" or some such. regards, tom lane