Re: [BUGS] BUG #2846: inconsistent and confusing handling of - Mailing list pgsql-patches

I have made some more progress on this patch.  I have fixed the issue
with aggregates:

    test=> select avg(ff) from tt;
    ERROR:  type "double precision" value out of range: overflow

and tested the performance overhead of the new CheckFloat8Val() calls
the fix requires using:

    EXPLAIN ANALYZE SELECT AVG(x.COL)
    FROM (SELECT 323.2 AS COL FROM generate_series(1,1000000)) AS x;

and could not measure any overhead.

I also found a few more bugs, this one with float4 negation:

    test=> SELECT -('0'::float4);
     ?column?
    ----------
           -0
    (1 row)

    test=> SELECT -('0'::float8);
     ?column?
    ----------
            0
    (1 row)

and this one with casting 'Nan' to an integer:

    test=> SELECT 'Nan'::float8::int4;
        int4
    -------------
     -2147483648
    (1 row)

I have fixed these as well:

    test=> SELECT -('0'::float4);
     ?column?
    ----------
            0
    (1 row)

    test=>  SELECT 'Nan'::float8::int4;
    ERROR:  integer out of range

The only unsolved issue is the one with underflow checks.  I have added
comments explaining the problem in case someone ever figures out how to
address it.

If I don't receive further comments, I will apply the new attached patch
shortly.

---------------------------------------------------------------------------

bruce wrote:
> Roman Kononov wrote:
> >
> > The following bug has been logged online:
> >
> > Bug reference:      2846
> > Logged by:          Roman Kononov
> > Email address:      kononov195-pgsql@yahoo.com
> > PostgreSQL version: 8.2.0 and older
> > Operating system:   linux 2.6.15-27-amd64 ubuntu
> > Description:        inconsistent and confusing handling of underflows, NaNs
> > and INFs
> > Details:
>
> This is a very interesting bug report.  It seems you have done some good
> analysis of PostgreSQL and how it handles certain corner cases,
> infinity, and NaN.
>
> I have researched your findings and will show some fixes below:
>
> > Please compare the results of the simple queries.
> > ==============================================
> > test=# select ('NaN'::float4)::int2;
> >  int2
> > ------
> >     0
> > (1 row)
>
> There certainly should be an isnan() test when converting to int2
> because while float can represent NaN, int2 cannot.  The fix shows:
>
>     test=> select ('NaN'::float4)::int2;
>     ERROR:  smallint out of range
>
> > test=# select ('NaN'::float4)::int4;
> >     int4
> > -------------
> >  -2147483648
> > (1 row)
>
> Same for int4:
>
>     test=> select ('NaN'::float4)::int4;
>     ERROR:  integer out of range
>
> > test=# select ('NaN'::float4)::int8;
> > ERROR:  bigint out of range
>
> This one was correct because it uses rint() internally.
>
> > test=# select ('nan'::numeric)::int4;
> > ERROR:  cannot convert NaN to integer
> > ==============================================
> > test=# select abs('INF'::float4);
> >    abs
> > ----------
> >  Infinity
> > (1 row)
>
> Correct.
>
> > test=# select abs('INF'::float8);
> > ERROR:  type "double precision" value out of range: overflow
>
> This one was more complicated.  float4/8 operations test for
> results > FLOAT[84]_MAX.  This is because if you do this:
>
>     test=> select (1e201::float8)*(1e200::float8);
>
> the result internally is Infinity, so they check for Inf as a check for
> overflow.  The bottom line is that while the current code allows
> infinity to be entered, it does not allow the value to operate in many
> context because it is assumes Inf to be an overflow indicator.  I have
> fixed this by passing a boolean to indicate if any of the operands were
> infinity, and if so, allow an infinite result, so this now works:
>
>     test=> select abs('INF'::float8);
>        abs
>     ----------
>      Infinity
>     (1 row)
>
> > ==============================================
> > test=# select -('INF'::float4);
> >  ?column?
> > -----------
> >  -Infinity
> > (1 row)
> >
> > test=# select -('INF'::float8);
> > ERROR:  type "double precision" value out of range: overflow
>
> And this now works too:
>
>     test=> select -('INF'::float8);
>      ?column?
>     -----------
>      -Infinity
>     (1 row)
>
> > ==============================================
> > test=# select (1e-37::float4)*(1e-22::float4);
> >  ?column?
> > ----------
> >         0
> > (1 row)
>
> This one is quite complex.  For overflow, there is a range of values
> that is represented as > FLOAT8_MAX, but for values very large, the
> result becomes Inf.  The old code assumed an Inf result was an overflow,
> and threw an error, as I outlined above.  The new code does a better
> job.
>
> Now, for underflow.  For underflow, we again have a range slightly
> smaller than DBL_MIN where we can detect an underflow, and throw an
> error, but just like overflow, if the underflow is too small, the result
> becomes zero.  The bad news is that unlike Inf, zero isn't a special
> value.  With Inf, we could say if we got an infinite result from
> non-infinite arguments, we had an overflow, but for underflow, how do we
> know if zero is an underflow or just the correct result?  For
> multiplication, we could say that a zero result for non-zero arguments
> is almost certainly an underflow, but I don't see how we can handle the
> other operations as simply.
>
> I was not able to fix the underflow problems you reported.
>
> > test=# select (1e-37::float4)*(1e-2::float4);
> > ERROR:  type "real" value out of range: underflow
> > ==============================================
> > test=# select (1e-300::float8)*(1e-30::float8);
> >  ?column?
> > ----------
> >         0
> > (1 row)
> >
> > test=# select (1e-300::float8)*(1e-20::float8);
> > ERROR:  type "double precision" value out of range: underflow
> > ==============================================
> > test=# select ('INF'::float8-'INF'::float8);
> >  ?column?
> > ----------
> >       NaN
> > (1 row)
> >
> > test=# select ('INF'::float8+'INF'::float8);
> > ERROR:  type "double precision" value out of range: overflow
>
> This works fine now:
>
>     test=> select ('INF'::float8+'INF'::float8);
>      ?column?
>     ----------
>      Infinity
>     (1 row)
>
> > ==============================================
> > test=# select ('INF'::float4)::float8;
> >   float8
> > ----------
> >  Infinity
> > (1 row)
> >
> > test=# select ('INF'::float8)::float4;
> > ERROR:  type "real" value out of range: overflow
> > ==============================================
> > test=# select cbrt('INF'::float4);
> >    cbrt
> > ----------
> >  Infinity
> > (1 row)
> >
> > test=# select sqrt('INF'::float4);
> > ERROR:  type "double precision" value out of range: overflow
>
> This works fine too:
>
>     test=> select ('INF'::float8)::float4;
>       float4
>     ----------
>      Infinity
>     (1 row)
>
> > ==============================================
> > test=# select ((-32768::int8)::int2)%(-1::int2);
> >  ?column?
> > ----------
> >         0
> > (1 row)
> >
> > test=# select ((-2147483648::int8)::int4)%(-1::int4);
> > ERROR:  floating-point exception
> > DETAIL:  An invalid floating-point operation was signaled. This probably
> > means an out-of-range result or an invalid operation, such
> > as division by zero.
>
> This was an interesting case.  It turns out the value has to be INT_MIN,
> and the second value has to be -1.  The exception happens, I think,
> because the CPU does the division first before getting the remainder,
> and INT_MIN / -1 is > INT_MAX, hence the error.  I just special-cased it
> to return zero in the int4mod() code:
>
>     test=> select ((-2147483648::int8)::int4)%(-1::int4);
>      ?column?
>     ----------
>             0
>     (1 row)
>
> You can actually show the error without using int8:
>
>     test=> select ((-2147483648)::int4) % (-1);
>      ?column?
>     ----------
>             0
>     (1 row)
>
> The parentheses are required to make the value negative before the cast
> to int4.
>
> > ==============================================
> > test=# create table tt (ff float8);
> > CREATE TABLE
> > test=# insert into tt values (1e308),(1e308),(1e308);
> > INSERT 0 3
> > test=# select * from tt;
> >    ff
> > --------
> >  1e+308
> >  1e+308
> >  1e+308
> > (3 rows)
> >
> > test=# select avg(ff) from tt;
> >    avg
> > ----------
> >  Infinity
> > (1 row)
> >
> > test=# select stddev(ff) from tt;
> >  stddev
> > --------
> >     NaN
> > (1 row)
>
> I didn't study the aggregate cases.  Does someone want to look those
> over?
>
> The attached patch fixes all the items I mentioned above.

