Обсуждение: Possible rounding error of large float values?
<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>
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
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
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 #
# 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
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)
> 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