Re: Re: float bug? perl 5.8, DBI and oracle 10.2.0

The is an old old problem. I ran into it once many many moons ago working
with an Access report using ODBC on an Oracle 7 table.

The Access report was summing up about 200k records and one field was a FP
number. All of the records that had a field value of 0 Access just could
not see it as 0 and on the sum we were allways getting a sum of 1 or 2 more
that we were suppse to get. Looking at is line by line we finally sumbled
on it.
Access was seeing an Oracle FP 0 as .00000001( or alike) and the sum would
add these up and spoil out totals.

I had a quick look at the DBD::Oracle code and it would be a nightmare to
try and do some sort of conversion to a signed doubble or single in C for
retruned FP and then to PERL. Taking all the dada and cating them to a
string is the only way to handle it so it will work pratically.
One would have to deal with DATES, TIMESTAMPS, NUMBER, NUMBER(38),TINY_INT
and god knows what else.

Cheers
John Scoles




""Rutherdale, Will"" <Will.Rutherdale@xxxxxxxxxx> wrote in message
news:7B8C821E108976489746D62B099CC397011398C1@xxxxxxxxxxxxxxxxxxxxxxxxxxxx
I admire Peter's effort in tracking down the specifics at various
conversion points.

However, there are no documented guarantees of equality from the
library. I hope you're not seriously considering jigging the libraries
to make your program work. If you do that, you'll run up against the
same problem next release.

The more stable solution would be to change your program so it doesn't
depend on equality. Rather you could create a function floatEqual() or
floatZero() that tests whether two float values are nearly equal in
relative terms, or close to zero, without requiring that they be exactly
equal or zero. Then replace the == operator in your application code
with those function calls.

I bet it would be less work to use that solution than to jig the
libraries.

Besides, there are many floating point conversion points, including the
compiler itself, that can change with a new release.

-Will


-----Original Message-----
From: erwan.lemonnier@xxxxxxxxx
[mailto:erwan.lemonnier@xxxxxxxxx] On Behalf Of Erwan Lemonnier
Sent: Wednesday 11 July 2007 04:29
To: dbi-users@xxxxxxxx; hjp@xxxxxxxxx
Subject: Re: Re: float bug? perl 5.8, DBI and oracle 10.2.0


Taking up on Peter's answer to
http://www.nntp.perl.org/group/perl.dbi.users/2007/07/msg31663.html:

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?");

That's indeed what I thought. I tried. But as you mention
below, it isn't.

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?

Thanks! I think you just put your finger on the core of the problem!
This is the very reason why I turned toward the DBI list in the first
hand.

Note that the trailing bit is most probably the one that gets
interpreted as 2^-52 (2.22044604925031e-16) in the faulty $sum result.

I would like to check what binary representation perl 5.6.2 returns
for that same string when fetched from the database. How did you get
the binary strings above?
It itches me that the computation gives the correct result in perl
5.6.2 but not 5.8.8.

The next question would be: what is in charge of converting the data
representation fetched from oracle into perl's native float (leading
to the binary representation you listed above). Would that be
DBD::Oracle? or DBI? or perl itself?

/Erwan Lemonnier




- - - - - 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.


.

Relevant Pages

  • Re: ANNOUNCE: DBD:Oracle 1.18
    ... Well a warning in one compiler is better than and error in an other so I ... This might be a problem more with the older oracle OCI in your client. ... perl -Mblib t/26exe_array.t ...
    (perl.dbi.users)
  • Re: float bug? perl 5.8, DBI and oracle 10.2.0
    ... If you want to store high precision numbers in oracle, you've got 38 decimal digits to play with, and with minimal coaxing perl will handle them as strings at the appropriate points so that the exact values go in and come out. ... 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. ...
    (perl.dbi.users)
  • RE: Memory fault(coredump) with two DBD type connections
    ... I get a core dump ... all the libraries are the same threaded model(single threaded perl uses ... I am running Perl 5.8.8 with DBD-Oracle 1.19 compiled against Oracle ... when I try to connect to the same Oracle database AND same DB2 database ...
    (perl.dbi.users)
  • Re: Is perl better? :(((
    ... >> Wasthe original PERL script hitting an Oracle database? ... >> Did you run an explain plan on your Oracle SQL? ... Java and perl on another. ...
    (comp.lang.java.databases)
  • Re: Is perl better? :(((
    ... >> Wasthe original PERL script hitting an Oracle database? ... >> Did you run an explain plan on your Oracle SQL? ... Java and perl on another. ...
    (comp.lang.java.programmer)