Thread: BUG #2846: inconsistent and confusing handling of underflows, NaNs and INFs
BUG #2846: inconsistent and confusing handling of underflows, NaNs and INFs
From
"Roman Kononov"
Date:
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