I'm implementing the new precision 34 decimal/numeric support in
Jaybird, and I notice that the underlying Decimal128 is used as just an
integer container, instead of already applying an appropriate exponent
for the scale. This was a bit of a surprise to me.
For example, a decimal(19,2) with value 123.45 returns a Decimal128 with
value 12345 (or 12345E0), where I would have expected it to already have
the scale applied, that is: 12345E-2.
Why was this implementation chosen? And doesn't this make things harder
when performing calculations (or other operations) with these values?
And if it is used as a container for a plain big integer, why not just
use a densely packed decimal (the format used in a Decimal128 to encode
the coefficient), or 'better', a binary encoded big integer? Both would
allow for even higher precision with the same storage requirements (or
smaller storage requirements for the same precision).
And also very interesting: inserting a pre-scaled Decimal128 value is
allowed, but results in a doubly scaled value (so in essence a
decimal(34,2) suddenly contains a value with scale 4). For example,
inserting a Decimal128 with value 123.45 (as in 12345E-2) into a
decimal(34,2) and then casting to varchar results in value 1.2345.
From a technical perspective, I would prefer if Firebird would use
(send and accept) the Decimal128 values that correspond with the value
(that is: correctly scaled, ie 12345E-2 in above examples), and reject
values that have the wrong scale.
Alternatively - but more complex - scale and round where appropriate
(that is sending a 100E0 value will be scaled to 10000E-2, and 123456E-3
is rounded to 12346E-2), and throw an overflow error if the value is too
big. (eg sending 1234567890123456789012345678901234E0 for a decimal(34,1)).
However, if the current solution is kept, then Firebird must reject any
Decimal128 parameter values with an (unbiased) exponent other than 0 to
prevent issues like demonstrated with inserting a pre-scaled value.
From the perspective of Jaybird, where otherwise I would have been able
to use the handling of Decimal128 I already had in place for DECFLOAT
(only needing to round to the target scale when sending values), I now
need to perform additional fiddling to either correctly scale on
receive, or get the unscaled value and convert that to Decimal128 when
sending values. In terms of memory, this will require the creation of at
least one additional intermediate object per value sent or received, or
creating ugly hacks to circumvent the need to create these intermediate
objects.
Mark
--
Mark Rotteveel
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
