Re: Selecting tiny numbers with many significant digits returns NULL

On 29/09/10 22:22, Ken Marks wrote:
>  While trying to run a statistical calculation Perl script that I have
> been running for several years it started crashing. I spent some time to
> track down the problem to my Perl script selecting a fairly small value
> from a table and getting NULL in response. I wrote a simplified script
> that demonstrated the problem so I could experiment to see what was
> going on. Below is that script:
> 
> use DBI;
> use English;
> use strict;
> no strict 'subs';
> 
> my ($dbh, $sth, $foobar);
> 
> $dbh = DBI->connect('DBI:ODBC:<database_name>', '', '', {RaiseError => 
> 1, AutoCommit =>  1});
> 
> $sth = $dbh->prepare("select 2.775557561562E-17");
> $sth->execute();
> $foobar = $sth->fetchrow;
> print "foobar=$foobar\n";
> 
> $sth = $dbh->prepare("select 2.7755575615629E-17");
> $sth->execute();
> $foobar = $sth->fetchrow;
> print "foobar=$foobar\n";
> 
> exit(0);
> 
> 
> When I run this program the output is as follows:
> 
> foobar=2.775557561562E-17
> foobar=
> 
> 
> It seems that when the mantissa of a number has too many significant
> digits DBI returns a NULL instead of the number. The only difference in
> the second case is the addition of an additional digit. This took some
> time to boil my problem down to this simple issue. Searching on the
> Internet has yet to yield any similar issues (with or without
> solutions). As a result I am appealing to this list to either point out
> something incredibly bone-headed that I am doing or to try to reproduce
> this in their environment and propose a solution.
> 
> Right now I am looking at dropping in a bunch of ROUND() functions in my
> SQL that is generating the number giving me fits but that seems to be
> more of a kludge workaround than a proper fix. Any thoughts from the
> deities on this list?
> 
> Before anybody asks, I'm running Perl v5.10.1 (with all the most recent
> module versions loaded through ppm) on a Windows 7 machine.
> 
> Thanks in advance for anybody spending time on this issue that is
> keeping me from doing useful work.
> 
> 
> 
> KM
> 
> 
With Perl 5.10.1 built on Linux and

DBD::ODBC (subversion trunk)
DBI 1.615
Easysoft SQL Server ODBC driver

I get:

foobar=2.77556E-17
foobar=2.77556E-17

On windows with Perl 5.8.9 I get:

sql server odbc driver:

C:\perlbuild_xxx\DBD-ODBC-1.26_1>perl sigdigits.pl
foobar=2.7755575615619999E-17
foobar=2.7755575615629E-17

sql server native client driver:
C:\perlbuild_xxx\DBD-ODBC-1.26_1>perl sigdigits.pl
foobar=2.7755575615619999E-17
foobar=2.7755575615629E-17

On Windows I can add as many digits as I like and always get something back.

What ODBC driver are you using?

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