Thread: Precision errors in float8 type casting (as of 7.3.2 and some earlier releases at least)

I ran into this oddity today and tested it on a few of our PosgreSQL
backends (all of which gave the same response):

phil=# select 3.85::float4*1;
     ?column?
------------------
 3.84999990463257
(1 row)

phil=# select 3.85::float4*1::float8;
     ?column?
------------------
 3.84999990463257

phil=# select (3.85::float4)::float8;
      float8
------------------
 3.84999990463257
(1 row)

(Or substitute 3.85 for any number with something other than 0 to the
right of the decimal point, or pull the same values from any table which
stores in float4/real format.)

Obviously, this is wrong and should return 3.85.  We traced this down on
an ecom server which was shaving off pennies from some transactions
(because we truncate to the hundredths place instead of rounding what we
get back from the SQL backend).

The newest server we have is 7.3.2, so I haven't tried this on the
current 7.3.3 release.

phil=# select version();

version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2
20020903 (Red Hat Linux 8.0 3.2-7)
(1 row)


Phil

On Wed, 9 Jul 2003, Philip Edelbrock wrote:

> phil=# select (3.85::float4)::float8;
>       float8
> ------------------
>  3.84999990463257
> (1 row)
>
> (Or substitute 3.85 for any number with something other than 0 to the
> right of the decimal point, or pull the same values from any table which
> stores in float4/real format.)
>
> Obviously, this is wrong and should return 3.85.  We traced this down on

I see no obviously about it.  Once you've placed a value in a float you
are accepting the chance of some precision loss.  When we print a float4
we can print it with an amount of precision that generally limits this
(although you'll see things like 3.849998 -> 3.5), but once you cast it to
a float8 those values are distinguishably different.  In theory one could
keep the history of the value around to determine a precision, but that
doesn't really seem better in general.