For what it's worth, I'd say "not a bug." If you want to store high
precision numbers in oracle, you've got 38 decimal digits to play
with, and with minimal coaxing perl (and DBI) will handle them as
strings at the appropriate points so that the exact values go in and
come out.
Once you start doing any sort of math with them, I'd say all bets are
off. I haven't done any numerical work in 10 years or so, but I seem
to recall that one can reasonably expect 6 or so decimal significant
digits from a 32 bit floating point number - I'll go out on a limb
and hazard that one can expect 12 or so digits from a 64 bit floating
point number - at any rate I'd be very surprised to get 18
significant digits. And of course these expectations will shrink
depending on the number and order of manipulations.
And as http://www.lahey.com/float.htm points out, that's just the way
it is.
I'd have to ask, when you put in 1.73696, how did you derive it in
the first place? Are your measurements and calculations such that you
"really" have 1.7369600000000000?
If you need precision like that, you should be using a special
library like Math::BigFloat. and if you need more than 38 digits you
should store them as strings in Oracle.
-Chris
On Jul 17, 2007, at 8:51 AM, Erwan Lemonnier wrote:
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.
--
Christopher Sarnowski
[EMAIL PROTECTED]
Penn Bioinformatics Core
1316 Blockley Hall MC 6021
(215) 746-7784
