DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=sender:date:from:to:cc:subject:message-id:references:mime-version
	:content-type:content-disposition:in-reply-to:user-agent;
	b=R7JnKrmc8irLOJ982gnwus/N6adDC3C3z7Os40Ll4ct5iMTiL8es5ugQCuwWipyHSP
	jp2Wd+IuWHjzfs6CF1O12enQja5OdZSGvxcvV7HUxClDIOBUClhYsj1+7xzwDCeRvrcH
	GtxzJeGWL8USq5YHJ3gylZak1MrZsD0mZpOrw=
Date: Tue, 28 Jul 2009 09:53:05 -0700
From: Tim Bunce <Tim.Bunce@pobox.com>
To: dbi-users@perl.org
Cc: dbi-announce@perl.org
Subject: SERIOUS BUG IN DBD::Pg 2.14.0 for bigint types
Message-ID: <20090728165305.GA79427@timac.local>
References: <0fc2eba725c9e37c70799877a847d411@biglumber.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <0fc2eba725c9e37c70799877a847d411@biglumber.com>
User-Agent: Mutt/1.5.17 (2007-11-01)

On Tue, Jul 28, 2009 at 02:44:55AM -0000, Greg Sabino Mullane wrote:
> 
> Version 2.14.0 of DBD::Pg has been released and is available on CPAN. 
> 
> 2.14.0 Released July 27, 2009 (subversion r13130)
> 
>   - Return ints and bools-cast-to-number from the db as true Perlish numbers.       
>     (CPAN bug #47619) [GSM]                                                         

The code now stores bigint values (8 bytes long) in a floating point
variable (NV).  Most perl's are configured with the NV type being a
simple C 'double' which is typically 8 bytes:

    $ perl -V:'^nv(size|type)'
    nvsize='8';
    nvtype='double';

You can't store a large 8 byte int value into an 8 byte floating point
value without loss of precision. (See appended explanation.)

To be completely clear about this:

     DBD::Pg 2.14.0 may *SILENTLY ALTER LARGE BIGINT VALUES*

Here's a fix:

--- dbdimp.c.orig	2009-07-28 09:46:21.000000000 -0700
+++ dbdimp.c	2009-07-28 09:46:35.000000000 -0700
@@ -3381,9 +3381,6 @@
 					case PG_INT2:
 						sv_setiv(sv, atol((char *)value));
 						break;
-					case PG_INT8:
-						sv_setnv(sv, atoll((char *)value));
-						break;
 					default:
 						sv_setpvn(sv, (char *)value, value_len);
 					}

Tim.


=head3 Perl Floating Point Values

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

Basically, a floating point value is represented internally by two
values.  One value, the mantissa, holds a binary I<approximation> 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<perl -V> and
looking for C<nvsize=> in the output.  The 64 bit floats are known as
I<doubles> and have approximately 15 digits of precision between 1e-307
to 1e+308, and the 96 bit floats are known as I<long doubles> and have
approximately 18 digits of precision between 1e-4931 to 1e+4932. Some
systems support 128 bit I<quad doubles> 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).