Skip Menu |
 

Preferred bug tracker

Please visit the preferred bug tracker to report your issue.

This queue is for tickets about the DBD-Oracle CPAN distribution.

Report information
The Basics
Id: 80358
Status: open
Priority: 0/
Queue: DBD-Oracle

People
Owner: Nobody in particular
Requestors: me [...] awirtz.com
Cc:
AdminCc:

Bug Information
Severity: Important
Broken in: 1.50
Fixed in: (no value)



Subject: NUMBER is cast to float, not to nvtype
Download (untitled) / with headers
text/plain 790b
When Perl is compiled with nvtype='long float', all numeric equality operations use an epsilon value appropriate for this precision. However, the DBD::Oracle driver always internally casts NUMBER values to 8-byte floats even when the Perl binary uses nvsize=16. This causes a large enough rounding error that Perl's numeric equality operators no longer operate correctly. The following test script illustrates the issue: #!/usr/bin/perl use DBI; use Test::Simple 'tests' => 1; my $dsn = 'dbi:Oracle(Username=>scott,Password=>tiger):MYORADB'; my $dbh = DBI->connect($dsn) or die $DBI::errstr; my($value) = $dbh->selectrow_array('select 1.2 from dual'); ok($value == 1.2, '(NUMBER)1.2 == (scalar)1.2'); This test script fails on Perl 5.14.2 nvtype='long double' with DBD::Oracle 1.50
On Tue Oct 23 16:45:53 2012, me@awirtz.com wrote: Show quoted text
> When Perl is compiled with nvtype='long float', all numeric equality > operations use an epsilon > value appropriate for this precision. However, the DBD::Oracle driver > always internally casts > NUMBER values to 8-byte floats even when the Perl binary uses > nvsize=16. This causes a large > enough rounding error that Perl's numeric equality operators no longer > operate correctly. The > following test script illustrates the issue: > > #!/usr/bin/perl > use DBI; > use Test::Simple 'tests' => 1; > my $dsn = 'dbi:Oracle(Username=>scott,Password=>tiger):MYORADB'; > my $dbh = DBI->connect($dsn) or die $DBI::errstr; > my($value) = $dbh->selectrow_array('select 1.2 from dual'); > ok($value == 1.2, '(NUMBER)1.2 == (scalar)1.2'); > > This test script fails on Perl 5.14.2 nvtype='long double' with > DBD::Oracle 1.50
I'm guessing those casts need to use an NV. A quick look at the code suggests fixing this might be quite involved. I'm not familiar with that code but I'll try and take a look. Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 1.7k
On Wed Oct 24 04:11:24 2012, MJEVANS wrote: Show quoted text
> On Tue Oct 23 16:45:53 2012, me@awirtz.com wrote:
> > When Perl is compiled with nvtype='long float', all numeric equality > > operations use an epsilon > > value appropriate for this precision. However, the DBD::Oracle driver > > always internally casts > > NUMBER values to 8-byte floats even when the Perl binary uses > > nvsize=16. This causes a large > > enough rounding error that Perl's numeric equality operators no longer > > operate correctly. The > > following test script illustrates the issue: > > > > #!/usr/bin/perl > > use DBI; > > use Test::Simple 'tests' => 1; > > my $dsn = 'dbi:Oracle(Username=>scott,Password=>tiger):MYORADB'; > > my $dbh = DBI->connect($dsn) or die $DBI::errstr; > > my($value) = $dbh->selectrow_array('select 1.2 from dual'); > > ok($value == 1.2, '(NUMBER)1.2 == (scalar)1.2'); > > > > This test script fails on Perl 5.14.2 nvtype='long double' with > > DBD::Oracle 1.50
> > I'm guessing those casts need to use an NV. > > A quick look at the code suggests fixing this might be quite involved. > I'm not familiar with that code but I'll try and take a look. > > Martin
hmmm, DBD::Oracle binds that number as a string. If you look at the SV for 1.2 and the one returned by DBD::Oracle you'll get: SV for 1.2: SV = NV(0x925b454) at 0x92b3258 REFCNT = 1 FLAGS = (PADMY,NOK,pNOK) NV = 1.2 SV returned by DBD::Oracle: SV = PV(0x9386730) at 0x92b7dd8 REFCNT = 1 FLAGS = (PADMY,POK,pPOK) <-- no NV, it is only a string PV = 0x93ba478 "1.2"\0 CUR = 3 LEN = 12 so I don't see how this has anything to do with DBD::Oracle. e.g., my $x = "1.2"; my $y = 1.2; ok($x == $y, 'test'); fails also. Martin -- Martin J. Evans Wetherby, UK
From: me [...] awirtz.com
Download (untitled) / with headers
text/plain 617b
On Wed Oct 24 05:11:10 2012, MJEVANS wrote: Show quoted text
> > my $x = "1.2"; > my $y = 1.2; > ok($x == $y, 'test'); > > fails also.
Something seriously wacky is going on here. That test, in isolation, actually passes for me... until I connect to an Oracle DB, and then it begins failing! Here's my current test script wherein only test #6 fails: use DBI; use Test::Simple 'tests' => 6; ok('1.2' == 1.2); ok('1.2' == ($_ = 1.2)); DBI->connect('dbi:SQLite:dbname=:memory'); ok('1.2' == 1.2); ok('1.2' == ($_ = 1.2)); DBI->connect('dbi:Oracle(Username=>scott,Password=>tiger):MYORADB'); ok('1.2' == 1.2); ok('1.2' == ($_ = 1.2));
Download (untitled) / with headers
text/plain 1.2k
On Wed Oct 24 15:34:07 2012, me@awirtz.com wrote: Show quoted text
> On Wed Oct 24 05:11:10 2012, MJEVANS wrote:
> > > > my $x = "1.2"; > > my $y = 1.2; > > ok($x == $y, 'test'); > > > > fails also.
> > Something seriously wacky is going on here. > That test, in isolation, actually passes for me... until I connect to > an Oracle DB, and then it > begins failing! > Here's my current test script wherein only test #6 fails: > > use DBI; > use Test::Simple 'tests' => 6; > ok('1.2' == 1.2); > ok('1.2' == ($_ = 1.2)); > DBI->connect('dbi:SQLite:dbname=:memory'); > ok('1.2' == 1.2); > ok('1.2' == ($_ = 1.2)); > DBI->connect('dbi:Oracle(Username=>scott,Password=>tiger):MYORADB'); > ok('1.2' == 1.2); > ok('1.2' == ($_ = 1.2));
Admittedly, I cannot explain that right but it still doesn't make sense to compare floating point numbers because they are not exact. It is possible the Oracle client libraries define a symbol for a function that Perl uses but it behaves differently. This won't be easy to identify but you could try running your test again with lazy loading (set and export PERL_DL_NONLAZY=1 before running the test) - didn't work for me. You might be better looking for a wider audience for this issue as I'm not sure right now. Perhaps, dbi-users list or perlmonks. Martin -- Martin J. Evans Wetherby, UK
From: me [...] awirtz.com
Download (untitled) / with headers
text/plain 1.6k
On Wed Oct 24 16:24:10 2012, MJEVANS wrote: Show quoted text
> Admittedly, I cannot explain that right but it still doesn't make sense > to compare floating point numbers because they are not exact. > > It is possible the Oracle client libraries define a symbol for a > function that Perl uses but it behaves differently. This won't be easy > to identify but you could try running your test again with lazy loading > (set and export PERL_DL_NONLAZY=1 before running the test) - didn't work > for me. > > You might be better looking for a wider audience for this issue as I'm > not sure right now. Perhaps, dbi-users list or perlmonks. > > Martin
I completely agree as a matter of style regarding floating point comparisons (and am happy to discover that DBD::Oracle does indeed preserve decimal precision by casting NUMBER fields to PVs - the Perl side of the code I'm maintaining really should have been written as a string- comparison). The only reason this came up was because some existing code which used to work suddenly stopped working under very specific conditions. The decimal value from the DB is not the result of arithmetic, but happens to be a constant representing an intermediate state which was defined sometime after states "1" and "2" already existed. Internally Oracle has no problem with this as NUMBERs are stored as base-100 floats rather than IEEE binary floats. I have resolved my particular situation by reverting to a perl build which uses 8-byte floats, but this remains worrisome in that is seems to indicate a deeper issue with the Oracle XS and/or OCI. I did try the "PERL_DL_NONLAZY" flag, and for me also, it does not resolve the issue. I may do more investigation as time allows. -Aaron


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.