Thread: Possible rounding error of large float values?

Possible rounding error of large float values?

From
"Adam Lancaster"
Date:
<p><font face="Arial" size="2">When setting a float column to this value:</font><p><font face="Arial"
size="2">9223372036854775807</font><p><fontface="Arial" size="2">It gets selected out as:</font><p><font face="Arial"
size="2">9.22337203685478E18</font><p><fontface="Arial" size="2">Which appears to be rounded. When we cast it to
numerictype we get:</font><p><font face="Arial" size="2">9223372036854780000</font><p><font face="Arial" size="2">Which
alsois rounded. It is still possible to find the row using the original value</font><p><font face="Arial"
size="2">select* from <table> where <col> = 9223372036854775807</font><p><font face="Arial" size="2">Is
thisexpected behavior?</font><br /><p><font face="Arial" size="2">Thanks,</font><br /><font face="Arial"
size="2">Adam</font>

Re: Possible rounding error of large float values?

From
Josh Berkus
Date:
Adam,

> 9223372036854775807
>
> It gets selected out as:
>
> 9.22337203685478E18

This is a property of FLOAT data types.  They round.

> Which appears to be rounded. When we cast it to numeric type we get:
>
> 9223372036854780000
>
> Which also is rounded. It is still possible to find the row using the
> original value

Hmmm ... is 15 digits the limit of NUMERIC?   It may be.

> Is this expected behavior?

Yes.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco


Re: Possible rounding error of large float values?

From
Oliver Elphick
Date:
On Tue, 2004-08-24 at 20:52, Josh Berkus wrote:
> Adam,
> 
> > 9223372036854775807
> >
> > It gets selected out as:
> >
> > 9.22337203685478E18
> 
> This is a property of FLOAT data types.  They round.
> 
> > Which appears to be rounded. When we cast it to numeric type we get:
> >
> > 9223372036854780000
> >
> > Which also is rounded. It is still possible to find the row using the
> > original value
> 
> Hmmm ... is 15 digits the limit of NUMERIC?   It may be.

It must be the limit of float.  Numeric can't produce any more than it
was given and it was cast from float.

bray=# select 653596708775675750507850507570708696432 ::numeric;                numeric
-----------------------------------------653596708775675750507850507570708696432
(1 row)

-- 
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
========================================   "I saw in the night visions, and, behold, one like the      Son of man came
withthe clouds of heaven, and came to     the Ancient of days, and they brought him near before      him. And there was
givenhim dominion, and glory, and      a kingdom, that all people, nations, and languages,      should serve him; his
dominionis an everlasting      dominion, which shall not pass away, and his kingdom      that which shall not be
destroyed."                                       Daniel 7:13,14
 



Re: Possible rounding error of large float values?

From
Jan Wieck
Date:
On 8/24/2004 4:21 PM, Oliver Elphick wrote:
> On Tue, 2004-08-24 at 20:52, Josh Berkus wrote:
>> Adam,
>> 
>> > 9223372036854775807
>> >
>> > It gets selected out as:
>> >
>> > 9.22337203685478E18
>> 
>> This is a property of FLOAT data types.  They round.
>> 
>> > Which appears to be rounded. When we cast it to numeric type we get:
>> >
>> > 9223372036854780000
>> >
>> > Which also is rounded. It is still possible to find the row using the
>> > original value
>> 
>> Hmmm ... is 15 digits the limit of NUMERIC?   It may be.

The original numeric code I presented contained even log(), ln() and 
trigonometric functions that produced results of up to 1,000 digits 
precision. They wheren't considered usefull, so they didn't survive, but 
numeric is that good after all.


Jan

> 
> It must be the limit of float.  Numeric can't produce any more than it
> was given and it was cast from float.
> 
> bray=# select 653596708775675750507850507570708696432 ::numeric;
>                  numeric
> -----------------------------------------
>  653596708775675750507850507570708696432
> (1 row)
> 


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Re: Possible rounding error of large float values?

From
"Iain"
Date:
# select 9223372036854775807 = 9223372036854775807::float;?column?
----------t
(1 row)

# select 9223372036854775807 = 9223372036854775807.0::float;?column?
----------t
(1 row)

# select 9223372036854775807 = 9223372036854775807.0000::numeric;?column?
----------t
(1 row)

This and the fact that it's still possible to find the row using the
original value would seem to indicate that the rounding is just a display
artifact..

----- Original Message ----- 
From: "Oliver Elphick" <olly@lfix.co.uk>
To: "Josh Berkus" <josh@agliodbs.com>
Cc: "Adam Lancaster" <alancaster@preventsys.com>; <pgsql-sql@postgresql.org>
Sent: Wednesday, August 25, 2004 5:21 AM
Subject: Re: [SQL] Possible rounding error of large float values?


> On Tue, 2004-08-24 at 20:52, Josh Berkus wrote:
> > Adam,
> >
> > > 9223372036854775807
> > >
> > > It gets selected out as:
> > >
> > > 9.22337203685478E18
> >
> > This is a property of FLOAT data types.  They round.
> >
> > > Which appears to be rounded. When we cast it to numeric type we get:
> > >
> > > 9223372036854780000
> > >
> > > Which also is rounded. It is still possible to find the row using the
> > > original value
> >
> > Hmmm ... is 15 digits the limit of NUMERIC?   It may be.
>
> It must be the limit of float.  Numeric can't produce any more than it
> was given and it was cast from float.
>
> bray=# select 653596708775675750507850507570708696432 ::numeric;
>                  numeric
> -----------------------------------------
>  653596708775675750507850507570708696432
> (1 row)
>
> -- 
> Oliver Elphick                                          olly@lfix.co.uk
> Isle of Wight                              http://www.lfix.co.uk/oliver
> GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
>                  ========================================
>      "I saw in the night visions, and, behold, one like the
>       Son of man came with the clouds of heaven, and came to
>       the Ancient of days, and they brought him near before
>       him. And there was given him dominion, and glory, and
>       a kingdom, that all people, nations, and languages,
>       should serve him; his dominion is an everlasting
>       dominion, which shall not pass away, and his kingdom
>       that which shall not be destroyed."
>                                     Daniel 7:13,14
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster



Re: Possible rounding error of large float values?

From
Stephan Szabo
Date:
On Wed, 25 Aug 2004, Iain wrote:

>
> # select 9223372036854775807 = 9223372036854775807::float;
>  ?column?
> ----------
>  t
> (1 row)

> This and the fact that it's still possible to find the row using the
> original value would seem to indicate that the rounding is just a display
> artifact..

I don't think that's true since the number ending in 807 is equal to a
version ending in 808.

sszabo=# select 9223372036854775807 = 9223372036854775808::float;?column?
----------t
(1 row)

(In this next one I'm casting both because I want to choose the types of
both literals)

sszabo=#  select 9223372036854775807::float =
9223372036854775808::numeric;?column?
----------t
(1 row)




Re: Possible rounding error of large float values?

From
"Iain"
Date:
> I don't think that's true since the number ending in 807 is equal to a
> version ending in 808.
>
> sszabo=# select 9223372036854775807 = 9223372036854775808::float;
>  ?column?
> ----------
>  t
> (1 row)

Good point. I think that the problems with using floats are well documented
(I particulary like the doc that come with IBMs java.lang.BigDecimal
package) so there shouldn't be any excuse for being surprised by how they
behave. Basically floats are fine when used for what they were desgned for,
but a liability otherwise.

Just thinking about it now, I can't remember last time I designed a database
that used float data...

regards
Iain