On 2007-07-10 14:04:06 +0200, Erwan Lemonnier wrote: > I am having trouble with a tricky little beast that smells like a bug, > behaves like one but might not be one. > Here are the symptoms: > > I compile perl 5.8.8 (default configuration) and the latest DBI and > DBD::Oracle, connect to an Oracle 10.2.0. Then I create one table and insert > a number with decimals in it (a float). I write a script that fetches this > number, and substracts to it the same number hardcoded within the > script.Theexpected result should be 0. Instead, I get > 2.22044604925031e-16.
Oracle stores numbers in decimal, while perl does computations in
binary. In general you will have to expect some rounding error in the
conversion.
> sql_execute("INSERT INTO test_oracle_bug (DATA) VALUES (1.73696)");
> $DBC->commit;
>
> # fetch numeric from table
> my $ret = sql_execute("SELECT DATA FROM
> test_oracle_bug")->fetchrow_arrayref;
> my ($val) = @$ret;
>
> my $sum = 1.73696 - $val;
> is($sum,0,"does sum $sum == 0?");
This is peculiar, though. DBD::Oracle gets the number from the database
as a string (this can be confirmed with Devel::Peek::Dump), so the above
code should be equvialent to:
my ($val) = ('1.73696');
my $sum = 1.73696 - $val;
is($sum,0,"does sum $sum == 0?");
However, it isn't, for some reason, and I can't see the difference
(upgrading $val to utf8 doesn't make a difference).
Why does perl convert the string '1.73696' to
0011111111111011110010101001011010010001101001110101110011010000
if it comes from an Oracle database, but convert it to
0011111111111011110010101001011010010001101001110101110011010001
if it is hard coded in the script or read from a file?
hp
PS: The binary representations of the numbers were made with
unpack("B64", reverse(pack("d", $val2)))
--
_ | Peter J. Holzer | If I wanted to be "academically correct",
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | [EMAIL PROTECTED] | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users
pgp1go8pdO2Fz.pgp
Description: PGP signature
