Beefy Boxes and Bandwidth Generously Provided by pair Networks Bob
go ahead... be a heretic

Rounding off the scale value after decimal point

by venu_hs (Novice)
on Sep 17, 2012 at 15:36 UTC ( #994052=perlquestion: print w/ replies, xml ) Need Help??
venu_hs has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,

I am fetching data from a table in my sybase database using DBI module. I have a float column in my table. One of the cell value is 4632.3190000000004. When i fetch the same in my perl script. It gets rounded off to 4632.319. So i wanted to confirm the issue, i connected to the sybase database using SQL Developer and queried, i actually got 4632.319. I also connected using ASE isql and used the same query, i got the result as 4632.3190000000004. Is this any driver issue? Could you please help me and let me know if i need to change any of my DBI properties or the Perl properties so that the float value does not get rounded off. Sorry to bother you Monks. Could some one please help me. This is a bit urgent.

Comment on Rounding off the scale value after decimal point
Re: Rounding off the scale value after decimal point
by choroba (Abbot) on Sep 17, 2012 at 15:48 UTC
    It is a precision issue:
    perl -e 'print 4632.3190000000000 == 4632.3190000000008'
    لսႽ ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ
Re: Rounding off the scale value after decimal point
by rjt (Chaplain) on Sep 17, 2012 at 17:21 UTC

    This is a common problem with floating point numbers. In general, you cannot rely on floating point numbers to be "rounded" to any precision unless the rounding is actually part of the program logic or underlying language/library. This happens with whole numbers used as floating points, too. For instance, 39 might become 38.999999999999, which can be a real problem if not handled correctly.

    Small differences in rounding occur because floating point numbers are stored in a relatively small region of memory, and the mantissa and exponent are stored as powers of 2, rather than powers of 10, as you see on screen if you just print it out. As such, it's very improbable that a given number in base-10 (decimal) arithmetic will have a base-2 (binary) equivalent that gives the exact same number. The number will be close (yours broke on the seventeenth significant figure), but rarely exact. DBI and SQL Developer are likely clipping off spurious digit(s) from the result, while ASE is not, or it could be differences in machine/data architectures.

    Do you actually need 17 significant figures, as your example indicates? Or do you just need the results to be the same across all tools? If it's the former, you will have to rethink your data design to include larger floating point numbers (larger means more precise with floating point). If it's the latter, I would just round the result off to whatever precision you need (and can safely expect to maintain).

Re: Rounding off the scale value after decimal point
by swampyankee (Parson) on Sep 19, 2012 at 02:45 UTC

    Perl will use the floating point library for the C compiler used to build it, so I would expect (but not guarantee) that a C interface to the database would give the same answer as the perl interface.

    Double precision only has 15 or 16 significant digits, so the "4" in the right-most place is almost certainly not significant. See, for example,

    Information about American English usage here and here. Floating point issues? Please read this before posting. — emc

      Thank you monks for your responses. I dont want to round off the number. So i started using bignum module to specify the precision as 17. So if a number is actually 500.123, as per the precision of 17, it becomes 500.12300000000000000 which is ideal and intended. But then i would need it as 500.123 Any other perl modules i can use to accomodate this scenario.

        But then i would need it as 500.123 Any other perl modules i can use to accomodate this scenario.

        How odd, either you want the precision or you don't

        You can squash trailing zeros with  $num =~ s/0+$//;

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://994052]
Front-paged by Arunbear
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (9)
As of 2014-04-20 14:04 GMT
Find Nodes?
    Voting Booth?

    April first is:

    Results (485 votes), past polls