--
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: src/backend/utils/adt/float.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/float.c,v
retrieving revision 1.131
diff -c -c -r1.131 float.c
*** src/backend/utils/adt/float.c    23 Dec 2006 02:13:24 -0000    1.131
--- src/backend/utils/adt/float.c    27 Dec 2006 18:43:26 -0000
***************
*** 104,111 ****
  int            extra_float_digits = 0;        /* Added to DBL_DIG or FLT_DIG */


! static void CheckFloat4Val(double val);
! static void CheckFloat8Val(double val);
  static int    float4_cmp_internal(float4 a, float4 b);
  static int    float8_cmp_internal(float8 a, float8 b);

--- 104,111 ----
  int            extra_float_digits = 0;        /* Added to DBL_DIG or FLT_DIG */


! static void CheckFloat4Val(double val, bool has_inf_args);
! static void CheckFloat8Val(double val, bool has_inf_args);
  static int    float4_cmp_internal(float4 a, float4 b);
  static int    float8_cmp_internal(float8 a, float8 b);

***************
*** 211,219 ****
   * raise an ereport() error if it is
   */
  static void
! CheckFloat4Val(double val)
  {
!     if (fabs(val) > FLOAT4_MAX)
          ereport(ERROR,
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
                   errmsg("type \"real\" value out of range: overflow")));
--- 211,220 ----
   * raise an ereport() error if it is
   */
  static void
