Hi Douglas,
yes, different vendors have different limits on the precision, the most
extreme probably being PostgreSQL.
But apart from that, the arithmetic is different.
A better option is to implement some optimized fixed precision classes
like SQLDecimal38 and SQLDecimal65 + a more general variable precision
SQLDecimal. But, as I mentioned, this is something different than
Decimal<N>.
Greetings
Raffaello
On 2021-03-31 22:53, Douglas Surber wrote:
Understood. The problem is that right now the only appropriate type for
non-integer SQL numbers is BigDecimal. It's too big and too slow and lots of
users avoid it.
Decimal128 supports 34 significant digits. The max precision of SQL numeric
types varies from vendor to vendor. In SQL Server it is 38. In MySQL it is 65.
So there are a huge range of values representable in SQL that are not
representable in Decimal128. BUT, for the vast majority of applications that
might be tempted to use Decimal128, those non-representable numbers don't
occur. Currency amounts exceeding 34 decimal digits of precision are an almost
non-existent minority.
Very few apps will pay the price of using BigDecimal even though it would
support huge numbers exactly. Instead they find workarounds that are more
efficient. Decimal128 would be a substantial improvement for those apps.
Douglas
On Mar 31, 2021, at 1:13 PM, Raffaello Giulietti
<raffaello.giulie...@gmail.com> wrote:
Hi,
I think there's a misunderstanding about the nature of IEEE 754 Decimal<n>
(e.g., Decimal64), the subject of this thread, and the nature of SQL DECIMAL(p, s).
SQL DECIMAL(p, s) represent *fixed* point decimal numbers, with an overall
maximum precision p and a scale s, the number of digits to the right of the
decimal point (both parameters can be selected freely inside some ranges). For
example, DECIMAL(2, 1) can represent only the values
-9.9, -9.8, ..., 9.8, 9.9
and that's it.
Thus, the sum 6.6 + 7.7 overflows, as well as the product 2.9 * 4.
IEEE decimals are *floating* point decimal numbers. A hypothetical decimal of
precision 2 can represent values of the form c*10^q, where integer c meets |c|
< 100 (that is, max two digits) and integer q is limited in some range. It
covers the values above and much more, for example, 0.012 (=12*10^(-3)) and -3.4E2
(=-34*10^1).
The sum 6.6 + 7.7 produces 14 because the mathematical result 14.3 is rounded
to the closest number of precision 2 (assuming RoundingMode.HALF_EVEN). By the
same token, the product 2.9 * 4 produces 12, which is 11.6 rounded to 2 digits.
But really, the position of the decimal point is floating.
IEEE decimals and SQL decimals are fundamentally different and ave different
arithmetic, so I wouldn't recommend using the proposed classes for JDBC.
On the positive side, SQL decimals, are easier to implement if the maximum
allowed p in DECIMAL(p, s) is reasonable, say 38. But that's another topic.
Greetings
Raffaello
