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/site_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>---------------------------