! CheckFloat4Val(double val, bool has_inf_args)
  {
!     /* If one of the input arguments was infinity, allow an infinite result */
!     if (fabs(val) > FLOAT4_MAX && (!isinf(val) || !has_inf_args))
          ereport(ERROR,
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
                   errmsg("type \"real\" value out of range: overflow")));
***************
*** 230,241 ****
   * raise an ereport() error if it is
   */
  static void
! CheckFloat8Val(double val)
  {
!     if (fabs(val) > FLOAT8_MAX)
          ereport(ERROR,
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
            errmsg("type \"double precision\" value out of range: overflow")));
      if (val != 0.0 && fabs(val) < FLOAT8_MIN)
          ereport(ERROR,
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
--- 231,261 ----
   * raise an ereport() error if it is
   */
  static void
! CheckFloat8Val(double val, bool has_inf_args)
  {
!     /*
!      *    Computations that slightly exceed FLOAT8_MAX are non-Infinity,
!      *    but those that greatly exceed FLOAT8_MAX become Infinity.  Therefore
!      *    it is difficult to tell if a value is really infinity or the result
!      *    of an overflow.  The solution is to use a boolean indicating if
!      *    the input arguments were infiity, meaning an infinite result is
!      *    probably not the result of an overflow.  This allows various
!      *    computations like SELECT 'Inf'::float8 + 5.
!      */
!     if (fabs(val) > FLOAT8_MAX && (!isinf(val) || !has_inf_args))
          ereport(ERROR,
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
            errmsg("type \"double precision\" value out of range: overflow")));
+     /*
+      *    Underflow has similar issues to overflow, i.e. if a computation is
+      *    slighly smaller than FLOAT8_MIN, the result is non-zero, but if it is
+      *    much smaller than FLOAT8_MIN, the value becomes zero.  However,
+      *    unlike overflow, zero is not a special value and can be the result
+      *    of a computation, so there is no easy way to pass a boolean
+      *    indicating whether a zero result is reasonable or not.  It might
+      *    be possible for multiplication and division, but because of rounding,
+      *    such tests would probably not be reliable.
+      */
      if (val != 0.0 && fabs(val) < FLOAT8_MIN)
          ereport(ERROR,
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
***************
*** 369,376 ****
       * if we get here, we have a legal double, still need to check to see if
       * it's a legal float4
       */
!     if (!isinf(val))
!         CheckFloat4Val(val);

      PG_RETURN_FLOAT4((float4) val);
  }
--- 389,395 ----
       * if we get here, we have a legal double, still need to check to see if
       * it's a legal float4
       */
!     CheckFloat4Val(val, true /* allow Inf */);

      PG_RETURN_FLOAT4((float4) val);
  }
***************
*** 558,565 ****
               errmsg("invalid input syntax for type double precision: \"%s\"",
                      orig_num)));

!     if (!isinf(val))
!         CheckFloat8Val(val);

      PG_RETURN_FLOAT8(val);
  }
--- 577,583 ----
               errmsg("invalid input syntax for type double precision: \"%s\"",
                      orig_num)));

!     CheckFloat8Val(val, true /* allow Inf */);

      PG_RETURN_FLOAT8(val);
  }
***************
*** 652,659 ****
  float4um(PG_FUNCTION_ARGS)
  {
      float4        arg1 = PG_GETARG_FLOAT4(0);

!     PG_RETURN_FLOAT4((float4) -arg1);
  }

  Datum
--- 670,681 ----
  float4um(PG_FUNCTION_ARGS)
  {
      float4        arg1 = PG_GETARG_FLOAT4(0);
+     float4        result;
+
+     result = ((arg1 != 0) ? -(arg1) : arg1);

!     CheckFloat4Val(result, isinf(arg1));
!     PG_RETURN_FLOAT4(result);
  }

  Datum
***************
*** 705,716 ****
  float8abs(PG_FUNCTION_ARGS)
  {
      float8        arg1 = PG_GETARG_FLOAT8(0);
-     float8        result;

!     result = fabs(arg1);
!
!     CheckFloat8Val(result);
!     PG_RETURN_FLOAT8(result);
  }


--- 727,734 ----
  float8abs(PG_FUNCTION_ARGS)
  {
      float8        arg1 = PG_GETARG_FLOAT8(0);

!     PG_RETURN_FLOAT8(fabs(arg1));
  }


***************
*** 725,731 ****

      result = ((arg1 != 0) ? -(arg1) : arg1);

!     CheckFloat8Val(result);
      PG_RETURN_FLOAT8(result);
  }

--- 743,749 ----

      result = ((arg1 != 0) ? -(arg1) : arg1);

!     CheckFloat8Val(result, isinf(arg1));
      PG_RETURN_FLOAT8(result);
  }

***************
*** 786,792 ****
      double        result;

      result = arg1 + arg2;
!     CheckFloat4Val(result);
      PG_RETURN_FLOAT4((float4) result);
  }

--- 804,810 ----
      double        result;

      result = arg1 + arg2;
!     CheckFloat4Val(result, isinf(arg1) || isinf(arg2));
      PG_RETURN_FLOAT4((float4) result);
  }

***************
*** 798,804 ****
      double        result;

      result = arg1 - arg2;
!     CheckFloat4Val(result);
      PG_RETURN_FLOAT4((float4) result);
  }

--- 816,822 ----
      double        result;

      result = arg1 - arg2;
!     CheckFloat4Val(result, isinf(arg1) || isinf(arg2));
      PG_RETURN_FLOAT4((float4) result);
  }

***************
*** 810,816 ****
      double        result;

      result = arg1 * arg2;
!     CheckFloat4Val(result);
      PG_RETURN_FLOAT4((float4) result);
  }

--- 828,834 ----
      double        result;

      result = arg1 * arg2;
!     CheckFloat4Val(result, isinf(arg1) || isinf(arg2));
      PG_RETURN_FLOAT4((float4) result);
  }

***************
*** 829,835 ****
      /* Do division in float8, then check for overflow */
      result = (float8) arg1 / (float8) arg2;

!     CheckFloat4Val(result);
      PG_RETURN_FLOAT4((float4) result);
  }

--- 847,853 ----
      /* Do division in float8, then check for overflow */
      result = (float8) arg1 / (float8) arg2;

!     CheckFloat4Val(result, isinf(arg1) || isinf(arg2));
      PG_RETURN_FLOAT4((float4) result);
  }

***************
*** 848,854 ****

      result = arg1 + arg2;

!     CheckFloat8Val(result);
      PG_RETURN_FLOAT8(result);
  }

--- 866,872 ----

      result = arg1 + arg2;

!     CheckFloat8Val(result, isinf(arg1) || isinf(arg2));
      PG_RETURN_FLOAT8(result);
  }

***************
*** 861,867 ****

      result = arg1 - arg2;

!     CheckFloat8Val(result);
      PG_RETURN_FLOAT8(result);
  }

--- 879,885 ----

      result = arg1 - arg2;

!     CheckFloat8Val(result, isinf(arg1) || isinf(arg2));
      PG_RETURN_FLOAT8(result);
  }

***************
*** 874,880 ****

      result = arg1 * arg2;

!     CheckFloat8Val(result);
      PG_RETURN_FLOAT8(result);
  }

--- 892,898 ----

      result = arg1 * arg2;

!     CheckFloat8Val(result, isinf(arg1) || isinf(arg2));
      PG_RETURN_FLOAT8(result);
  }

***************
*** 892,898 ****

      result = arg1 / arg2;

!     CheckFloat8Val(result);
      PG_RETURN_FLOAT8(result);
  }

--- 910,916 ----

      result = arg1 / arg2;

!     CheckFloat8Val(result, isinf(arg1) || isinf(arg2));
      PG_RETURN_FLOAT8(result);
  }

***************
*** 1142,1148 ****
  {
      float8        num = PG_GETARG_FLOAT8(0);

!     CheckFloat4Val(num);

      PG_RETURN_FLOAT4((float4) num);
  }
--- 1160,1166 ----
  {
      float8        num = PG_GETARG_FLOAT8(0);

!     CheckFloat4Val(num, isinf(num));

      PG_RETURN_FLOAT4((float4) num);
  }
***************
*** 1157,1163 ****
      float8        num = PG_GETARG_FLOAT8(0);
      int32        result;

!     if (num < INT_MIN || num > INT_MAX)
          ereport(ERROR,
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
                   errmsg("integer out of range")));
--- 1175,1182 ----
      float8        num = PG_GETARG_FLOAT8(0);
      int32        result;

!     /* 'Inf' is handled by INT_MAX */
!     if (num < INT_MIN || num > INT_MAX || isnan(num))
          ereport(ERROR,
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
                   errmsg("integer out of range")));
***************
*** 1176,1182 ****
      float8        num = PG_GETARG_FLOAT8(0);
      int16        result;

!     if (num < SHRT_MIN || num > SHRT_MAX)
          ereport(ERROR,
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
                   errmsg("smallint out of range")));
--- 1195,1201 ----
      float8        num = PG_GETARG_FLOAT8(0);
      int16        result;

!     if (num < SHRT_MIN || num > SHRT_MAX || isnan(num))
          ereport(ERROR,
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
                   errmsg("smallint out of range")));
***************
*** 1223,1229 ****
      float4        num = PG_GETARG_FLOAT4(0);
      int32        result;

!     if (num < INT_MIN || num > INT_MAX)
          ereport(ERROR,
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
                   errmsg("integer out of range")));
--- 1242,1248 ----
      float4        num = PG_GETARG_FLOAT4(0);
      int32        result;

!     if (num < INT_MIN || num > INT_MAX || isnan(num))
          ereport(ERROR,
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
                   errmsg("integer out of range")));
***************
*** 1242,1248 ****
      float4        num = PG_GETARG_FLOAT4(0);
      int16        result;

!     if (num < SHRT_MIN || num > SHRT_MAX)
          ereport(ERROR,
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
                   errmsg("smallint out of range")));
--- 1261,1267 ----
      float4        num = PG_GETARG_FLOAT4(0);
      int16        result;

!     if (num < SHRT_MIN || num > SHRT_MAX || isnan(num))
          ereport(ERROR,
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
                   errmsg("smallint out of range")));
***************
*** 1485,1491 ****

      result = sqrt(arg1);

!     CheckFloat8Val(result);
      PG_RETURN_FLOAT8(result);
  }

--- 1504,1510 ----

      result = sqrt(arg1);

!     CheckFloat8Val(result, isinf(arg1));
      PG_RETURN_FLOAT8(result);
  }

***************
*** 1500,1505 ****
--- 1519,1525 ----
      float8        result;

      result = cbrt(arg1);
+     CheckFloat8Val(result, isinf(arg1));
      PG_RETURN_FLOAT8(result);
  }

***************
*** 1539,1545 ****
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
                   errmsg("result is out of range")));

!     CheckFloat8Val(result);
      PG_RETURN_FLOAT8(result);
  }

--- 1559,1565 ----
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
                   errmsg("result is out of range")));

!     CheckFloat8Val(result, isinf(arg1) || isinf(arg2));
      PG_RETURN_FLOAT8(result);
  }

***************
*** 1569,1575 ****
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
                   errmsg("result is out of range")));

!     CheckFloat8Val(result);
      PG_RETURN_FLOAT8(result);
  }

--- 1589,1595 ----
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
                   errmsg("result is out of range")));

!     CheckFloat8Val(result, isinf(arg1));
      PG_RETURN_FLOAT8(result);
  }

***************
*** 1598,1604 ****

      result = log(arg1);

!     CheckFloat8Val(result);
      PG_RETURN_FLOAT8(result);
  }

--- 1618,1624 ----

      result = log(arg1);

!     CheckFloat8Val(result, isinf(arg1));
      PG_RETURN_FLOAT8(result);
  }

***************
*** 1628,1634 ****

      result = log10(arg1);

!     CheckFloat8Val(result);
      PG_RETURN_FLOAT8(result);
  }

--- 1648,1654 ----

      result = log10(arg1);

!     CheckFloat8Val(result, isinf(arg1));
      PG_RETURN_FLOAT8(result);
  }

***************
*** 1653,1659 ****
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
                   errmsg("input is out of range")));

!     CheckFloat8Val(result);
      PG_RETURN_FLOAT8(result);
  }

--- 1673,1679 ----
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
                   errmsg("input is out of range")));

!     CheckFloat8Val(result, isinf(arg1));
      PG_RETURN_FLOAT8(result);
  }

***************
*** 1678,1684 ****
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
                   errmsg("input is out of range")));

!     CheckFloat8Val(result);
      PG_RETURN_FLOAT8(result);
  }

--- 1698,1704 ----
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
                   errmsg("input is out of range")));

!     CheckFloat8Val(result, isinf(arg1));
      PG_RETURN_FLOAT8(result);
  }

***************
*** 1703,1709 ****
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
                   errmsg("input is out of range")));

!     CheckFloat8Val(result);
      PG_RETURN_FLOAT8(result);
  }

--- 1723,1729 ----
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
                   errmsg("input is out of range")));

!     CheckFloat8Val(result, isinf(arg1));
      PG_RETURN_FLOAT8(result);
  }

***************
*** 1729,1735 ****
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
                   errmsg("input is out of range")));

!     CheckFloat8Val(result);
      PG_RETURN_FLOAT8(result);
  }

--- 1749,1755 ----
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
                   errmsg("input is out of range")));

!     CheckFloat8Val(result, isinf(arg1) || isinf(arg2));
      PG_RETURN_FLOAT8(result);
  }

***************
*** 1754,1760 ****
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
                   errmsg("input is out of range")));

!     CheckFloat8Val(result);
      PG_RETURN_FLOAT8(result);
  }

--- 1774,1780 ----
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
                   errmsg("input is out of range")));

!     CheckFloat8Val(result, isinf(arg1));
      PG_RETURN_FLOAT8(result);
  }

***************
*** 1780,1786 ****
                   errmsg("input is out of range")));

      result = 1.0 / result;
!     CheckFloat8Val(result);
      PG_RETURN_FLOAT8(result);
  }

--- 1800,1806 ----
                   errmsg("input is out of range")));

      result = 1.0 / result;
!     CheckFloat8Val(result, isinf(arg1));
      PG_RETURN_FLOAT8(result);
  }

***************
*** 1805,1811 ****
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
                   errmsg("input is out of range")));

!     CheckFloat8Val(result);
      PG_RETURN_FLOAT8(result);
  }

--- 1825,1831 ----
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
                   errmsg("input is out of range")));

!     CheckFloat8Val(result, isinf(arg1));
      PG_RETURN_FLOAT8(result);
  }

***************
*** 1830,1836 ****
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
                   errmsg("input is out of range")));

!     CheckFloat8Val(result);
      PG_RETURN_FLOAT8(result);
  }

--- 1850,1856 ----
                  (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
                   errmsg("input is out of range")));

