Re: 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?

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 #

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: Possible rounding error of large float values?

# 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"
To: "Josh Berkus"
Cc: "Adam Lancaster" ;
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


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Possible rounding error of large float values?

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)



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: Possible rounding error of large float values?

> 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


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match