BUG #2846: inconsistent and confusing handling of underflows, NaNs and INFs - Mailing list pgsql-bugs

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:

Please compare the results of the simple queries.
==============================================
test=# select ('NaN'::float4)::int2;
 int2
------
    0
(1 row)

test=# select ('NaN'::float4)::int4;
    int4
-------------
 -2147483648
(1 row)

test=# select ('NaN'::float4)::int8;
ERROR:  bigint out of range

test=# select ('nan'::numeric)::int4;
ERROR:  cannot convert NaN to integer
==============================================
test=# select abs('INF'::float4);
   abs
----------
 Infinity
(1 row)

test=# select abs('INF'::float8);
ERROR:  type "double precision" value out of range: overflow
==============================================
test=# select -('INF'::float4);
 ?column?
-----------
 -Infinity
(1 row)

test=# select -('INF'::float8);
ERROR:  type "double precision" value out of range: overflow
==============================================
test=# select (1e-37::float4)*(1e-22::float4);
 ?column?
----------
        0
(1 row)

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
==============================================
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
==============================================
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.
==============================================
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)

==============================================
All this things are in float.c