!     CheckFloat8Val(result, isinf(arg1));
      PG_RETURN_FLOAT8(result);
  }

***************
*** 1846,1852 ****

      result = arg1 * (180.0 / M_PI);

!     CheckFloat8Val(result);
      PG_RETURN_FLOAT8(result);
  }

--- 1866,1872 ----

      result = arg1 * (180.0 / M_PI);

!     CheckFloat8Val(result, isinf(arg1));
      PG_RETURN_FLOAT8(result);
  }

***************
*** 1872,1878 ****

      result = arg1 * (M_PI / 180.0);

!     CheckFloat8Val(result);
      PG_RETURN_FLOAT8(result);
  }

--- 1892,1898 ----

      result = arg1 * (M_PI / 180.0);

!     CheckFloat8Val(result, isinf(arg1));
      PG_RETURN_FLOAT8(result);
  }

***************
*** 1963,1970 ****

      N += 1.0;
      sumX += newval;
      sumX2 += newval * newval;
!
      /*
       * If we're invoked by nodeAgg, we can cheat and modify our first
       * parameter in-place to reduce palloc overhead. Otherwise we construct a
--- 1983,1992 ----

      N += 1.0;
      sumX += newval;
+     CheckFloat8Val(sumX, isinf(transvalues[1]) || isinf(newval));
      sumX2 += newval * newval;
!     CheckFloat8Val(sumX2, isinf(transvalues[2]) || isinf(newval));
!
      /*
       * If we're invoked by nodeAgg, we can cheat and modify our first
       * parameter in-place to reduce palloc overhead. Otherwise we construct a
***************
*** 2016,2023 ****

      N += 1.0;
      sumX += newval;
      sumX2 += newval * newval;
!
      /*
       * If we're invoked by nodeAgg, we can cheat and modify our first
       * parameter in-place to reduce palloc overhead. Otherwise we construct a
--- 2038,2047 ----

      N += 1.0;
      sumX += newval;
+     CheckFloat4Val(sumX, isinf(transvalues[1]) || isinf(newval));
      sumX2 += newval * newval;
!     CheckFloat4Val(sumX2, isinf(transvalues[2]) || isinf(newval));
!
      /*
       * If we're invoked by nodeAgg, we can cheat and modify our first
       * parameter in-place to reduce palloc overhead. Otherwise we construct a
***************
*** 2088,2093 ****
--- 2112,2118 ----
          PG_RETURN_NULL();

      numerator = N * sumX2 - sumX * sumX;
+     CheckFloat8Val(numerator, isinf(sumX2) || isinf(sumX));

      /* Watch out for roundoff error producing a negative numerator */
      if (numerator <= 0.0)
***************
*** 2116,2121 ****
--- 2141,2147 ----
          PG_RETURN_NULL();

      numerator = N * sumX2 - sumX * sumX;
+     CheckFloat8Val(numerator, isinf(sumX2) || isinf(sumX));

      /* Watch out for roundoff error producing a negative numerator */
      if (numerator <= 0.0)
***************
*** 2144,2149 ****
--- 2170,2176 ----
          PG_RETURN_NULL();

      numerator = N * sumX2 - sumX * sumX;
+     CheckFloat8Val(numerator, isinf(sumX2) || isinf(sumX));

      /* Watch out for roundoff error producing a negative numerator */
      if (numerator <= 0.0)
***************
*** 2172,2177 ****
--- 2199,2205 ----
          PG_RETURN_NULL();

      numerator = N * sumX2 - sumX * sumX;
+     CheckFloat8Val(numerator, isinf(sumX2) || isinf(sumX));

      /* Watch out for roundoff error producing a negative numerator */
      if (numerator <= 0.0)
***************
*** 2220,2230 ****

      N += 1.0;
      sumX += newvalX;
      sumX2 += newvalX * newvalX;
      sumY += newvalY;
      sumY2 += newvalY * newvalY;
      sumXY += newvalX * newvalY;
!
      /*
       * If we're invoked by nodeAgg, we can cheat and modify our first
       * parameter in-place to reduce palloc overhead. Otherwise we construct a
--- 2248,2263 ----

      N += 1.0;
      sumX += newvalX;
+     CheckFloat8Val(sumX, isinf(transvalues[1]) || isinf(newvalX));
      sumX2 += newvalX * newvalX;
+     CheckFloat8Val(sumX2, isinf(transvalues[2]) || isinf(newvalX));
      sumY += newvalY;
+     CheckFloat8Val(sumY, isinf(transvalues[3]) || isinf(newvalY));
      sumY2 += newvalY * newvalY;
+     CheckFloat8Val(sumY2, isinf(transvalues[4]) || isinf(newvalY));
      sumXY += newvalX * newvalY;
!     CheckFloat8Val(sumXY, isinf(transvalues[5]) || isinf(newvalX) || isinf(newvalY));
!
      /*
       * If we're invoked by nodeAgg, we can cheat and modify our first
       * parameter in-place to reduce palloc overhead. Otherwise we construct a
***************
*** 2282,2287 ****
--- 2315,2321 ----
          PG_RETURN_NULL();

      numerator = N * sumX2 - sumX * sumX;
+     CheckFloat8Val(numerator, isinf(sumX2) || isinf(sumX));

      /* Watch out for roundoff error producing a negative numerator */
      if (numerator <= 0.0)
***************
*** 2310,2315 ****
--- 2344,2350 ----
          PG_RETURN_NULL();

      numerator = N * sumY2 - sumY * sumY;
+     CheckFloat8Val(numerator, isinf(sumY2) || isinf(sumY));

      /* Watch out for roundoff error producing a negative numerator */
      if (numerator <= 0.0)
***************
*** 2340,2345 ****
--- 2375,2381 ----
          PG_RETURN_NULL();

      numerator = N * sumXY - sumX * sumY;
+     CheckFloat8Val(numerator, isinf(sumXY) || isinf(sumX) || isinf(sumY));

      /* A negative result is valid here */

***************
*** 2406,2411 ****
--- 2442,2448 ----
          PG_RETURN_NULL();

      numerator = N * sumXY - sumX * sumY;
+     CheckFloat8Val(numerator, isinf(sumXY) || isinf(sumX) || isinf(sumY));

      PG_RETURN_FLOAT8(numerator / (N * N));
  }
***************
*** 2432,2437 ****
--- 2469,2475 ----
          PG_RETURN_NULL();

      numerator = N * sumXY - sumX * sumY;
+     CheckFloat8Val(numerator, isinf(sumXY) || isinf(sumX) || isinf(sumY));

      PG_RETURN_FLOAT8(numerator / (N * (N - 1.0)));
  }
***************
*** 2464,2471 ****
--- 2502,2512 ----
          PG_RETURN_NULL();

      numeratorX = N * sumX2 - sumX * sumX;
+     CheckFloat8Val(numeratorX, isinf(sumX2) || isinf(sumX));
      numeratorY = N * sumY2 - sumY * sumY;
+     CheckFloat8Val(numeratorY, isinf(sumY2) || isinf(sumY));
      numeratorXY = N * sumXY - sumX * sumY;
+     CheckFloat8Val(numeratorXY, isinf(sumXY) || isinf(sumX) || isinf(sumY));
      if (numeratorX <= 0 || numeratorY <= 0)
          PG_RETURN_NULL();

***************
*** 2501,2508 ****
--- 2542,2552 ----
          PG_RETURN_NULL();

      numeratorX = N * sumX2 - sumX * sumX;
+     CheckFloat8Val(numeratorX, isinf(sumX2) || isinf(sumX));
      numeratorY = N * sumY2 - sumY * sumY;
+     CheckFloat8Val(numeratorY, isinf(sumY2) || isinf(sumY));
      numeratorXY = N * sumXY - sumX * sumY;
+     CheckFloat8Val(numeratorXY, isinf(sumXY) || isinf(sumX) || isinf(sumY));
      if (numeratorX <= 0)
          PG_RETURN_NULL();
      /* per spec, horizontal line produces 1.0 */
***************
*** 2538,2544 ****
--- 2582,2590 ----
          PG_RETURN_NULL();

      numeratorX = N * sumX2 - sumX * sumX;
+     CheckFloat8Val(numeratorX, isinf(sumX2) || isinf(sumX));
      numeratorXY = N * sumXY - sumX * sumY;
+     CheckFloat8Val(numeratorXY, isinf(sumXY) || isinf(sumX) || isinf(sumY));
      if (numeratorX <= 0)
          PG_RETURN_NULL();

***************
*** 2570,2576 ****
--- 2616,2624 ----
          PG_RETURN_NULL();

      numeratorX = N * sumX2 - sumX * sumX;
+     CheckFloat8Val(numeratorX, isinf(sumX2) || isinf(sumX));
      numeratorXXY = sumY * sumX2 - sumX * sumXY;
+     CheckFloat8Val(numeratorXXY, isinf(sumY) || isinf(sumX2) || isinf(sumX) || isinf(sumXY));
      if (numeratorX <= 0)
          PG_RETURN_NULL();

***************
*** 2598,2604 ****
      float8        result;

      result = arg1 + arg2;
!     CheckFloat8Val(result);
      PG_RETURN_FLOAT8(result);
  }

--- 2646,2652 ----
      float8        result;

      result = arg1 + arg2;
!     CheckFloat8Val(result, isinf(arg1) || isinf(arg2));
      PG_RETURN_FLOAT8(result);
  }

***************
*** 2610,2616 ****
      float8        result;

      result = arg1 - arg2;
!     CheckFloat8Val(result);
      PG_RETURN_FLOAT8(result);
  }

--- 2658,2664 ----
      float8        result;

      result = arg1 - arg2;
!     CheckFloat8Val(result, isinf(arg1) || isinf(arg2));
      PG_RETURN_FLOAT8(result);
  }

***************
*** 2622,2628 ****
      float8        result;

      result = arg1 * arg2;
!     CheckFloat8Val(result);
      PG_RETURN_FLOAT8(result);
  }

