On 2013.01.05 5:39 AM, Lyle wrote:
On 05/01/2013 10:41, Darren Duncan wrote:
Since the SQL standard, as well as some other programming languages, define
"decimal" as being exact numeric, then it is absolutely wrong to map them to
either FLOAT or DOUBLE. In fact, in Perl 5 the only native type that can map
to a DECIMAL is a character string of numeric digits. Don't shoehorn this.
There is no good reason to map DECIMAL to a floating point type.
I'm not overly familiar with Perl's internal handling of number. I guess if you
have DECIMAL from a character string Perl will switch it out to an approximate
the moment you do a calculation on it. Furthermore if the DBI (or the DBDs, I'm
not sure where the distinction lies) is already putting it into a Perl decimal
which is floating point, then the battle has already been lost before it gets to
me.
My understanding is that Perl 5 has 3 internal data types of relevance here,
which are integers, floats, and strings.
A DBMS integer can be represented perfectly by a Perl integer as long as it is
within the Perl integer range, which AFAIK these days is typically a 64-bit
machine int but used to be 32 bits, either depending on your configure settings
at compile time (perl -V can tell you). If the integer is larger than that, eg
some DBMSs support 128 bit ints, you'd need to use a Perl string to represent it
perfectly.
A DBMS float might be exactly representable by a Perl float, maybe, depending on
their details, or there might be a round-off in the conversion; now while that
loss might not matter, it means round-tripping may not produce an identical
value, which is a larger concern in my mind.
A DBMS DECIMAL has no native Perl equivalent and you'd have to use a string in
the general case.
As for what DBDs actually do, well that's a different matter; but I'm talking
about what *could* be done in the Perl somewhere, and typically I'd expect the
DBD to make that decision on the Perl's behalf.
Yes, doing naive calculation on numbers-as-strings could cause loss, but at
least having it as strings initially gives the Perl code the choice for what to
do because it has all the detail.
Now Perl does also have BigInt, BigRat, BigFloat etc which could be utilized for
exact transference, but AFAIK no DBD does that as it would hurt performance or
add further dependencies.
-- Darren Duncan