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

From: Philip Edelbrock <phil(at)edgedesign(dot)us>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Precision errors in float8 type casting (as of 7.3.2 and some earlier releases at least)
Date: 2003-07-10 00:16:08
Message-ID: 3F0CB048.2070209@edgedesign.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Stephan Szabo 2003-07-10 04:44:02 Re: Precision errors in float8 type casting (as of 7.3.2 and
Previous Message Sociedad Literaria METAFORA 2003-07-09 20:00:01 Error: Panic