pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Possible 7.1RC1 bug
4+ messages / 3 participants
[nested] [flat]

* Possible 7.1RC1 bug
@ 2001-03-29 20:02 Kyle <kyle@actarg.com>
  2001-03-30 15:33 ` Re: Possible 7.1RC1 bug Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 4+ messages in thread

From: Kyle @ 2001-03-29 20:02 UTC (permalink / raw)
  To: pgsql-sql

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';

Attachments:

kyle.vcf (text/x-vcard, 184B) download 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
^ permalink raw reply [nested|flat] 4+ messages in thread

* Re: Possible 7.1RC1 bug
  2001-03-29 20:02 Possible 7.1RC1 bug Kyle <kyle@actarg.com>
@ 2001-03-30 15:33 ` Tom Lane <tgl@sss.pgh.pa.us>
  2001-03-30 17:37   ` Re: Possible 7.1RC1 bug Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Tom Lane @ 2001-03-30 15:33 UTC (permalink / raw)
  To: Kyle <kyle@actarg.com>; +Cc: pgsql-sql

Kyle <kyle@actarg.com> 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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Possible 7.1RC1 bug
  2001-03-29 20:02 Possible 7.1RC1 bug Kyle <kyle@actarg.com>
  2001-03-30 15:33 ` Re: Possible 7.1RC1 bug Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-03-30 17:37   ` Josh Berkus <josh@agliodbs.com>
  2001-03-31 05:33     ` Re: Possible 7.1RC1 bug Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 4+ messages in thread

From: Josh Berkus @ 2001-03-30 17:37 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Possible 7.1RC1 bug
  2001-03-29 20:02 Possible 7.1RC1 bug Kyle <kyle@actarg.com>
  2001-03-30 15:33 ` Re: Possible 7.1RC1 bug Tom Lane <tgl@sss.pgh.pa.us>
  2001-03-30 17:37   ` Re: Possible 7.1RC1 bug Josh Berkus <josh@agliodbs.com>
@ 2001-03-31 05:33     ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 2001-03-31 05:33 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

"Josh Berkus" <josh@agliodbs.com> 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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2001-03-31 05:33 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-29 20:02 Possible 7.1RC1 bug Kyle <kyle@actarg.com>
2001-03-30 15:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-30 17:37   ` Josh Berkus <josh@agliodbs.com>
2001-03-31 05:33     ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox