float bug? perl 5.8, DBI and oracle 10.2.0

------=_Part_27226_16979169.1184069046924
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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:

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

--------------------------------------------

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 (brewbuilder@ls20-bc2-14.build.redhat.com) (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

--------------------------------------------
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/s ite_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
.

--------------------------------------------

------=_Part_27226_16979169.1184069046924--

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

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: erwan.lemonnier@gmail.com
> [mailto:erwan.lemonnier@gmail.com] 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:
>
> --------------------------------------------
> use strict;
> use warnings;
> use Data::Dumper;
> use Test::More tests =3D> 1;
> use Carp qw(confess);
> use DBI;
>
> # database credentials: EDIT HERE
> my $ORASID =3D $ENV{ORACLE_SID};
> my $ORAUSR =3D 'username';
> my $ORAPWD =3D 'password';
>
> my $DBC;
>
> sub sql_execute {
> my ($sql,@arg) =3D @_;
> my $sth =3D $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 =3D DBI->connect("dbi:Oracle:$ORASID",$ORAUSR,$ORAPWD,
> {
> PrintError=3D>0,
> AutoCommit=3D>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 =3D sql_execute("SELECT DATA FROM
> test_oracle_bug")->fetchrow_arrayref;
> my ($val) =3D @$ret;
>
> my $sum =3D 1.73696 - $val;
> is($sum,0,"does sum $sum == 0?");
>
> --------------------------------------------
>
> 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 =3D PVNV(0x9ed8860) at 0x9e2e8e0
> REFCNT =3D 1
> FLAGS =3D (PADBUSY,PADMY,NOK,POK,pNOK,pPOK)
> IV =3D 0
> NV =3D 0
> PV =3D 0x9f45e48 "0"\0
> CUR =3D 1
> LEN =3D 35
>
> In 5.8.8, it is:
>
> SV =3D PVNV(0x9b6f898) at 0x9ab8c44
> REFCNT =3D 1
> FLAGS =3D (PADBUSY,PADMY,NOK,POK,pNOK,pPOK)
> IV =3D 0
> NV =3D 2.22044604925031e-16
> PV =3D 0x9beca78 "2.22044604925031e-16"\0
> CUR =3D 20
> LEN =3D 36
>
> in 5.8.8, 'print Dumper(float_parts($sum));' says that $sum contains:
>
> $VAR1 =3D '+';
> $VAR2 =3D -52;
> $VAR3 =3D '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
> (brewbuilder@ls20-bc2-14.build.redhat.com) (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
>
> --------------------------------------------
> Summary of my perl5 (revision 5 version 8 subversion 8) configuration:
> Platform:
> osname=3Dlinux, osvers=3D2.6.9-55.elsmp, archname=3Di686-linux
> uname=3D'linux dpluplu3 2.6.9-55.elsmp #1 smp fri apr 20
> 17:03:35 edt 2007
> i686 i686 i386 gnulinux '
> config_args=3D''
> hint=3Drecommended, useposix=3Dtrue, d_sigaction=3Ddefine
> usethreads=3Dundef use5005threads=3Dundef useithreads=3Dundef
> usemultiplicity=3Dundef
> useperlio=3Ddefine d_sfio=3Dundef uselargefiles=3Ddefine usesocks=
=3Dundef
> use64bitint=3Dundef use64bitall=3Dundef uselongdouble=3Dundef
> usemymalloc=3Dn, bincompat5005=3Dundef
> Compiler:
> cc=3D'cc', ccflags =3D'-fno-strict-aliasing -pipe
> -Wdeclaration-after-statement -I/usr/local/include -D_LARGEFILE_SOURCE
> -D_FILE_OFFSET_BITS=3D64',
> optimize=3D'-O3',
> cppflags=3D'-fno-strict-aliasing -pipe -Wdeclaration-after-statemen=
t
> -I/usr/local/include'
> ccversion=3D'', gccversion=3D'3.4.6 20060404 (Red Hat 3.4.6-8)',
> gccosandvers=3D''
> intsize=3D4, longsize=3D4, ptrsize=3D4, doublesize=3D8, byteorder=
=3D1234
> d_longlong=3Ddefine, longlongsize=3D8, d_longdbl=3Ddefine,
> longdblsize=3D12
> ivtype=3D'long', ivsize=3D4, nvtype=3D'double', nvsize=3D8, Off_t=
=3D'off_t',
> lseeksize=3D8
> alignbytes=3D4, prototype=3Ddefine
> Linker and Libraries:
> ld=3D'cc', ldflags =3D' -L/usr/local/lib'
> libpth=3D/usr/local/lib /lib /usr/lib
> libs=3D-lnsl -ldb -ldl -lm -lcrypt -lutil -lc
> perllibs=3D-lnsl -ldl -lm -lcrypt -lutil -lc
> libc=3D/lib/libc-2.3.4.so, so=3Dso, useshrplib=3Dfalse,
> libperl=3Dlibperl.a
> gnulibc_version=3D'2.3.4'
> Dynamic Linking:
> dlsrc=3Ddl_dlopen.xs, dlext=3Dso, d_dlsymun=3Dundef, ccdlflags=3D'-=
Wl,-E'
> cccdlflags=3D'-fpic', lddlflags=3D'-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=3D"/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
> .
>
> --------------------------------------------
>



- - - - - Appended by Scientific Atlanta, a Cisco company - - - - - =

This e-mail and any attachments may contain information which is confiden=
tial,
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, yo=
u are
not authorized to read, print, retain, copy or disseminate this message o=
r 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.

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

--/2994txjAzEdQwm5
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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:
>=20
> 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 ins=
ert
> 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;
>=20
> # fetch numeric from table
> my $ret =3D sql_execute("SELECT DATA FROM
> test_oracle_bug")->fetchrow_arrayref;
> my ($val) =3D @$ret;
>=20
> my $sum =3D 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) =3D ('1.73696');
=20
my $sum =3D 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

001111111111101111001010100101101001000110100111010111001101 0000

if it comes from an Oracle database, but convert it to=20

001111111111101111001010100101101001000110100111010111001101 0001

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


--=20
_ | Peter J. Holzer | If I wanted to be "academically correct",
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | hjp@wsr.ac.at | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users

--/2994txjAzEdQwm5
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGk+47MdFfQa64PCwRApFUAJ93Zj1uFn7QjgyLg8D0Js6fv5OjnACe NmZt
FED4urbrjsG1we3nX0uQ8WU=
=4A6l
-----END PGP SIGNATURE-----

--/2994txjAzEdQwm5--

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/msg316 63.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
>
> 001111111111101111001010100101101001000110100111010111001101 0000
>
> if it comes from an Oracle database, but convert it to
>
> 001111111111101111001010100101101001000110100111010111001101 0001
>
> 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

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

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@gmail.com
> [mailto:erwan.lemonnier@gmail.com] On Behalf Of Erwan Lemonnier
> Sent: Wednesday 11 July 2007 04:29
> To: dbi-users@perl.org; hjp@wsr.ac.at
> 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/msg316 63.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) =3D ('1.73696');
> >
> > my $sum =3D 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
> >
> > 001111111111101111001010100101101001000110100111010111001101 0000
> >
> > if it comes from an Oracle database, but convert it to
> >
> > 001111111111101111001010100101101001000110100111010111001101 0001
> >
> > 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 confiden=
tial,
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, yo=
u are
not authorized to read, print, retain, copy or disseminate this message o=
r 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.

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"" wrote in message
news:7B8C821E108976489746D62B099CC397011398C1@SACATOREXCH01. corp.sa.net...
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@gmail.com
> [mailto:erwan.lemonnier@gmail.com] On Behalf Of Erwan Lemonnier
> Sent: Wednesday 11 July 2007 04:29
> To: dbi-users@perl.org; hjp@wsr.ac.at
> 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/msg316 63.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
> >
> > 001111111111101111001010100101101001000110100111010111001101 0000
> >
> > if it comes from an Oracle database, but convert it to
> >
> > 001111111111101111001010100101101001000110100111010111001101 0001
> >
> > 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.

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

The discussion started by
http://www.nntp.perl.org/group/perl.dbi.users/2007/07/msg316 63.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.

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

I personally tried some simple experiments with gcc. I found that
printf( "%0.20lg", x ) where x is a double with value 1.73696, yielded
this value:
1.7369600000000000595

However, feeding that back to a double variable using sscanf() resulted
in a value that tested equal to 1.73696. Using sscanf() to convert
"1.73696" to a double also resulted in equality to 1.73696. I tested
this using different versions of gcc (3.4.2 and 4.1.2 I think). Same
result.

So I found nothing conclusive that can be blamed on the C compiler.
There might be some other conversion that broke. I would tend to think
the C compiler and library are more important than the Perl version,
because Perl is compiled using C and uses the C libraries. For that
matter, so do extension libraries such as the DBDs and DBI.

Another possibility is that one of the conversions in DBI uses sprintf()
with not enough precision, but that shouldn't break a number like
1.73696 that terminates in its decimal representation with zero so
early, one would think.

Regarding alternative representations, I believe the following setup is
worth considering for anyone doing high-precision financial
calculations:

In the database, use NUMERIC with a high precision.
In Perl do a
use Math::BigFloat;
or some variant of that; the man pages give variants.

I personally don't have direct experience with this setup, since my Perl
work tends to be non-financial. But it looks like this combination of
tools would give precise results on financial values. (I have used the
BigInt library with success.)

-Will

> -----Original Message-----
> From: erwan.lemonnier@gmail.com
> [mailto:erwan.lemonnier@gmail.com] On Behalf Of Erwan Lemonnier
> Sent: Tuesday 17 July 2007 08:52
> To: dbi-users@perl.org
> Subject: Re: Re: float bug? perl 5.8, DBI and oracle 10.2.0
>
>
> The discussion started by
> http://www.nntp.perl.org/group/perl.dbi.users/2007/07/msg316 63.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 =3D $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.
>



- - - - - Appended by Scientific Atlanta, a Cisco company - - - - - =

This e-mail and any attachments may contain information which is confiden=
tial,
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, yo=
u are
not authorized to read, print, retain, copy or disseminate this message o=
r 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.

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

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/msg316 63.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
csarnows@pcbi.upenn.edu
Penn Bioinformatics Core
1316 Blockley Hall MC 6021
(215) 746-7784

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

On Tue, Jul 17, 2007 at 11:11:49AM -0400, Christopher Sarnowski wrote:
> 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.

For the record, DBD::Oracle binds parameters and fetches values as strings.

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

Yeap.

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

This is a key point. When you're on this kind of investigation you must
assume nothing and check everything with great care.

Funnily enough I wrote a section on this topic back in May 2006 for the
2nd edition of DBI book (which is currently shelved, by the way).
I've appended the relevant chunk of the rough draft. Comments welcome.

Tim.


=head0 Handling Database Data Types

We've talked a lot about fetching data. Fetching it all at once,
fetching it row by row, fetching into arrays, and fetching into hashes.
But fetching what? What is this stuff we've been fetching?

Data, I hear you say. Strings and numbers and stuff like that. Well, strings
and numbers may seem simple, and usually they are, but even here there are
issues you should be aware of. Then there are more complex types like dates and
LOBs with their own set of complications to consider. Finally we need to
discuss NULLs and how the absense of a value is represented and handled.

[...string sections skipped...]

=head1 Numeric Types

Let's take a look at integer, fixed point, and floating point values now.
These types are more straightforward than character strings but there
are still issues you should be aware of.

=head2 The Numbers of Perl

Before we talk about databases and drivers and such like I
should outline the way perl handles numeric values.
N The intrepid and curious might like to look at the perl source code.
Especially the comments for NV_PRESERVES_UV in sv.c and PERL_PRESERVE_IVUV
in pp_hot.c>

Internally perl has three basic types that are relevant here: integer
values (known as IV), floating point values (known as NV), and strings.

=head3 Perl Integer Values

Integers are typically stored as 32 or 64 bit (4 or 8 byte) values
depending on how perl was configured. You can check the size of integers
in your perl by running C and looking for C in the output.
The range of a 32 bit integer is -2,147,483,648 to 2,147,483,647
(10 digits of precision). The range of a 64 bit integer is
-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (19 digits
of precision).

=head3 Perl Floating Point Values

Technically the term "floating point" refers to a number representation
consisting of a I, C, and an I, C. The number
represented is the value of C. But what does that mean?

Basically, a floating point value is represented internally by two
values. One value, the mantissa, holds a binary I of the
significant digits and another value, the exponent, is used to indicate
where the decimal point should be. It may be within the significant
digits but it may also be way off to the right (positive mantissa) or
left (negative mantissa).

Floating point values are typically stored in 64 bits or sometimes 96
bits (that's 8, or 12 bytes) depending on how your perl was configured.
You can check the size used in your perl by running C and
looking for C in the output. The 64 bit floats are known as
I and have approximately 15 digits of precision between 1e-307
to 1e+308, and the 96 bit floats are known as I and have
approximately 18 digits of precision between 1e-4931 to 1e+4932. Some
systems support 128 bit I with even greater precision and
scale.

It's becoming more common for perl to be configured with 64 bit
integers but still using 64 bit floating point values. But a 64 bit
integer has 19 digits of precision whereas a 64 bit floating point
value only has approximately 18. This is important to know because it
means that a large integer may loose precision if it's involved in a
calculation that causes it to be converted to a floating point value
(which is basically anything more involved that addition or subtraction
of another integer).

If you ask perl to print a value where the decimal point is outside the
significant digits it'll format the value using exponential notation,
where the mantissa and exponent are written separately with an C
between them. You can get the same effect using C.

Here are a few examples to help illustrate all this:

print 1000000000000000.0 , "\n";
print 1000000000000000.0 * 10 , "\n"; # shift decimal place
print 0.0000000000000001 , "\n";
print 0.0000000000000001 * 10 , "\n"; # shift decimal place
print 1 / 299792458 , "\n";
printf "%e\n", 42.1;

prints:

1e+15
1e+16
1e-16
1e-15
3.33564095198152e-09
4.210000e+01

=head3 Beware the Creeping Errors

Floating point values are so common, so pervasive, and so effective that
it's easy to forget that their great utility comes with a price: subtle
loss of precision, even for small values.

Here's a little script that adds 42.1 one thousand times:

$a = 0;
$a += 42.1 for (1..1000);
print "$a\n";

Well, 42.1 * 1000 is 42100, so that's what it should print.
But it doesn't, it prints:

42099.9999999992

This sort of thing gives accountants nightmares. Your mileage may vary as
floating point systems can vary, leading to further nightmares. Try it
for yourself.

You might be lucky and find it prints the right number, but don't think
that means all is well. All it means is that your perl was configured
with I. The underlying problem is still there, we just
need to push a little harder to make it show itself. Change the C<1000>
above to C<1000000>, or more, and you'll see the loss of precision.
[XXX check that 1000000 does exhibit the problem on long double
systems.]

You may remember I said that the mantissa part of the internal floating
point representation holds a I of the significant
digits. It's that binary approximation that's the cause of the problems.

I don't want to delve further into the intricacies of why floating point
values behave this way. There's no need. There are just two things you
need to know:

Firstly, that floating point values often contain I small
errors (that don't show up when you print them) so you need to take care
when working with them to avoid accumulating those errors into nasty
surprises like the one above.

Secondly, that those 'imperceptably small errors' mean that just because
two floating point values I to be the same doesn't mean they
are. Take a look at this example:

$a = 42.1; $a += 0.1 for (1..30); # add 3 one way
$b = 42.1; $b += 0.3 for (1..10); # add 3 another way
print "a=$a b=$b\n";
($a == $b) ? print "Equal\n" : print "Not equal!\n";

it prints:N
systems.>

a=45.1 b=45.1
Not equal!

This is because the test for equality checks the underlying binary
representation of the values and thereby includes those imperceptable
errors.

=head3 Perl String Values

Whenever a string is used in a numeric context perl will try to
interpret the contents of the string as a number. Before perl 5.8
strings were always converted to floating point values. From perl
5.8 onwards a string will become an integer value if appropriate.
Strings that don't look like numbers are converted to 0 and a warning
generated if perl is running with the -w flag (which is highly
recommended).

What if you need to work with larger values, or greater precision,
than your perl can handle? With perl there is always a way and the way
here is to use the Math::BigInt and/or Math::BigFloat modules which
come with perl. Those modules let you work with arbitary sized integers
and floating point values.

Now, back to your regularly scheduled database programming...

=head2 Integer Values

Integers. 0, 1, 2, 3, -7, 42. You can't get much simpler than integers.
But do you I integers at all?

Some databases support very large integers. Oracle, for example, supports
integers with 38 digits of precision. That's far beyond the 10 digits of a
simple 32bit integer and even the 19 digits of a 64bit integer. Because of
this the DBD::Oracle driver returns integers, and indeed all other numberic
types, as strings.

By returing a string the driver avoids the issue of how best to deal with
values outside the range supported by Perls native types. The application is
then free to do whatever is most appropriate. Often that's to simply duck the
issue as well, in which case perl will convert the strings to floating point
values and everything will be, or seem to be, just fine. However, if the
application does care about precision then it can use the Math::BigInt and/or
Math::BigFloat modules mentioned above.

Perhaps you don't think it would matter much if 1234567890123456
becomes 1.23456789012346e+15. After all it's only lost one digit
of precision in a very large number. Who'd notice? Apart from your
boss you'll find that databases are picky about accuracy as well.
If 1234567890123456 was a value fetched from the database and you
tried to update it by executing

UPDATE table SET foo = foo - 42 WHERE foo = 1.23456789012346e+15

you'll be disappointed.

The DBI will probably gain a way to hook into the fetching of a
value from the database so that the use of Math::BigInt, for example,
can be made transparent and not clutter up the code. But that hasn't
happened yet. XXX

Many databases support multiple sizes of integer types from 1 to 8
bytes in size with INTEGER (4 bytes) and SMALLINT (2 bytes) being
the most common.N one underlying numeric type which is variable width and all other
numeric types are aliases for it.>

The standard integer types are INTEGER and SMALLINT, with BIGINT and
TINYINT being newer additions. The TYPE numbers are 4, 5, and -5, -6
respectively.

=head2 Fixed Point Values

Databases often provide a fixed point numeric type called
NUMERIC, or DECIMAL, or both N<
Officially the only difference between NUMERIC and DECIMAL is that
NUMERIC has a precision I to the one specified in the type
declaration and DECIMAL has the same I precision than the
declared one. It's a classic fudge from the early days of SQL
standardization. Most databases just use the same type for both.>.

You can think of fixed point values as being like a string of digits
of a fixed maximum length with a decimal point at a fixed position.
Hence the name: fixed point. The key, er, point about fixed point values
is that they never loose precision. Unlike floating point, which
I'll describe next, there's no fuzzyness in the value. That makes
them very useful where accuracy is required, which is commonly the
case with money, for some reason.

Consider this table:

CREATE TABLE pay {
id INTEGER,
amount DECIMAL(5,2)
}

In this example, 5 is the I and 2 is the I.
The precision is the I number of significant decimal digits
that will be stored for values, and the scale is the number of
those digits that will be stored I the decimal point.

The value 42.1 would be stored in a DECIMAL(5,2) field as:

<- precision -> 5
[ ] [4] [2] . [1] [0]
<- scale -> 2

If the scale is 0 then the value will have no decimal point or
fractional part.

Standard SQL requires that the amount column be able to store any value
with 5 digits in total with 2 of those after the decimal point. In this
case, therefore, the range of values that can be stored in the amount
column is from -999.99 to 999.99.

Drivers should typically return these values as strings for the same reasons
that some return integers as strings: it avoids the driver being the cause of a
loss of precision. Also take note of the cautions in the Floating Point Values
section below as they can also apply to fixed point values.

One more thing to keep in mind about fixed point types: zero isn't false.
A zero stored in a DECIMAL(5,2) field will probably be returned as "C<0.00>"
so code like this:

$hourly_rate = $row->{hourly_rate} || 0.42; # if zero then use default value

won't default a zero value to 0.42. I've seen this cause bugs in production
systems more than once. The "zero is false" concept runs deep in Perl
developers and can make it hard to spot this problem.

The standard fixed point types are NUMERIC and DECIMAL, with TYPE codes
of 2 and 3 respectively.

=head2 Floating Point Values

Having discussed I point values you ought to be able to guess
what I point means: you still have a precision of a certain
number of digits but instead of the decimal point being fixed at a
certain location, it 'floats'. But what does that mean?

Take a moment to reread "Perl Floating Point Values" on page XXX if you
haven't just read it. That's where I discuss some subtle but important
issues with floating point values in general. Here on I'll just focus on
how those issues apply in the context of the DBI.

The standard floating point types are FLOAT, REAL, and DOUBLE, which
have standard TYPE codes of 6, 7, and 8 respectively.

In most databases, the REAL type is half the size of the DOUBLE type and
has a range of at least 1E-37 to 1E+37 with a precision of at least 6
decimal digits. The DOUBLE type typically has a range of around 1E-307
to 1E+308 with a precision of at least 15 digits.

=head3 Floating Ambiguity

The FLOAT type is typically a 'smart alias' for either REAL or DOUBLE
depending on the value of an optional value in parenthesis:
C)>.

It's best avoided for applications wishing to be portable because
databases differ in how they interpret the value of I. Some interpret
it as the precision in I digits, which matches the SQL standard,
while others interpret it as the precision in I digits.

=head3 Beyond the Fringe

Values that are too large or too small may cause an error, or may be
capped at infinity (positive or negative infinity, as appropriate).

Values with more significant digits than can be represented by the type
used may be rounded to fit, or truncated to fit, or cause an error.

Numbers too close to zero that are not representable as distinct from
zero may cause an underflow error, or may be silently changed to 0.

May this, may that. It all depends on the database. Are you having fun yet?

=head3 Behind the String

Back in "Perl Floating Point Values" on page XXX we looked at how two
numbers that appear to be the same may not be equal. Here's another
variation on the same theme, but this one is more directly relevant to
databases and the DBI:

$a = 42.1; $a += 0.1 for (1..30); # add 3
$b = "$a"; # cross the client/server interface as text
print "a=$a b=$b\n";
($a == $b) ? print "Equal\n" : print "Not equal!\n";

I'm sure you can guess what that prints:

a=45.1 b=45.1
Not equal!

Depending on the database and driver used the conversion two/from text
at the client/server interface can apply in either direction and happen
at the client, or at the server, or not at all. Or perhaps you did it
yourself without noticing:

# select the lowest value of the foo field
$min_foo = $dbh->selectrow_array("SELECT MIN(foo) FROM TABLE");
# delete all records with that value
$dbh->do("DELETE FROM TABLE WHERE foo = $min_foo");

Using a placeholder I help:

$dbh->do("DELETE FROM TABLE WHERE foo = ?", undef, $min_foo);

but isn't guaranteed to, and it almost certainly won't if the driver
emulates placeholders by substituting values into the SQL statement.
(We'll talk about placeholders in "Adding Parameters to Statements" on
page XXX.)

XXX xref this above from optimistic locking

=head3 Getting the Point

If you live in a country that uses a full stop ("C<.>") as the decimal
point character it may come as a surprise to you that large parts of
the world don't. Many countries use a comma ("C<,>") instead.

The rules about how numbers and other types are formatted in different
places are known as I.

All is fine so long as your application and the database you're talking
to both agree on the format to use. But if they don't agree, perhaps
because they're in different countries, or are using different locales
for some other reason, then you'll get some unpleasant surprises.

When sending floating point values to the database as strings some databases
will complain about invalid characters in the value with an error.
That's good, or at least it's better than other databases which will
silently tuncate or otherwise mangle the number.

When fetching floating point values from the database as strings you'll
get a string formatted by the database server according to it's rules.
If you use that value in a numeric context perl will try to convert it
to a number for you. You'll only get a warning about any invalid characters
if you're running perl with warnings enabled (via the C<-w> option or
the C pragma).

=head3 Getting the Value

DBI drivers for databases which use standard floating point types, and
can return floating point values as floating point values, ought to do so
in order to avoid an obvious cause of imperceptable changes in the value.

It would be tempting to think that that would completely avoid the
problem. Sadly that's not the case. Yes, it does significantly reduce
it, but don't be lulled into a false sense of security. There are still
other ways imperceptable differences can creep in.

=head3 In Summary

The bottom line here is to avoid working with floating point values in a
way that accumlates the hidden errors, and avoid any logic that assumes
two floating point values will be equal.

I've only touched on the main issue that you need to be aware of when
working with floating point values but there are others. Lots of them!
For example, the standard floating point format, known as IEEE
754N >, defines special values
like "C" for Infinity, "C" for Not a Number, even the humble
zero has a positive and negative version (though they are treated as
being equal, thankfully). But, you can go a long way without knowing
more than that, so I'll stop here.

=cut

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

Hi again! Thanks Tim for this thorough explanation :)

> For the record, DBD::Oracle binds parameters and fetches values as strings.

Great, now we are getting closer to the heart :)

As I wrote in my first post, the problem can be triggered with the
following perl test:

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

--------------------------------------------

As you see, the numeric value is inserted in the database with:

"INSERT INTO test_oracle_bug (DATA) VALUES (1.73696)"

I therefore assume that we are passing a string forward to
DBD::Oracle, and that it is a string that gets inserted into the
column DATA of type NUMBER in oracle. If I understood Tim well, the
number 1.73696 is then stored as a decimal string in the oracle
database.

When we later retrieve this value ("my ($val) = @$ret;") from the
table, what we get in $val should then be the same string, as returned
by DBD::Oracle. A look at $val with Devel::Peek confirms this:

"Dump($val);" says:

SV = PV(0x97bf684) at 0x988aed8
REFCNT = 1
FLAGS = (PADBUSY,PADMY,POK,pPOK)
PV = 0x9aaff18 "1.73696"\0
CUR = 7
LEN = 8

No IV (integer value) or NV (native float) in this SV (scalar value),
just a PV (string).

From there on, it would seem reasonable to believe that we can exclude
a DBI/DBD::Oracle problem: what we are dealing with is a string
representation of 1.73696.

Now, we reach the next line of code: "my $sum = 1.73696 - $val;". This
line evals to $sum = 2.22044604925031e-16. That's the problem.

However! if I try getting the same effect from pure perl code:

--------------------------------------------

use strict;
use warnings;
use Test::More tests => 1;

my $val = "1.73696";
my $sum = 1.73696 - $val;

is($sum,0,"sum is 0");

--------------------------------------------

Then the test passes, $sum is 0.

In other words, "1.73696 - $val" evaluates to 0 when $val is the
string "1.736960" hardcoded in the source code, but does not when $val
is the *same* string but returned by fetchrow_arrayref. Notice here
that according to Devel::Peek, the SV of the hardcoded $val and that
of the retrieved $val are identical. But obviously, something between
them must differ, otherwise the same operation on both would yield the
same result 8(

Is there any reason why the string 1.73696 returned by
fetchrow_arrayref would differ from the string "1.73696" hardcoded in
source code?

I have a feeling that Tim's example holds the answer:

$a = 42.1; $a += 0.1 for (1..30); # add 3
$b = "$a"; # cross the client/server interface as text
print "a=$a b=$b\n";
($a == $b) ? print "Equal\n" : print "Not equal!\n";

Need to think more...

/Erwan

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

Erwan Lemonnier wrote:
> Hi again! Thanks Tim for this thorough explanation :)
>
>> For the record, DBD::Oracle binds parameters and fetches values as
>> strings.
>
> Great, now we are getting closer to the heart :)
>
> As I wrote in my first post, the problem can be triggered with the
> following perl test:
>
> --------------------------------------------
> 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?");
>
> --------------------------------------------

I don't want to get involved in the mechanics of your specific problem
but as an aside if wanted to do what your example does I'd let the
database work it out:

select data - ? from test_oracle_bug
$sth->execute("1.73696")

then all the maths is done at the database end.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com



Martin

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

Hello folk!

Now I think I got a clean shot at what's troubling me 8-)
Consider the following code:

-----------------------------------------------
use strict;
use warnings;
use DBI;

my $ORASID = $ENV{ORACLE_SID};
my $ORAUSR = 'username';
my $ORAPWD = 'password';

sub showbin {
print "bin: ".unpack("B70",reverse pack("d",$_[0]))."\n";
}

my $v1 = "1.73696";
showbin($v1);

print "connecting\n";
my $DBC = DBI->connect("dbi:Oracle:$ORASID",$ORAUSR,$ORAPWD,
{ PrintError=>0, AutoCommit=>0 } );

my $v2 = "1.73696";
showbin($v2);

-----------------------------------------------

This code simply opens a connection toward an oracle database. And
shows the binary representation of the string "1.73696" converted into
a native float (NV) before and after we opened the connection.

When I run it with perl 5.6.2, DBI 1.38 and DBD::Oracle 1.17, it says:

[HEAD] ~/HEAD/test/t> !967$ /opt/perl-5.6.2/bin/perl 04_test1.t
bin: 001111111111101111001010100101101001000110100111010111001101 0001
connecting
bin: 001111111111101111001010100101101001000110100111010111001101 0001

When I run it with perl 5.8.8, DBI 1.58 and DBD::Oracle 1.19, it says:

[HEAD] ~/HEAD/test/t> !969$ /opt/perl-5.8.8/bin/perl 04_test1.t
bin: 001111111111101111001010100101101001000110100111010111001101 0001
connecting
bin: 001111111111101111001010100101101001000110100111010111001101 0000

See how the least significant bit (last one on the right) changes in
the last run?
There we have it. It is what caused the problems I have been spamming
you all with for the last few days :)

Conclusion: on my host (perl 5.8 etc.), the line:

my $DBC = DBI->connect("dbi:Oracle:$ORASID",$ORAUSR,$ORAPWD,
{ PrintError=>0, AutoCommit=>0 } );

seems to alter the way perl parses the string "1.73696". This later
resulted in arithmetic errors that looked like floating point related
issues but were not.

Has anyone any idea of what's happening here????

/Erwan

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

------=_Part_29324_28602331.1184768874737
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 7/18/07, Erwan Lemonnier wrote:
>
> Hello folk!
>
> Now I think I got a clean shot at what's troubling me 8-)
> Consider the following code:
>
> -----------------------------------------------
> use strict;
> use warnings;
> use DBI;
>
> my $ORASID = $ENV{ORACLE_SID};
> my $ORAUSR = 'username';
> my $ORAPWD = 'password';
>
> sub showbin {
> print "bin: ".unpack("B70",reverse pack("d",$_[0]))."\n";
> }
>
> my $v1 = "1.73696";
> showbin($v1);
>
> print "connecting\n";
> my $DBC = DBI->connect("dbi:Oracle:$ORASID",$ORAUSR,$ORAPWD,
> { PrintError=>0, AutoCommit=>0 } );
>
> my $v2 = "1.73696";
> showbin($v2);
>
> -----------------------------------------------
>
> This code simply opens a connection toward an oracle database. And
> shows the binary representation of the string "1.73696" converted into
> a native float (NV) before and after we opened the connection.
>
> When I run it with perl 5.6.2, DBI 1.38 and DBD::Oracle 1.17, it says:
>
> [HEAD] ~/HEAD/test/t> !967$ /opt/perl-5.6.2/bin/perl 04_test1.t
> bin: 001111111111101111001010100101101001000110100111010111001101 0001
> connecting
> bin: 001111111111101111001010100101101001000110100111010111001101 0001
>
> When I run it with perl 5.8.8, DBI 1.58 and DBD::Oracle 1.19, it says:
>
> [HEAD] ~/HEAD/test/t> !969$ /opt/perl-5.8.8/bin/perl 04_test1.t
> bin: 001111111111101111001010100101101001000110100111010111001101 0001
> connecting
> bin: 001111111111101111001010100101101001000110100111010111001101 0000
>
> See how the least significant bit (last one on the right) changes in
> the last run?
> There we have it. It is what caused the problems I have been spamming
> you all with for the last few days :)
>
> Conclusion: on my host (perl 5.8 etc.), the line:
>
> my $DBC = DBI->connect("dbi:Oracle:$ORASID",$ORAUSR,$ORAPWD,
> { PrintError=>0, AutoCommit=>0 } );
>
> seems to alter the way perl parses the string "1.73696". This later
> resulted in arithmetic errors that looked like floating point related
> issues but were not.
>
> Has anyone any idea of what's happening here????
>