--- 2670,2676 ----
      float8        result;

      result = arg1 * arg2;
!     CheckFloat8Val(result, isinf(arg1) || isinf(arg2));
      PG_RETURN_FLOAT8(result);
  }

***************
*** 2639,2645 ****
                   errmsg("division by zero")));

      result = arg1 / arg2;
!     CheckFloat8Val(result);
      PG_RETURN_FLOAT8(result);
  }

--- 2687,2693 ----
                   errmsg("division by zero")));

      result = arg1 / arg2;
!     CheckFloat8Val(result, isinf(arg1) || isinf(arg2));
      PG_RETURN_FLOAT8(result);
  }

***************
*** 2658,2664 ****

      result = arg1 + arg2;

!     CheckFloat8Val(result);
      PG_RETURN_FLOAT8(result);
  }

--- 2706,2712 ----

      result = arg1 + arg2;

!     CheckFloat8Val(result, isinf(arg1) || isinf(arg2));
      PG_RETURN_FLOAT8(result);
  }

***************
*** 2671,2677 ****

      result = arg1 - arg2;

!     CheckFloat8Val(result);
      PG_RETURN_FLOAT8(result);
  }

--- 2719,2725 ----

      result = arg1 - arg2;

!     CheckFloat8Val(result, isinf(arg1) || isinf(arg2));
      PG_RETURN_FLOAT8(result);
  }

***************
*** 2684,2690 ****

      result = arg1 * arg2;

!     CheckFloat8Val(result);
      PG_RETURN_FLOAT8(result);
  }

--- 2732,2738 ----

      result = arg1 * arg2;

!     CheckFloat8Val(result, isinf(arg1) || isinf(arg2));
      PG_RETURN_FLOAT8(result);
  }

***************
*** 2702,2708 ****

      result = arg1 / arg2;

!     CheckFloat8Val(result);
      PG_RETURN_FLOAT8(result);
  }

--- 2750,2756 ----

      result = arg1 / arg2;

!     CheckFloat8Val(result, isinf(arg1) || isinf(arg2));
      PG_RETURN_FLOAT8(result);
  }

Index: src/backend/utils/adt/int.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/int.c,v
retrieving revision 1.75
diff -c -c -r1.75 int.c
*** src/backend/utils/adt/int.c    4 Oct 2006 00:29:59 -0000    1.75
--- src/backend/utils/adt/int.c    27 Dec 2006 18:43:26 -0000
***************
*** 1124,1129 ****
--- 1124,1134 ----
          ereport(ERROR,
                  (errcode(ERRCODE_DIVISION_BY_ZERO),
                   errmsg("division by zero")));
+
+     /* SELECT ((-2147483648)::int4) % (-1); causes a floating point exception */
+     if (arg1 == INT_MIN && arg2 == -1)
+         PG_RETURN_INT32(0);
+
      /* No overflow is possible */

      PG_RETURN_INT32(arg1 % arg2);
Index: src/test/regress/expected/float4.out
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/expected/float4.out,v
retrieving revision 1.13
diff -c -c -r1.13 float4.out
*** src/test/regress/expected/float4.out    7 Apr 2005 01:51:40 -0000    1.13
--- src/test/regress/expected/float4.out    27 Dec 2006 18:43:28 -0000
***************
*** 72,78 ****
  SELECT ' INFINITY    x'::float4;
  ERROR:  invalid input syntax for type real: " INFINITY    x"
  SELECT 'Infinity'::float4 + 100.0;
! ERROR:  type "double precision" value out of range: overflow
  SELECT 'Infinity'::float4 / 'Infinity'::float4;
   ?column?
  ----------
--- 72,82 ----
  SELECT ' INFINITY    x'::float4;
  ERROR:  invalid input syntax for type real: " INFINITY    x"
  SELECT 'Infinity'::float4 + 100.0;
!  ?column?
! ----------
!  Infinity
! (1 row)
!
  SELECT 'Infinity'::float4 / 'Infinity'::float4;
   ?column?
  ----------
Index: src/test/regress/expected/float8.out
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/expected/float8.out,v
retrieving revision 1.24
diff -c -c -r1.24 float8.out
*** src/test/regress/expected/float8.out    8 Jun 2005 21:15:29 -0000    1.24
--- src/test/regress/expected/float8.out    27 Dec 2006 18:43:28 -0000
***************
*** 72,78 ****
  SELECT ' INFINITY    x'::float8;
  ERROR:  invalid input syntax for type double precision: " INFINITY    x"
  SELECT 'Infinity'::float8 + 100.0;
! ERROR:  type "double precision" value out of range: overflow
  SELECT 'Infinity'::float8 / 'Infinity'::float8;
   ?column?
  ----------
--- 72,82 ----
  SELECT ' INFINITY    x'::float8;
  ERROR:  invalid input syntax for type double precision: " INFINITY    x"
  SELECT 'Infinity'::float8 + 100.0;
!  ?column?
! ----------
!  Infinity
! (1 row)
!
  SELECT 'Infinity'::float8 / 'Infinity'::float8;
   ?column?
  ----------