I personally tried some simple experiments with gcc. I found that
printf( "%0.20lg", x ) where x is a double with value 1.73696, yielded
this value:
1.7369600000000000595
However, feeding that back to a double variable using sscanf() resulted
in a value that tested equal to 1.73696. Using sscanf() to convert
"1.73696" to a double also resulted in equality to 1.73696. I tested
this using different versions of gcc (3.4.2 and 4.1.2 I think). Same
result.
So I found nothing conclusive that can be blamed on the C compiler.
There might be some other conversion that broke. I would tend to think
the C compiler and library are more important than the Perl version,
because Perl is compiled using C and uses the C libraries. For that
matter, so do extension libraries such as the DBDs and DBI.
Another possibility is that one of the conversions in DBI uses sprintf()
with not enough precision, but that shouldn't break a number like
1.73696 that terminates in its decimal representation with zero so
early, one would think.
Regarding alternative representations, I believe the following setup is
worth considering for anyone doing high-precision financial
calculations:
In the database, use NUMERIC with a high precision.
In Perl do a
use Math::BigFloat;
or some variant of that; the man pages give variants.
I personally don't have direct experience with this setup, since my Perl
work tends to be non-financial. But it looks like this combination of
tools would give precise results on financial values. (I have used the
BigInt library with success.)
-Will
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Erwan Lemonnier
> Sent: Tuesday 17 July 2007 08:52
> To: dbi-users@perl.org
> Subject: Re: Re: float bug? perl 5.8, DBI and oracle 10.2.0
>
>
> The discussion started by
> http://www.nntp.perl.org/group/perl.dbi.users/2007/07/msg31663.html
> didn't really end with a clear answer, so I wanted to restate the
> problem more clearly this time, in the light of what was brought under
> the discussion.
>
>
> The main line: when storing the string '1.73696' as a numeric into an
> oracle table, then retrieving it into a variable, say $var, if you
> compute from perl:
>
> my $sum = $var - 1.73696;
>
> and if you run with perl 5.8, oracle 10.2.0 and the latest DBI and
> DBD::Oracle modules, the resulting $sum will not be 0 but 2^-52.
>
> Note that the same code running with a perl 5.6 and slightly different
> versions of the surrounding modules returns the expected 0.
>
>
> So what's happening here?
>
> First, 1.73696 happens to be one of those nasty decimal numbers that
> cannot be exactly represented in floating point notation. Meaning that
> the last bit of this number's significand has to be rounded. Now,
> according to IEEE 754, there are a couple of ways of rounding a
> significand, but the norm is to round to the nearest.
>
> As Peter J. Holzer pointed out, when perl sees the string 1.73696, it
> converts it to the following binary representation of a 64-bit
> floating point:
>
> # sign exponent significand
> 0 01111111111 1011110010101001011010010001101001110101110011010001
>
> This apparently does not depend on the version of perl used (both
> 5.6.2, 5.8.5 and 5.8.8 returns that. The opposite would have been
> scary).
>
> On the other hand, the number 1.73696 stored in the oracle database
> gets converted into the floating point:
>
> 0 01111111111 1011110010101001011010010001101001110101110011010000
>
> when running with perl 5.8.* on my host.
> The difference becomes:
>
> 0 01111001011 0000000000000000000000000000000000000000000000000000
>
> which is 2^-52, or 2.22044604925031e-16.
>
> But with perl 5.6.2 and its older versions of DBI/DBD::Oracle,
> possibly compiled differently, the number fetched from the database
> is:
>
> 0 01111111111 1011110010101001011010010001101001110101110011010001
>
> which is exactly the same representation as 1.73696 when perl parses
> it. The difference in that case is an exact 0.
>
> So what's probably happening here is that perl and oracle/DBD::Oracle
> round the binary representation of 1.73696 differently, leading to a
> difference in the least significant bit of the significand of the
> floating point representation of this particular number as seen by
> perl.
>
> The real question is therefore: why would this happen?
>
> In my limited understanding of those issues, I see a few ways of
> explaining this, but fail to prove any of them:
>
> - it could be, as Will Rutherdale mentioned, a floating point problem.
> It could for example be triggered by the compiler: if some of the code
> I used was compiled with -O3 and some with -O2, the order of the
> arithmetic operations leading to perl's representation of the string
> 1.73696 and that of the operations to convert the perl string to
> oracle's inner format and back to native float could differ enough to
> yield a rounding difference, despite the fact that they both are
> supposed to follow IEEE 754.
>
> - it could be that oracle does itself the conversion of the original
> '1.73696' string and uses a different format for native floats than my
> local perl, leading to a difference in rounding. Notice that my oracle
> and perl are running on separate servers, with different hardware. But
> wouldn't that be a bug anyway?
>
> - or it really is a bug somewhere in or beyond DBD::Oracle, introduced
> in later versions than those of the DBI/DBD modules that are installed
> on my host under perl 5.6.2, and that leads to different significand
> rounding rules when perl or oracle/DBD::Oracle convert 1.73696 to a
> floating point.
>
> Help! This really bothers me! Can this be a bug, or not?
>
> /Erwan
>
> PS: as for the workarounds, personally I switched to integer
> arithmetics: I multiply all decimal numbers fetched from oracle by a
> power of 10 and round it, in order to convert the decimal into a
> signed integer, perform all arithmetic operations on integers, then
> divide the result with the appropriate power of ten before using it.
>
- - - - - Appended by Scientific Atlanta, a Cisco company - - - - -
This e-mail and any attachments may contain information which is confidential,
proprietary, privileged or otherwise protected by law. The information is solely
intended for the named addressee (or a person responsible for delivering it to
the addressee). If you are not the intended recipient of this message, you are
not authorized to read, print, retain, copy or disseminate this message or any
part of it. If you have received this e-mail in error, please notify the sender
immediately by return e-mail and delete it from your computer.