Silly question time - I assume that if you don't includes the DBI->connect
line, then the two invocations of showbin produce the same output in both
versions of Perl.


Somewhere in "Elements of Programming Style" by Kernighan & Plauger, it says
words to the effect that:

A wise programmer once said 'moving floating point numbers is like moving
sand piles; every time you do, you lose a little sand and you pick up a
little dirt'.

--
Jonathan Leffler #include
Guardian of DBD::Informix - v2007.0226 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."

------=_Part_29324_28602331.1184768874737--

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

Tim, I have a couple of feedback comments on your text.

A) I would not characterise 32-bit signed integers as giving 10 digits
of precision as you did. They give log10( 2^31 ) ~=3D 9.3319 digits of
precision. Since you can't count on the full 10th digit, I would
truncate and tell people you get 9 digits of precision. Similarly a
signed 64-bit integer gives 18.9649 or really just 18 digits of
precision to be safe, but 128-bit signed integers give you a full 38
digits.

B) long double is not usually 96 bits, but rather 80 bits. Most
machines that people use follow the IEEE 754 standard, which says >=3D79
bits but is normally implemented as 80 bits.

Good explanation generally.

For the general list, I'm still interested in the issue of alternative
representations for financial work. Has anyone had much experience with
the ( NUMERIC , Math::BigFloat ) combination? Is that used generally by
people in the field?

-Will


> -----Original Message-----
> From: Tim Bunce [mailto:Tim.Bunce@pobox.com]
> Sent: Tuesday 17 July 2007 19:02
> To: Christopher Sarnowski
> Cc: erwan@lemonnier.se; dbi-users@perl.org
> Subject: Re: float bug? perl 5.8, DBI and oracle 10.2.0
>
> . . .
>
> Funnily enough I wrote a section on this topic back in May
> 2006 for the
> 2nd edition of DBI book (which is currently shelved, by the way).
> I've appended the relevant chunk of the rough draft. Comments welcome.
>
> Tim.
>
> . . .
>
> =3Dhead3 Perl Integer Values
>
> Integers are typically stored as 32 or 64 bit (4 or 8 byte) values
> depending on how perl was configured. You can check the size
> of integers
> in your perl by running C and looking for C
> in the output.
> The range of a 32 bit integer is -2,147,483,648 to 2,147,483,647
> (10 digits of precision). The range of a 64 bit integer is
> -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (19 digits
> of precision).
> . . .
> =3Dhead3 Perl Floating Point Values
>
> Floating point values are typically stored in 64 bits or sometimes 96
> bits (that's 8, or 12 bytes) depending on how your perl was
> configured.
> You can check the size used in your perl by running C and
> looking for C in the output. The 64 bit floats are known as
> I and have approximately 15 digits of precision
> between 1e-307
> to 1e+308, and the 96 bit floats are known as I and have
> approximately 18 digits of precision between 1e-4931 to 1e+4932. Some
> systems support 128 bit I with even greater
> precision and
> scale.
>
> It's becoming more common for perl to be configured with 64 bit
> integers but still using 64 bit floating point values. But a 64 bit
> integer has 19 digits of precision whereas a 64 bit floating point
> value only has approximately 18. This is important to know because it
> means that a large integer may loose precision if it's involved in a
> calculation that causes it to be converted to a floating point value
> (which is basically anything more involved that addition or
> subtraction
> of another integer).
> . . .
>
> Some databases support very large integers. Oracle, for
> example, supports
> integers with 38 digits of precision. That's far beyond the
> 10 digits of a
> simple 32bit integer and even the 19 digits of a 64bit
> integer. Because of
> this the DBD::Oracle driver returns integers, and indeed all
> other numberic
> types, as strings.
>
> The DBI will probably gain a way to hook into the fetching of a
> value from the database so that the use of Math::BigInt, for example,
> can be made transparent and not clutter up the code. But that hasn't
> happened yet. XXX
>
> Many databases support multiple sizes of integer types from 1 to 8
> bytes in size with INTEGER (4 bytes) and SMALLINT (2 bytes) being
> the most common.N > one underlying numeric type which is variable width and all other
> numeric types are aliases for it.>
>



- - - - - Appended by Scientific Atlanta, a Cisco company - - - - - =

This e-mail and any attachments may contain information which is confiden=
tial,
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, yo=
u are
not authorized to read, print, retain, copy or disseminate this message o=
r 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.

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

On Wed, Jul 18, 2007 at 10:58:58AM -0400, Rutherdale, Will wrote:
> Tim, I have a couple of feedback comments on your text.
>
> A) I would not characterise 32-bit signed integers as giving 10 digits
> of precision as you did. They give log10( 2^31 ) ~= 9.3319 digits of
> precision. Since you can't count on the full 10th digit, I would
> truncate and tell people you get 9 digits of precision. Similarly a
> signed 64-bit integer gives 18.9649 or really just 18 digits of
> precision to be safe, but 128-bit signed integers give you a full 38
> digits.
>
> B) long double is not usually 96 bits, but rather 80 bits. Most
> machines that people use follow the IEEE 754 standard, which says >=79
> bits but is normally implemented as 80 bits.

Okay.

> Good explanation generally.

Thanks for the feedback Will. I'll make some changes.

> For the general list, I'm still interested in the issue of alternative
> representations for financial work. Has anyone had much experience with
> the ( NUMERIC , Math::BigFloat ) combination? Is that used generally by
> people in the field?

Might be worth meditating on that question over at http://perlmonks.org

Tim.

>
> -Will
>
>
> > -----Original Message-----
> > From: Tim Bunce [mailto:Tim.Bunce@pobox.com]
> > Sent: Tuesday 17 July 2007 19:02
> > To: Christopher Sarnowski
> > Cc: erwan@lemonnier.se; dbi-users@perl.org
> > Subject: Re: float bug? perl 5.8, DBI and oracle 10.2.0
> >
> > . . .
> >
> > Funnily enough I wrote a section on this topic back in May
> > 2006 for the
> > 2nd edition of DBI book (which is currently shelved, by the way).
> > I've appended the relevant chunk of the rough draft. Comments welcome.
> >
> > Tim.
> >
> > . . .
> >
> > =head3 Perl Integer Values
> >
> > Integers are typically stored as 32 or 64 bit (4 or 8 byte) values
> > depending on how perl was configured. You can check the size
> > of integers
> > in your perl by running C and looking for C
> > in the output.
> > The range of a 32 bit integer is -2,147,483,648 to 2,147,483,647
> > (10 digits of precision). The range of a 64 bit integer is
> > -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (19 digits
> > of precision).
> > . . .
> > =head3 Perl Floating Point Values
> >
> > Floating point values are typically stored in 64 bits or sometimes 96
> > bits (that's 8, or 12 bytes) depending on how your perl was
> > configured.
> > You can check the size used in your perl by running C and
> > looking for C in the output. The 64 bit floats are known as
> > I and have approximately 15 digits of precision
> > between 1e-307
> > to 1e+308, and the 96 bit floats are known as I and have
> > approximately 18 digits of precision between 1e-4931 to 1e+4932. Some
> > systems support 128 bit I with even greater
> > precision and
> > scale.
> >
> > It's becoming more common for perl to be configured with 64 bit
> > integers but still using 64 bit floating point values. But a 64 bit
> > integer has 19 digits of precision whereas a 64 bit floating point
> > value only has approximately 18. This is important to know because it
> > means that a large integer may loose precision if it's involved in a
> > calculation that causes it to be converted to a floating point value
> > (which is basically anything more involved that addition or
> > subtraction
> > of another integer).
> > . . .
> >
> > Some databases support very large integers. Oracle, for
> > example, supports
> > integers with 38 digits of precision. That's far beyond the
> > 10 digits of a
> > simple 32bit integer and even the 19 digits of a 64bit
> > integer. Because of
> > this the DBD::Oracle driver returns integers, and indeed all
> > other numberic
> > types, as strings.
> >
> > The DBI will probably gain a way to hook into the fetching of a
> > value from the database so that the use of Math::BigInt, for example,
> > can be made transparent and not clutter up the code. But that hasn't
> > happened yet. XXX
> >
> > Many databases support multiple sizes of integer types from 1 to 8
> > bytes in size with INTEGER (4 bytes) and SMALLINT (2 bytes) being
> > the most common.N > > one underlying numeric type which is variable width and all other
> > numeric types are aliases for it.>
> >
>
>
>
> - - - - - 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.

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

On Wed, Jul 18, 2007 at 03:23:50PM +0200, Erwan Lemonnier wrote:
>
> sub showbin {
> print "bin: ".unpack("B70",reverse pack("d",$_[0]))."\n";
> }
>
> my $v1 = "1.73696";
> showbin($v1);
>
> print "connecting\n";
> my $DBC = DBI->connect("dbi:Oracle:$ORASID",$ORAUSR,$ORAPWD,
> { PrintError=>0, AutoCommit=>0 } );
>
> my $v2 = "1.73696";
> showbin($v2);
>
> This code simply opens a connection toward an oracle database. And
> shows the binary representation of the string "1.73696" converted into
> a native float (NV) before and after we opened the connection.

> When I run it with perl 5.6.2, DBI 1.38 and DBD::Oracle 1.17, it says:
>
> [HEAD] ~/HEAD/test/t> !967$ /opt/perl-5.6.2/bin/perl 04_test1.t
> bin: 001111111111101111001010100101101001000110100111010111001101 0001
> connecting
> bin: 001111111111101111001010100101101001000110100111010111001101 0001
>
> When I run it with perl 5.8.8, DBI 1.58 and DBD::Oracle 1.19, it says:
>
> [HEAD] ~/HEAD/test/t> !969$ /opt/perl-5.8.8/bin/perl 04_test1.t
> bin: 001111111111101111001010100101101001000110100111010111001101 0001
> connecting
> bin: 001111111111101111001010100101101001000110100111010111001101 0000

Ah, now it's getting interesting! :)

Are the two versions of DBD::Oracle built against the same Oracle
version/installation?

What differences are there in the configuration of the two perl versions?
Take a look at the source for the Perl_sv_2nv() function in sv.c
in the two distributions. Also check for differences in the perl config
items that impact that code.

> See how the least significant bit (last one on the right) changes in
> the last run?
> There we have it. It is what caused the problems I have been spamming
> you all with for the last few days :)

Try it with:

my $v1 = "1.73696";
showbin($v1);
require DBD::Oracle;
my $v2 = "1.73696";
showbin($v2);

> Conclusion: on my host (perl 5.8 etc.), the line:
>
> my $DBC = DBI->connect("dbi:Oracle:$ORASID",$ORAUSR,$ORAPWD,
> { PrintError=>0, AutoCommit=>0 } );
>
> seems to alter the way perl parses the string "1.73696". This later
> resulted in arithmetic errors that looked like floating point related
> issues but were not.
>
> Has anyone any idea of what's happening here????

I know that on Solaris the act of loading the Oracle library (e.g.
libclntsh.so) could suck in other libraries which would cause
subtle side effects.

The particular case I (vagely) recall related to alarm(). Loading the
Oracle library caused the threaded version of alarm() to be loaded and
take precedence. So alarm(0) after loading DBD::Oracle wouldn't cancel
the alarm($timeout) set before loading it. Fun. I don't recall now
if/how that got resolved.

Anyway, I mention it because it seems like this is a similar issue.

Tim.

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

Hi Jonathan,

> Silly question time - I assume that if you don't includes the DBI->connect
> line, then the two invocations of showbin produce the same output in both
> versions of Perl.

It does. And in that case, the only float representation obtained is:

001111111111101111001010100101101001000110100111010111001101 0001

> A wise programmer once said 'moving floating point numbers is like moving
> sand piles; every time you do, you lose a little sand and you pick up a
> little dirt'.

Sure. And others have made this point on this list and it is true. But
in that particular case we are doing the *same operation* before and
after, on the same start data.

I just want to emphasize that the issue discussed in this thread is
not related to floating point precision, at least not in the meaning
of the usual loss of precisions following conversion to floating point
and arithmetic operations on them.

This issue is about how perl alters its behavior when converting a
string to a native float, after some oracle modules have been loaded.
See Tim's latest email for hints of the details behind this
conversion.

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

Hi Tim!

> Ah, now it's getting interesting! :)

So I thought :)

> Are the two versions of DBD::Oracle built against the same Oracle
> version/installation?

No.
The one used by perl 5.6.2 was built against an oracle 9.2.1.0 (unsure
of the exact version, but it was a 9.*). The one used by perl 5.8.5
and perl 5.8.8 was built against an oracle 10.2.0.

> What differences are there in the configuration of the two perl versions?

a diff between perl -V for my perl 5.6.2 and perl 5.8.8 shows the
following differences (the other options are in the 'perl -V' I
included in my very first post):

* 5.6.2:
osvers=2.6.9-42.0.2.elsmp
config_args='-Dprefix=/opt/perl-5.6.2'
useperlio=undef
ccflags ='-fno-strict-aliasing -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64'
cppflags='-fno-strict-aliasing'
libs=-lnsl -lgdbm -ldb -ldl -lm -lc -lcrypt -lutil
ccdlflags='-rdynamic'
Compile-time options: USE_LARGE_FILES

* 5.8.8:
osvers=2.6.9-55.elsmp
config_args=''
useperlio=define
bincompat5005=undef
ccflags ='-fno-strict-aliasing -pipe -Wdeclaration-after-statement
-I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64'
cppflags='-fno-strict-aliasing -pipe -Wdeclaration-after-statement
-I/usr/local/include'
libs=-lnsl -ldb -ldl -lm -lcrypt -lutil -lc
gnulibc_version='2.3.4'
ccdlflags='-Wl,-E'
Compile-time options: PERL_MALLOC_WRAP USE_LARGE_FILES USE_PERLIO


> Take a look at the source for the Perl_sv_2nv() function in sv.c
> in the two distributions.
> Also check for differences in the perl config
> items that impact that code.

Damn. I had a bad feeling I would end up there.
Ok, I'll put on my deep dive googles and switch on the tolkien
translator. If you don't hear from me within a week, call the asylum
to fetch me ;)

> Try it with:
>
> my $v1 = "1.73696";
> showbin($v1);
> require DBD::Oracle;
> my $v2 = "1.73696";
> showbin($v2);

I get:

[HEAD] ~/HEAD/test/t/> !1030$ /opt/perl-5.6.2/bin/perl
02_test_require_dbd_oracle.t
001111111111101111001010100101101001000110100111010111001101 0001
requiring
001111111111101111001010100101101001000110100111010111001101 0001

[HEAD] ~/HEAD/test/t/> !1031$ /opt/perl-5.8.8/bin/perl
02_test_require_dbd_oracle.t
001111111111101111001010100101101001000110100111010111001101 0001
requiring
001111111111101111001010100101101001000110100111010111001101 0001

No difference. What triggered the problem happened during the connect,
but not during 'require DBD::Oracle'.


> I know that on Solaris the act of loading the Oracle library (e.g.
> libclntsh.so) could suck in other libraries which would cause
> subtle side effects.
>
> The particular case I (vagely) recall related to alarm(). Loading the
> Oracle library caused the threaded version of alarm() to be loaded and
> take precedence. So alarm(0) after loading DBD::Oracle wouldn't cancel
> the alarm($timeout) set before loading it. Fun. I don't recall now
> if/how that got resolved.
>
> Anyway, I mention it because it seems like this is a similar issue.

Agggh. This example gives me goose flesh. I just hope I am not facing
something like that...

Is there anyone around with access to an oracle database 10.* and
running perl 5.8.* who could run the test code I provided earlier? The
one that just connects? It would be interesting to know how strongly
dependent it is to just my specific setup...

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

> > Take a look at the source for the Perl_sv_2nv() function in sv.c
> > in the two distributions.
> > Also check for differences in the perl config
> > items that impact that code.

Back from a first dive.
There are significant differences between 5.6.2 and 5.8.8 with respect
to how PVs are translated to NVs.

The function Perl_sv_2nv differs strongly between the 2 versions. It
might be enough to say that it has doubled in size, contain many more
branches (and a few more comments ;).

One thing attracted my attention (but it might be a false lead and I
may be wrong): perl 5.6.2 ultimately converts a string of chars to a
double (64 bits on my hardware) with one of the functions strtold,
atolf or sscanf, depending on what's available.

In 5.8.8, perl calls atof or (and that's the default) an own
implementation of atof called Perl_my_atof2, located in numeric.c.

So in my case, it seems 5.6.2 gets its string-to-float answer from the
standard linux libraries, while 5.8.* gets it from its own algorithm.
Fishy smell, ain't it?

We are kind of living the realm of DBI here, so I will continue this
thread on the perl-porters mailing list and see what we can get there.

/Erwan

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

Erwan Lemonnier wrote:
> Hi Tim!
>
>> Ah, now it's getting interesting! :)
>
> So I thought :)
>
>> Are the two versions of DBD::Oracle built against the same Oracle
>> version/installation?
>
> No.
> The one used by perl 5.6.2 was built against an oracle 9.2.1.0 (unsure
> of the exact version, but it was a 9.*). The one used by perl 5.8.5
> and perl 5.8.8 was built against an oracle 10.2.0.
>
>> What differences are there in the configuration of the two perl versions?
>
> a diff between perl -V for my perl 5.6.2 and perl 5.8.8 shows the
> following differences (the other options are in the 'perl -V' I
> included in my very first post):
>
> * 5.6.2:
> osvers=2.6.9-42.0.2.elsmp
> config_args='-Dprefix=/opt/perl-5.6.2'
> useperlio=undef
> ccflags ='-fno-strict-aliasing -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64'
> cppflags='-fno-strict-aliasing'
> libs=-lnsl -lgdbm -ldb -ldl -lm -lc -lcrypt -lutil
> ccdlflags='-rdynamic'
> Compile-time options: USE_LARGE_FILES
>
> * 5.8.8:
> osvers=2.6.9-55.elsmp
> config_args=''
> useperlio=define
> bincompat5005=undef
> ccflags ='-fno-strict-aliasing -pipe -Wdeclaration-after-statement
> -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64'
> cppflags='-fno-strict-aliasing -pipe -Wdeclaration-after-statement
> -I/usr/local/include'
> libs=-lnsl -ldb -ldl -lm -lcrypt -lutil -lc
> gnulibc_version='2.3.4'
> ccdlflags='-Wl,-E'
> Compile-time options: PERL_MALLOC_WRAP USE_LARGE_FILES USE_PERLIO
>
>
>> Take a look at the source for the Perl_sv_2nv() function in sv.c
>> in the two distributions.
>> Also check for differences in the perl config
>> items that impact that code.
>
> Damn. I had a bad feeling I would end up there.
> Ok, I'll put on my deep dive googles and switch on the tolkien
> translator. If you don't hear from me within a week, call the asylum
> to fetch me ;)
>
>> Try it with:
>>
>> my $v1 = "1.73696";
>> showbin($v1);
>> require DBD::Oracle;
>> my $v2 = "1.73696";
>> showbin($v2);
>
> I get:
>
> [HEAD] ~/HEAD/test/t/> !1030$ /opt/perl-5.6.2/bin/perl
> 02_test_require_dbd_oracle.t
> 001111111111101111001010100101101001000110100111010111001101 0001
> requiring
> 001111111111101111001010100101101001000110100111010111001101 0001
>
> [HEAD] ~/HEAD/test/t/> !1031$ /opt/perl-5.8.8/bin/perl
> 02_test_require_dbd_oracle.t
> 001111111111101111001010100101101001000110100111010111001101 0001
> requiring
> 001111111111101111001010100101101001000110100111010111001101 0001
>
> No difference. What triggered the problem happened during the connect,
> but not during 'require DBD::Oracle'.
>
>
>> I know that on Solaris the act of loading the Oracle library (e.g.
>> libclntsh.so) could suck in other libraries which would cause
>> subtle side effects.
>>
>> The particular case I (vagely) recall related to alarm(). Loading the
>> Oracle library caused the threaded version of alarm() to be loaded and
>> take precedence. So alarm(0) after loading DBD::Oracle wouldn't cancel
>> the alarm($timeout) set before loading it. Fun. I don't recall now
>> if/how that got resolved.
>>
>> Anyway, I mention it because it seems like this is a similar issue.
>
> Agggh. This example gives me goose flesh. I just hope I am not facing
> something like that...
>
> Is there anyone around with access to an oracle database 10.* and
> running perl 5.8.* who could run the test code I provided earlier? The
> one that just connects? It would be interesting to know how strongly
> dependent it is to just my specific setup...
>
>
perl --version

This is perl, v5.8.8 built for i486-linux-gnu-thread-multi

select * from v$version
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

cat x.pl

use DBI;
sub showbin {
print "bin: ".unpack("B70",reverse pack("d",$_[0]))."\n";
}
my $v1 = "1.73696";
showbin($v1);
print "connecting\n";
my $DBC = DBI->connect("dbi:Oracle:XE",'XXX','YYY',
{ PrintError=>0, AutoCommit=>0 } );
my $v2 = "1.73696";
showbin($v2);

perl x.pl

bin: 001111111111101111001010100101101001000110100111010111001101 0001
connecting
bin: 001111111111101111001010100101101001000110100111010111001101 0000

Hope this helps.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

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

> perl --version
>
> This is perl, v5.8.8 built for i486-linux-gnu-thread-multi
>
> select * from v$version
> Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
> PL/SQL Release 10.2.0.1.0 - Production
> CORE 10.2.0.1.0 Production
> TNS for Linux: Version 10.2.0.1.0 - Production
> NLSRTL Version 10.2.0.1.0 - Production
>

> perl x.pl
>
> bin: 001111111111101111001010100101101001000110100111010111001101 0001
> connecting
> bin: 001111111111101111001010100101101001000110100111010111001101 0000

Thanks Martin!
It does really help!
It means you have the same problem as me, and that it is worth
tracking it down :)

The problem occurs only in seldom cases, with specific numbers, and
the error introduced can easily get mixed up with usual floating point
precision issues, if not disappear among them, so it's quite likely to
pass un-noticed and have no serious consequences...

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

Well maybe it is time if piped up.

I ran the code in x.pl as well and just to add some more fuel to the fier I
ran it in activestate perl on a windows XP box.

I will try it on some different boxes as well

Here are my results

C:\johns\testperl>perl x.pl
bin: 001111111111101111001010100101101001000110100111010111001101 0000
connecting
bin: 001111111111101111001010100101101001000110100111010111001101 0000

Now here is what I ran it on

select * from v$version

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

and the perl -V


Summary of my perl5 (revision 5 version 8 subversion 8) configuration:
Platform:
osname=MSWin32, osvers=4.0, archname=MSWin32-x86-multi-thread
uname=''
config_args='undef'
hint=recommended, useposix=true, d_sigaction=undef
usethreads=define use5005threads=undef useithreads=define
usemultiplicity=define
useperlio=define d_sfio=undef uselargefiles=define usesocks=undef
use64bitint=undef use64bitall=undef uselongdouble=undef
usemymalloc=n, bincompat5005=undef
Compiler:
cc='cl', ccflags
='-nologo -GF -W3 -MD -Zi -DNDEBUG -O1 -DWIN32 -D_CONSOLE -DNO_STRICT -DHAVE_DE
S_FCRYPT -DNO_HASH_SEED -DUSE_SITECUSTOMIZE -DPERL_IMPLICIT_CONTEXT -DPERL_IMPLICIT_SYS
-DUSE_PERLIO
-DPERL_MSVCRT_READFIX',
optimize='-MD -Zi -DNDEBUG -O1',
cppflags='-DWIN32'
ccversion='12.00.8804', gccversion='', gccosandvers=''
intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=1234
d_longlong=undef, longlongsize=8, d_longdbl=define, longdblsize=10
ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='__int64',
lseeksize=8
alignbytes=8, prototype=define
Linker and Libraries:
ld='link', ldflags
'-nologo -nodefaultlib -debug -opt:ref,icf -libpath:"C:\Perl\lib\CORE" -ma
chine:x86'
libpth=\lib
libs= oldnames.lib kernel32.lib user32.lib gdi32.lib winspool.lib
comdlg32.lib advapi32.lib sh
ell32.lib ole32.lib oleaut32.lib netapi32.lib uuid.lib ws2_32.lib mpr.lib
winmm.lib version.lib od
bc32.lib odbccp32.lib msvcrt.lib
perllibs= oldnames.lib kernel32.lib user32.lib gdi32.lib winspool.lib
comdlg32.lib advapi32.li
b shell32.lib ole32.lib oleaut32.lib netapi32.lib uuid.lib ws2_32.lib
mpr.lib winmm.lib version.li
b odbc32.lib odbccp32.lib msvcrt.lib
libc=msvcrt.lib, so=dll, useshrplib=yes, libperl=perl58.lib
gnulibc_version=''
Dynamic Linking:
dlsrc=dl_win32.xs, dlext=dll, d_dlsymun=undef, ccdlflags=' '
cccdlflags=' ',
lddlflags='-dll -nologo -nodefaultlib -debug -opt:ref,icf -libpath:"C:\Perl\lib
\CORE" -machine:x86'


Characteristics of this binary (from libperl):
Compile-time options: MULTIPLICITY PERL_IMPLICIT_CONTEXT
PERL_IMPLICIT_SYS PERL_MALLOC_WRAP
PL_OP_SLAB_ALLOC USE_ITHREADS USE_LARGE_FILES
USE_PERLIO USE_SITECUSTOMIZE
Locally applied patches:
ActivePerl Build 820 [274739]
Iin_load_module moved for compatibility with build 806
PerlEx support in CGI::Carp
Less verbose ExtUtils::Install and Pod::Find
Patch for CAN-2005-0448 from Debian with modifications
Rearrange @INC so that 'site' is searched before 'perl'
Partly reverted 24733 to preserve binary compatibility
29930 win32.c typo in #define MULTIPLICITY
29868 win32_async_check() can still loop indefinitely
29690,29732 ANSIfy the PATH environment variable on Windows
29689 Add error handling to win32_ansipath
29675 Use short pathnames in $^X and @INC
29607,29676 allow blib.pm to be used for testing Win32 module
29605 Implement killpg() for MSWin32
29598 cwd() to return the short pathname
29597 let readdir() return the alternate filename
29590 Don't destroy the Unicode system environment on Perl startup
29528 get ext/Win32/Win32.xs to compile on cygwin
29509,29510,29511 Move Win32::* functions into Win32 module
29483 Move Win32 from win32/ext/Win32 to ext/Win32
29481 Makefile.PL changes to compile Win32.xs using cygwin
28671 Define PERL_NO_DEV_RANDOM on Windows
28376 Add error checks after execing PL_cshname or PL_sh_path
28305 Pod::Html should not convert "foo" into ``foo''
27833 Change anchor generation in Pod::Html for '=item item 2'
27832,27847 fix Pod::Html::depod() for multi-line strings
27719 Document the functions htmlify() and anchorify() in Pod::Html
27619 Bug in Term::ReadKey being triggered by a bug in
Term::ReadLine
27549 Move DynaLoader.o into libperl.so
27528 win32_pclose() error exit doesn't unlock mutex
27527 win32_async_check() can loop indefinitely
27515 ignore directories when searching @INC
27359 Fix -d:Foo=bar syntax
27210 Fix quote typo in c2ph
27203 Allow compiling swigged C++ code
27200 Make stat() on Windows handle trailing slashes correctly
27133 Initialise lastparen in the regexp structure
27061 L and Pod::Html
27034 Avoid "Prototype mismatch" warnings with autouse
26970 Make Passive mode the default for Net::FTP
26921 Avoid getprotobyname/number calls in IO::Socket::INET
26897,26903 Make common IPPROTO_* constants always available
26670 Make '-s' on the shebang line parse -foo=bar switches
26637 Make Borland and MinGW happy with change 26379
26536 INSTALLSCRIPT versus INSTALLDIRS
26379 Fix alarm() for Windows 2003
26087 Storable 0.1 compatibility
25861 IO::File performace issue
25084 long groups entry could cause memory exhaustion
24699 ICMP_UNREACHABLE handling in Net::Ping
Built under MSWin32
Compiled at Jan 23 2007 15:57:46
%ENV:
PERL5LIB="C:\Program Files\ActiveState Perl Dev Kit 6.0\lib\"
PERLDB_OPTS="RemotePort=127.0.0.1:2000"
@INC:
C:\Program Files\ActiveState Perl Dev Kit 6.0\lib\
C:/Perl/site/lib
C:/Perl/lib
.



----- Original Message -----
From: "Erwan Lemonnier"
To: "Martin Evans"
Cc:
Sent: Thursday, July 19, 2007 5:51 AM
Subject: Re: float bug? perl 5.8, DBI and oracle 10.2.0


>> perl --version
>>
>> This is perl, v5.8.8 built for i486-linux-gnu-thread-multi
>>
>> select * from v$version
>> Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
>> PL/SQL Release 10.2.0.1.0 - Production
>> CORE 10.2.0.1.0 Production
>> TNS for Linux: Version 10.2.0.1.0 - Production
>> NLSRTL Version 10.2.0.1.0 - Production
>>
>
>> perl x.pl
>>
>> bin: 001111111111101111001010100101101001000110100111010111001101 0001
>> connecting
>> bin: 001111111111101111001010100101101001000110100111010111001101 0000
>
> Thanks Martin!
> It does really help!
> It means you have the same problem as me, and that it is worth
> tracking it down :)
>
> The problem occurs only in seldom cases, with specific numbers, and
> the error introduced can easily get mixed up with usual floating point
> precision issues, if not disappear among them, so it's quite likely to
> pass un-noticed and have no serious consequences...
>

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

Here is the test again. Same DB and same version of DBI, DBD::Oracle and
the Oracle client but this time on a Lunix box

[wwwtest@nelson wwwtest]$ perl x.pl
bin: 001111111111101111001010100101101001000110100111010111001101 0001
connecting
bin: 001111111111101111001010100101101001000110100111010111001101 0001
[wwwtest@nelson wwwtest]$
[wwwtest@nelson wwwtest]$ perl -V
Summary of my perl5 (revision 5.0 version 8 subversion 0) configuration:
Platform:
osname=linux, osvers=2.6.9-34.0.2.el.jmm1,
archname=i386-linux-thread-multi
uname='linux roark.whiteboxlinux.org 2.6.9-34.0.2.el.jmm1 #1 fri nov 10
16:42:55 cst 2006 i686 athlon i386 gnulinux '
config_args='-des -Doptimize=-O2 -g -pipe -march=i386 -mcpu=i686 -Dmyhostname=localhost
-Dperladmin=root@localhost -Dcc=gcc -Dcf_by=Red Hat,
Inc. -Dinstallprefix=/usr -Dprefix=/usr -Darchname=i386-linux -Dvendorprefix=/usr
-Dsiteprefix=/usr -Dotherlibdirs=/usr/lib/perl5/5.8.0 -Duseshrplib -Dusethreads
-Duseithreads -Duselargefiles -Dd_dosuid -Dd_semctl_semun -Di_db -Ui_ndbm -Di_gdbm
-Di_shadow -Di_syslog -Dman3ext=3pm -Duseperlio -Dinstallusrbinperl -Ubincompat5005
-Uversiononly -Dpager=/usr/bin/less -isr'
hint=recommended, useposix=true, d_sigaction=define
usethreads=define use5005threads=undef useithreads=define
usemultiplicity=define
useperlio=define d_sfio=undef uselargefiles=define usesocks=undef
use64bitint=undef use64bitall=undef uselongdouble=undef
usemymalloc=n, bincompat5005=undef
Compiler:
cc='gcc', ccflags
='-D_REENTRANT -D_GNU_SOURCE -DTHREADS_HAVE_PIDS -DDEBUGGING -fno-strict-aliasing
-I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm',
optimize='-O2 -g -pipe -march=i386 -mcpu=i686',
cppflags='-D_REENTRANT -D_GNU_SOURCE -DTHREADS_HAVE_PIDS -DDEBUGGING -fno-strict-aliasing
-I/usr/local/include -I/usr/include/gdbm'
ccversion='', gccversion='3.2.3 20030502 (Red Hat Linux 3.2.3-54)',
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='gcc', ldflags =' -L/usr/local/lib'
libpth=/usr/local/lib /lib /usr/lib
libs=-lnsl -lgdbm -ldb -ldl -lm -lpthread -lc -lcrypt -lutil
perllibs=-lnsl -ldl -lm -lpthread -lc -lcrypt -lutil
libc=/lib/libc-2.3.2.so, so=so, useshrplib=true, libperl=libperl.so
gnulibc_version='2.3.2'
Dynamic Linking:
dlsrc=dl_dlopen.xs, dlext=so, d_dlsymun=undef,
ccdlflags='-rdynamic -Wl,-rpath,/usr/lib/perl5/5.8.0/i386-linux-thread-multi/CORE '
cccdlflags='-fPIC', lddlflags='-shared -L/usr/local/lib'


Characteristics of this binary (from libperl):
Compile-time options: DEBUGGING MULTIPLICITY USE_ITHREADS USE_LARGE_FILES
PERL_IMPLICIT_CONTEXT
Locally applied patches:
MAINT18379
Built under linux
Compiled at Dec 21 2006 18:39:03
@INC:
/usr/lib/perl5/5.8.0/i386-linux-thread-multi
/usr/lib/perl5/5.8.0
/usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi
/usr/lib/perl5/site_perl/5.8.0
/usr/lib/perl5/site_perl
/usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi
/usr/lib/perl5/vendor_perl/5.8.0
/usr/lib/perl5/vendor_perl
/usr/lib/perl5/5.8.0/i386-linux-thread-multi
/usr/lib/perl5/5.8.0
.
[wwwtest@nelson wwwtest]$



----- Original Message -----
From: "John Scoles"
To: ; "Martin Evans"
Cc:
Sent: Thursday, July 19, 2007 7:56 AM
Subject: Re: float bug? perl 5.8, DBI and oracle 10.2.0


> Well maybe it is time if piped up.
>
> I ran the code in x.pl as well and just to add some more fuel to the fier
> I
> ran it in activestate perl on a windows XP box.
>
> I will try it on some different boxes as well
>
> Here are my results
>
> C:\johns\testperl>perl x.pl
> bin: 001111111111101111001010100101101001000110100111010111001101 0000
> connecting
> bin: 001111111111101111001010100101101001000110100111010111001101 0000
>
> Now here is what I ran it on
>
> select * from v$version
>
> Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
> PL/SQL Release 10.2.0.3.0 - Production
> CORE 10.2.0.3.0 Production
> TNS for Linux: Version 10.2.0.3.0 - Production
> NLSRTL Version 10.2.0.3.0 - Production
>
> and the perl -V
>
>
> Summary of my perl5 (revision 5 version 8 subversion 8) configuration:
> Platform:
> osname=MSWin32, osvers=4.0, archname=MSWin32-x86-multi-thread
> uname=''
> config_args='undef'
> hint=recommended, useposix=true, d_sigaction=undef
> usethreads=define use5005threads=undef useithreads=define
> usemultiplicity=define
> useperlio=define d_sfio=undef uselargefiles=define usesocks=undef
> use64bitint=undef use64bitall=undef uselongdouble=undef
> usemymalloc=n, bincompat5005=undef
> Compiler:
> cc='cl', ccflags
> ='-nologo -GF -W3 -MD -Zi -DNDEBUG -O1 -DWIN32 -D_CONSOLE -DNO_STRICT -DHAVE_DE
> S_FCRYPT -DNO_HASH_SEED -DUSE_SITECUSTOMIZE -DPERL_IMPLICIT_CONTEXT -DPERL_IMPLICIT_SYS
> -DUSE_PERLIO
> -DPERL_MSVCRT_READFIX',
> optimize='-MD -Zi -DNDEBUG -O1',
> cppflags='-DWIN32'
> ccversion='12.00.8804', gccversion='', gccosandvers=''
> intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=1234
> d_longlong=undef, longlongsize=8, d_longdbl=define, longdblsize=10
> ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='__int64',
> lseeksize=8
> alignbytes=8, prototype=define
> Linker and Libraries:
> ld='link', ldflags
>
> -nologo -nodefaultlib -debug -opt:ref,icf -libpath:"C:\Perl\lib\CORE" -ma
> chine:x86'
> libpth=\lib
> libs= oldnames.lib kernel32.lib user32.lib gdi32.lib winspool.lib
> comdlg32.lib advapi32.lib sh
> ell32.lib ole32.lib oleaut32.lib netapi32.lib uuid.lib ws2_32.lib mpr.lib
> winmm.lib version.lib od
> bc32.lib odbccp32.lib msvcrt.lib
> perllibs= oldnames.lib kernel32.lib user32.lib gdi32.lib winspool.lib
> comdlg32.lib advapi32.li
> b shell32.lib ole32.lib oleaut32.lib netapi32.lib uuid.lib ws2_32.lib
> mpr.lib winmm.lib version.li
> b odbc32.lib odbccp32.lib msvcrt.lib
> libc=msvcrt.lib, so=dll, useshrplib=yes, libperl=perl58.lib
> gnulibc_version=''
> Dynamic Linking:
> dlsrc=dl_win32.xs, dlext=dll, d_dlsymun=undef, ccdlflags=' '
> cccdlflags=' ',
>
> ddlflags='-dll -nologo -nodefaultlib -debug -opt:ref,icf -libpath:"C:\Perl\lib
> \CORE" -machine:x86'
>
>
> Characteristics of this binary (from libperl):
> Compile-time options: MULTIPLICITY PERL_IMPLICIT_CONTEXT
> PERL_IMPLICIT_SYS PERL_MALLOC_WRAP
> PL_OP_SLAB_ALLOC USE_ITHREADS USE_LARGE_FILES
> USE_PERLIO USE_SITECUSTOMIZE
> Locally applied patches:
> ActivePerl Build 820 [274739]
> Iin_load_module moved for compatibility with build 806
> PerlEx support in CGI::Carp
> Less verbose ExtUtils::Install and Pod::Find
> Patch for CAN-2005-0448 from Debian with modifications
> Rearrange @INC so that 'site' is searched before 'perl'
> Partly reverted 24733 to preserve binary compatibility
> 29930 win32.c typo in #define MULTIPLICITY
> 29868 win32_async_check() can still loop indefinitely
> 29690,29732 ANSIfy the PATH environment variable on Windows
> 29689 Add error handling to win32_ansipath
> 29675 Use short pathnames in $^X and @INC
> 29607,29676 allow blib.pm to be used for testing Win32 module
> 29605 Implement killpg() for MSWin32
> 29598 cwd() to return the short pathname
> 29597 let readdir() return the alternate filename
> 29590 Don't destroy the Unicode system environment on Perl startup
> 29528 get ext/Win32/Win32.xs to compile on cygwin
> 29509,29510,29511 Move Win32::* functions into Win32 module
> 29483 Move Win32 from win32/ext/Win32 to ext/Win32
> 29481 Makefile.PL changes to compile Win32.xs using cygwin
> 28671 Define PERL_NO_DEV_RANDOM on Windows
> 28376 Add error checks after execing PL_cshname or PL_sh_path
> 28305 Pod::Html should not convert "foo" into ``foo''
> 27833 Change anchor generation in Pod::Html for '=item item 2'
> 27832,27847 fix Pod::Html::depod() for multi-line strings
> 27719 Document the functions htmlify() and anchorify() in Pod::Html
> 27619 Bug in Term::ReadKey being triggered by a bug in
> Term::ReadLine
> 27549 Move DynaLoader.o into libperl.so
> 27528 win32_pclose() error exit doesn't unlock mutex
> 27527 win32_async_check() can loop indefinitely
> 27515 ignore directories when searching @INC
> 27359 Fix -d:Foo=bar syntax
> 27210 Fix quote typo in c2ph
> 27203 Allow compiling swigged C++ code
> 27200 Make stat() on Windows handle trailing slashes correctly
> 27133 Initialise lastparen in the regexp structure
> 27061 L and Pod::Html
> 27034 Avoid "Prototype mismatch" warnings with autouse
> 26970 Make Passive mode the default for Net::FTP
> 26921 Avoid getprotobyname/number calls in IO::Socket::INET
> 26897,26903 Make common IPPROTO_* constants always available
> 26670 Make '-s' on the shebang line parse -foo=bar switches
> 26637 Make Borland and MinGW happy with change 26379
> 26536 INSTALLSCRIPT versus INSTALLDIRS
> 26379 Fix alarm() for Windows 2003
> 26087 Storable 0.1 compatibility
> 25861 IO::File performace issue
> 25084 long groups entry could cause memory exhaustion
> 24699 ICMP_UNREACHABLE handling in Net::Ping
> Built under MSWin32
> Compiled at Jan 23 2007 15:57:46
> %ENV:
> PERL5LIB="C:\Program Files\ActiveState Perl Dev Kit 6.0\lib\"
> PERLDB_OPTS="RemotePort=127.0.0.1:2000"
> @INC:
> C:\Program Files\ActiveState Perl Dev Kit 6.0\lib\
> C:/Perl/site/lib
> C:/Perl/lib
> .
>
>
>
> ----- Original Message -----
> From: "Erwan Lemonnier"
> To: "Martin Evans"
> Cc:
> Sent: Thursday, July 19, 2007 5:51 AM
> Subject: Re: float bug? perl 5.8, DBI and oracle 10.2.0
>
>
>>> perl --version
>>>
>>> This is perl, v5.8.8 built for i486-linux-gnu-thread-multi
>>>
>>> select * from v$version
>>> Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
>>> PL/SQL Release 10.2.0.1.0 - Production
>>> CORE 10.2.0.1.0 Production
>>> TNS for Linux: Version 10.2.0.1.0 - Production
>>> NLSRTL Version 10.2.0.1.0 - Production
>>>
>>
>>> perl x.pl
>>>
>>> bin: 001111111111101111001010100101101001000110100111010111001101 0001
>>> connecting
>>> bin: 001111111111101111001010100101101001000110100111010111001101 0000
>>
>> Thanks Martin!
>> It does really help!
>> It means you have the same problem as me, and that it is worth
>> tracking it down :)
>>
>> The problem occurs only in seldom cases, with specific numbers, and
>> the error introduced can easily get mixed up with usual floating point
>> precision issues, if not disappear among them, so it's quite likely to
>> pass un-noticed and have no serious consequences...
>>
>
>

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

On Thu, 2007-07-19 at 09:42 +0200, Erwan Lemonnier wrote:
> Hi Tim!
>
> > Ah, now it's getting interesting! :)
>
> So I thought :)
>
> > Are the two versions of DBD::Oracle built against the same Oracle
> > version/installation?
>
> No.
> The one used by perl 5.6.2 was built against an oracle 9.2.1.0 (unsure
> of the exact version, but it was a 9.*). The one used by perl 5.8.5
> and perl 5.8.8 was built against an oracle 10.2.0.
>
> > What differences are there in the configuration of the two perl versions?
>
> a diff between perl -V for my perl 5.6.2 and perl 5.8.8 shows the
> following differences (the other options are in the 'perl -V' I
> included in my very first post):
>
> * 5.6.2:
> osvers=2.6.9-42.0.2.elsmp
> config_args='-Dprefix=/opt/perl-5.6.2'
> useperlio=undef
> ccflags ='-fno-strict-aliasing -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64'
> cppflags='-fno-strict-aliasing'
> libs=-lnsl -lgdbm -ldb -ldl -lm -lc -lcrypt -lutil
> ccdlflags='-rdynamic'
> Compile-time options: USE_LARGE_FILES
>
> * 5.8.8:
> osvers=2.6.9-55.elsmp
> config_args=''
> useperlio=define
> bincompat5005=undef
> ccflags ='-fno-strict-aliasing -pipe -Wdeclaration-after-statement
> -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64'
> cppflags='-fno-strict-aliasing -pipe -Wdeclaration-after-statement
> -I/usr/local/include'
> libs=-lnsl -ldb -ldl -lm -lcrypt -lutil -lc
> gnulibc_version='2.3.4'
> ccdlflags='-Wl,-E'
> Compile-time options: PERL_MALLOC_WRAP USE_LARGE_FILES USE_PERLIO
>
>
> > Take a look at the source for the Perl_sv_2nv() function in sv.c
> > in the two distributions.
> > Also check for differences in the perl config
> > items that impact that code.
>
> Damn. I had a bad feeling I would end up there.
> Ok, I'll put on my deep dive googles and switch on the tolkien
> translator. If you don't hear from me within a week, call the asylum
> to fetch me ;)
>
> > Try it with:
> >
> > my $v1 = "1.73696";
> > showbin($v1);
> > require DBD::Oracle;
> > my $v2 = "1.73696";
> > showbin($v2);
>
> I get:
>
> [HEAD] ~/HEAD/test/t/> !1030$ /opt/perl-5.6.2/bin/perl
> 02_test_require_dbd_oracle.t
> 001111111111101111001010100101101001000110100111010111001101 0001
> requiring
> 001111111111101111001010100101101001000110100111010111001101 0001
>
> [HEAD] ~/HEAD/test/t/> !1031$ /opt/perl-5.8.8/bin/perl
> 02_test_require_dbd_oracle.t
> 001111111111101111001010100101101001000110100111010111001101 0001
> requiring
> 001111111111101111001010100101101001000110100111010111001101 0001
>
> No difference. What triggered the problem happened during the connect,
> but not during 'require DBD::Oracle'.
>
>
> > I know that on Solaris the act of loading the Oracle library (e.g.
> > libclntsh.so) could suck in other libraries which would cause
> > subtle side effects.
> >
> > The particular case I (vagely) recall related to alarm(). Loading the
> > Oracle library caused the threaded version of alarm() to be loaded and
> > take precedence. So alarm(0) after loading DBD::Oracle wouldn't cancel
> > the alarm($timeout) set before loading it. Fun. I don't recall now
> > if/how that got resolved.
> >
> > Anyway, I mention it because it seems like this is a similar issue.
>
> Agggh. This example gives me goose flesh. I just hope I am not facing
> something like that...
>
> Is there anyone around with access to an oracle database 10.* and
> running perl 5.8.* who could run the test code I provided earlier? The
> one that just connects? It would be interesting to know how strongly
> dependent it is to just my specific setup...

I know you asked for Oracle 10, but here is the result of

Perl : 5.008006 (i686-linux)
OS : linux (8.0)
DBI : 1.54
DBD::Sponge : 12.008696
DBD::Proxy : 0.2004
DBD::Oracle : 1.19

Oracle 9.2.0.1.0

bin: 001111111111101111001010100101101001000110100111010111001101 0001
connecting
bin: 001111111111101111001010100101101001000110100111010111001101 0001

--
Scott T. Hildreth

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

On Thu, 2007-07-19 at 09:36 -0500, Scott T. Hildreth wrote:
> On Thu, 2007-07-19 at 09:42 +0200, Erwan Lemonnier wrote:
> > Hi Tim!
> >
> > > Ah, now it's getting interesting! :)
> >
> > So I thought :)
> >
> > > Are the two versions of DBD::Oracle built against the same Oracle
> > > version/installation?
> >
> > No.
> > The one used by perl 5.6.2 was built against an oracle 9.2.1.0 (unsure
> > of the exact version, but it was a 9.*). The one used by perl 5.8.5
> > and perl 5.8.8 was built against an oracle 10.2.0.
> >
> > > What differences are there in the configuration of the two perl versions?
> >
> > a diff between perl -V for my perl 5.6.2 and perl 5.8.8 shows the
> > following differences (the other options are in the 'perl -V' I
> > included in my very first post):
> >
> > * 5.6.2:
> > osvers=2.6.9-42.0.2.elsmp
> > config_args='-Dprefix=/opt/perl-5.6.2'
> > useperlio=undef
> > ccflags ='-fno-strict-aliasing -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64'
> > cppflags='-fno-strict-aliasing'
> > libs=-lnsl -lgdbm -ldb -ldl -lm -lc -lcrypt -lutil
> > ccdlflags='-rdynamic'
> > Compile-time options: USE_LARGE_FILES
> >
> > * 5.8.8:
> > osvers=2.6.9-55.elsmp
> > config_args=''
> > useperlio=define
> > bincompat5005=undef
> > ccflags ='-fno-strict-aliasing -pipe -Wdeclaration-after-statement
> > -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64'
> > cppflags='-fno-strict-aliasing -pipe -Wdeclaration-after-statement
> > -I/usr/local/include'
> > libs=-lnsl -ldb -ldl -lm -lcrypt -lutil -lc
> > gnulibc_version='2.3.4'
> > ccdlflags='-Wl,-E'
> > Compile-time options: PERL_MALLOC_WRAP USE_LARGE_FILES USE_PERLIO
> >
> >
> > > Take a look at the source for the Perl_sv_2nv() function in sv.c
> > > in the two distributions.
> > > Also check for differences in the perl config
> > > items that impact that code.
> >
> > Damn. I had a bad feeling I would end up there.
> > Ok, I'll put on my deep dive googles and switch on the tolkien
> > translator. If you don't hear from me within a week, call the asylum
> > to fetch me ;)
> >
> > > Try it with:
> > >
> > > my $v1 = "1.73696";
> > > showbin($v1);
> > > require DBD::Oracle;
> > > my $v2 = "1.73696";
> > > showbin($v2);
> >
> > I get:
> >
> > [HEAD] ~/HEAD/test/t/> !1030$ /opt/perl-5.6.2/bin/perl
> > 02_test_require_dbd_oracle.t
> > 001111111111101111001010100101101001000110100111010111001101 0001
> > requiring
> > 001111111111101111001010100101101001000110100111010111001101 0001
> >
> > [HEAD] ~/HEAD/test/t/> !1031$ /opt/perl-5.8.8/bin/perl
> > 02_test_require_dbd_oracle.t
> > 001111111111101111001010100101101001000110100111010111001101 0001
> > requiring
> > 001111111111101111001010100101101001000110100111010111001101 0001
> >
> > No difference. What triggered the problem happened during the connect,
> > but not during 'require DBD::Oracle'.
> >
> >
> > > I know that on Solaris the act of loading the Oracle library (e.g.
> > > libclntsh.so) could suck in other libraries which would cause
> > > subtle side effects.
> > >
> > > The particular case I (vagely) recall related to alarm(). Loading the
> > > Oracle library caused the threaded version of alarm() to be loaded and
> > > take precedence. So alarm(0) after loading DBD::Oracle wouldn't cancel
> > > the alarm($timeout) set before loading it. Fun. I don't recall now
> > > if/how that got resolved.
> > >
> > > Anyway, I mention it because it seems like this is a similar issue.
> >
> > Agggh. This example gives me goose flesh. I just hope I am not facing
> > something like that...
> >
> > Is there anyone around with access to an oracle database 10.* and
> > running perl 5.8.* who could run the test code I provided earlier? The
> > one that just connects? It would be interesting to know how strongly
> > dependent it is to just my specific setup...
>
> I know you asked for Oracle 10, but here is the result of
>
> Perl : 5.008006 (i686-linux)
> OS : linux (8.0)
> DBI : 1.54
> DBD::Sponge : 12.008696
> DBD::Proxy : 0.2004
> DBD::Oracle : 1.19
>
> Oracle 9.2.0.1.0
>
> bin: 001111111111101111001010100101101001000110100111010111001101 0001
> connecting
> bin: 001111111111101111001010100101101001000110100111010111001101 0001

... and

bin: 001111111111101111001010100101101001000110100111010111001101 0001
connecting
bin: 001111111111101111001010100101101001000110100111010111001101 0000

with

Perl : 5.008008 (i686-linux)
OS : linux (2.6.14.6)
DBI : 1.54
DBD::mysql : 3.0002
DBD::Sponge : 12.008696
DBD::SQLite : 1.11
DBD::Proxy : 0.2004
DBD::Oracle : 1.19

connecting to 10.2.0.3.0 or 9.2.0.1.0
>

It seems to be the DBD::Oracle built against Oracle 10.x.

--
Scott T. Hildreth

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

Hi all!

I have started a thread on perl5-porters discussing the same issue.
The posts are not yet archived on
http://www.nntp.perl.org/group/perl.perl5.porters/ but should appear
there quite soon.

What this new thread boils down to (so far) is that perl 5.6 uses the
native atof to convert from string to float, and this atof in its turn
save, alter then restore the FPU flags that tells the microprocessor's
FPU which rounding mode it should use when computing a significand.
Perl 5.8 on the other hand uses its own implementation of atof, a
function called Perl_my_atof2 located in numeric.c. This
implementation does not alter the FPU flags. Apparently, one of the
oracle libraries (or at least something executed when doing
DBI->connect) changes the FPU flags and does not restore them
afterward. Hence the change in behavior we observed.

Now, part of what I just explained are still just hypothesis, but I am
getting pretty sure that it's what's happening.

A quick work around consists in recompiling perl 5.8 with the cc/gcc
flag "-DUSE_PERL_ATOF=0". The new perl binary obtained in that way
uses the system's atof and the string-to-float conversion is not
altered anymore by whatever happens during DBI->connect:

[HEAD] ~/HEAD/test/t> !1086$ /opt/perl-5.8.8_native_atof/bin/perl 01_test1.t
0 01111111111 1011110010101001011010010001101001110101110011010001
connecting
0 01111111111 1011110010101001011010010001101001110101110011010001

Voila!

That being said, we have a few possibilities:
- restore the FPU flags to the expected default after doing
DBI->connect. But it's probably a bad idea since there probably is a
good reason why those flags needed to be altered in the first place.
- patch perl's Perl_my_atof2 to act as the native atof with respect to
FPU flags. That may be a bad idea as well, since it means altering the
behavior of perl 5.8.

/Erwan

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

Hi,

Scott T. Hildreth schrieb:
--cut
>> I know you asked for Oracle 10, but here is the result of
>>
>> Perl : 5.008006 (i686-linux)
>> OS : linux (8.0)
>> DBI : 1.54
>> DBD::Sponge : 12.008696
>> DBD::Proxy : 0.2004
>> DBD::Oracle : 1.19
>>
>> Oracle 9.2.0.1.0
>>
>> bin: 001111111111101111001010100101101001000110100111010111001101 0001
>> connecting
>> bin: 001111111111101111001010100101101001000110100111010111001101 0001
>
> ... and
>
> bin: 001111111111101111001010100101101001000110100111010111001101 0001
> connecting
> bin: 001111111111101111001010100101101001000110100111010111001101 0000
>
> with
>
> Perl : 5.008008 (i686-linux)
> OS : linux (2.6.14.6)
> DBI : 1.54
> DBD::mysql : 3.0002
> DBD::Sponge : 12.008696
> DBD::SQLite : 1.11
> DBD::Proxy : 0.2004
> DBD::Oracle : 1.19
>
> connecting to 10.2.0.3.0 or 9.2.0.1.0
>
> It seems to be the DBD::Oracle built against Oracle 10.x.
>

not necessarily.

This are the results on Solaris 10 (11/06)
perl, v5.8.4 built for i86pc-solaris-64int
DBI 1.56
DBD 1.19,
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod

../float_test.pl
bin: 001111111111101111001010100101101001000110100111010111001101 0000
connecting
bin: 001111111111101111001010100101101001000110100111010111001101 0000



Regards,
Frank.

--
Dr. Frank Ullrich, DBA IT Zeitschriften
Heise Zeitschriften Verlag GmbH & Co KG, Helstorfer Str. 7, D-30625 Hannover
E-Mail: frank.ullrich@heise.de
Phone: +49 511 5352 587; FAX: +49 511 5352 538

Heise Zeitschriften Verlag GmbH & Co. KG
Registergericht: Amtsgericht Hannover HRA 26709

Persönlich haftende Gesellschafterin:
Heise Zeitschriften Verlag Geschäftsführung GmbH
Registergericht: Amtsgericht Hannover, HRB 60405
Geschäftsführer: Ansgar Heise, Steven P. Steinkraus, Dr. Alfons Schräder