Sounds like you're hitting machine epsilon. It is well known that floating point representations aren't exact, and various conversions (e.g. to and from decimal representation) are just going to increase the problem. This is a well known issue people encounter in numerical analysis courses in computer science.
It's made worse by the binary representation used for floating point on computers. It's not like a pocket calculator where they use BCD (binary-coded decimal). In general you can't expect comparisons between floating point values to come out exactly. Especially what you're doing, subtracting values and expecting to see zero, is not going to work in general. -Will > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Erwan Lemonnier > Sent: Tuesday 10 July 2007 08:04 > To: dbi-users@perl.org > Subject: float bug? perl 5.8, DBI and oracle 10.2.0 > > > Hi List! > > 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. > > I noticed this problem while upgrading a large software from > perl 5.6.2 to > 5.8.8. With my setup, perl 5.6.2 returns the expected 0. But > neither 5.8.5nor > 5.8.8 do. At this stage, I still don't know where to locate > the bug. It > could be the fault of my compiler when building perl. Or it > could be perl's > way of representing native floats (doubtful). Or it could be DBI, or > DBD::Oracle. Or the phase of the moon... > > Has anyone here encountered this issue? Any suggestion would be highly > appreciated! > > Here is a test sequence that reproduces the bug, at least in > my environment: > > -----------------<snip>--------------------------- > use strict; > use warnings; > use Data::Dumper; > use Test::More tests => 1; > use Carp qw(confess); > use DBI; > > # database credentials: EDIT HERE > my $ORASID = $ENV{ORACLE_SID}; > my $ORAUSR = 'username'; > my $ORAPWD = 'password'; > > my $DBC; > > sub sql_execute { > my ($sql,@arg) = @_; > my $sth = $DBC->prepare($sql); > if(!$sth || $sth->err) { > confess "prepare failed for [$sql]\nbecause: > [".$DBC->errstr."]"; > } > $sth->execute(@arg) || > confess "exec failed: [".$sth->errstr."]\nin query [$sql]"; > return $sth; > } > > # connect to oracle > ($DBC = DBI->connect("dbi:Oracle:$ORASID",$ORAUSR,$ORAPWD, > { > PrintError=>0, > AutoCommit=>0, > } > )) || > confess "failure connecting to $ORASID: ".$DBI::errstr; > > # create one temporary table with one numeric column filled > with test data > eval { sql_execute("DROP TABLE test_oracle_bug"); }; > sql_execute("CREATE TABLE test_oracle_bug (DATA NUMBER)"); > 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?"); > > -----------------<snip>--------------------------- > > when running the above tests with 5.6.2, I get: > > [HEAD] ~/HEAD/test/t> !1131$ /opt/perl-5.6.2/bin/perl > 04_test_oracle_bug.t > 1..1 > ok 1 - does sum 0 == 0? > > That's the expected result. Everything fine. > With 5.8.8, I get: > > [HEAD] ~/HEAD/test/t> !1132$ /opt/perl-5.8.8/bin/perl > 04_test_oracle_bug.t > 1..1 > not ok 1 - does sum 2.22044604925031e-16 == 0? > # Failed test 'does sum 2.22044604925031e-16 == 0?' > # at 04_test_oracle_bug.t line 59. > # got: '2.22044604925031e-16' > # expected: '0' > # Looks like you failed 1 test of 1. > > WRONG ANSWER! NO COOKIE!! > > I tried to dig a bit by myself, using Devel::Peek::Dump and > Data::Float::float_parts. > In 5.6.2, $sum ends up being: > > SV = PVNV(0x9ed8860) at 0x9e2e8e0 > REFCNT = 1 > FLAGS = (PADBUSY,PADMY,NOK,POK,pNOK,pPOK) > IV = 0 > NV = 0 > PV = 0x9f45e48 "0"\0 > CUR = 1 > LEN = 35 > > In 5.8.8, it is: > > SV = PVNV(0x9b6f898) at 0x9ab8c44 > REFCNT = 1 > FLAGS = (PADBUSY,PADMY,NOK,POK,pNOK,pPOK) > IV = 0 > NV = 2.22044604925031e-16 > PV = 0x9beca78 "2.22044604925031e-16"\0 > CUR = 20 > LEN = 36 > > in 5.8.8, 'print Dumper(float_parts($sum));' says that $sum contains: > > $VAR1 = '+'; > $VAR2 = -52; > $VAR3 = '1'; > > or in other words 2^-52. > > If I were to start speculating from here, I would say that > $sum looks very > much like a zero with some bits gone loose, kept by mistake > from previous > operations... > > I tried the same test, using an integer value instead of a > float. Replace > 1.73696 with 173696: it will work fine. In other words, when > perl only needs > IVs, it works, but when it start converting the PV 1.73696 into an NV, > trouble starts. > Notice too that it works with most float values. Replace 1.73696 with > 1.73697 or 1.73695 and it works. This hints more toward the > kind of issues > discussed in perlnumber... On the other hand, I get this > issue ONLY with > numbers fetched from the database. When substracting the same numbers > hardcoded as strings or numbers, the result is 0 as expected. > > Well, that's as far as I got, and I am stuck. > I would really appreciate some help or hint! Anyone? > > Best regards, > /Erwan Lemonnier > > > PS: I am running on a redhat: > Linux version 2.6.9-55.ELsmp > ([EMAIL PROTECTED]) (gcc > version 3.4.6 20060404 (Red Hat 3.4.6-3)) #1 SMP Fri Apr 20 > 17:03:35 EDT > 2007 > > On a dual intel xeon 3ghz, and my perl -V says > > -----------------<snip>--------------------------- > Summary of my perl5 (revision 5 version 8 subversion 8) configuration: > Platform: > osname=linux, osvers=2.6.9-55.elsmp, archname=i686-linux > uname='linux dpluplu3 2.6.9-55.elsmp #1 smp fri apr 20 > 17:03:35 edt 2007 > i686 i686 i386 gnulinux ' > config_args='' > hint=recommended, useposix=true, d_sigaction=define > usethreads=undef use5005threads=undef useithreads=undef > usemultiplicity=undef > useperlio=define d_sfio=undef uselargefiles=define usesocks=undef > use64bitint=undef use64bitall=undef uselongdouble=undef > usemymalloc=n, bincompat5005=undef > Compiler: > cc='cc', ccflags ='-fno-strict-aliasing -pipe > -Wdeclaration-after-statement -I/usr/local/include -D_LARGEFILE_SOURCE > -D_FILE_OFFSET_BITS=64', > optimize='-O3', > cppflags='-fno-strict-aliasing -pipe -Wdeclaration-after-statement > -I/usr/local/include' > ccversion='', gccversion='3.4.6 20060404 (Red Hat 3.4.6-8)', > gccosandvers='' > intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=1234 > d_longlong=define, longlongsize=8, d_longdbl=define, > longdblsize=12 > ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='off_t', > lseeksize=8 > alignbytes=4, prototype=define > Linker and Libraries: > ld='cc', ldflags =' -L/usr/local/lib' > libpth=/usr/local/lib /lib /usr/lib > libs=-lnsl -ldb -ldl -lm -lcrypt -lutil -lc > perllibs=-lnsl -ldl -lm -lcrypt -lutil -lc > libc=/lib/libc-2.3.4.so, so=so, useshrplib=false, > libperl=libperl.a > gnulibc_version='2.3.4' > Dynamic Linking: > dlsrc=dl_dlopen.xs, dlext=so, d_dlsymun=undef, ccdlflags='-Wl,-E' > cccdlflags='-fpic', lddlflags='-shared -L/usr/local/lib' > > > Characteristics of this binary (from libperl): > Compile-time options: PERL_MALLOC_WRAP USE_LARGE_FILES USE_PERLIO > Built under linux > Compiled at Jul 10 2007 10:42:23 > %ENV: > > PERL5LIB="/home/erwlem/HEAD/lib/perl:/home/erwlem/HEAD/lib/sit > e_perl:/home/erwlem/HEAD/utl/delivery/lib/perl" > @INC: > /home/erwlem/HEAD/lib/perl > /home/erwlem/HEAD/lib/site_perl/5.8.8/i686-linux > /home/erwlem/HEAD/lib/site_perl/5.8.8 > /home/erwlem/HEAD/lib/site_perl > /home/erwlem/HEAD/utl/delivery/lib/perl > /opt/perl-5.8.8/lib/5.8.8/i686-linux > /opt/perl-5.8.8/lib/5.8.8 > /opt/perl-5.8.8/lib/site_perl/5.8.8/i686-linux > /opt/perl-5.8.8/lib/site_perl/5.8.8 > /opt/perl-5.8.8/lib/site_perl > . > > -----------------<snip>--------------------------- > - - - - - 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.
