The issue is to simulate SQL DECIMAL arithmetic using IEEE Decimal<N>
Consider the division 20 / 3. Using Decimal64:
20 / 3 -> 6.666666666666666666666666666666667
Using DECIMAL(38, 6), or many other reasonable combinations:
20 / 3 -> 6.666667
We could expose a richer API in Decimal<N>, similar to BigDecimal's. It
is doable, but beyond IEEE and my current plan.
In addition, I find that the point issued by Maurizio about the best
name for the Valhalla primitive class is important enough for the
community at large to warrant a rumination pause.
Let me ponder about how to enrich the API to support SQL for a couple of
days or so. In the meantime, the Valhalla guys may come up with a good
solution to better assist migration of @ValueBased classes to truly
primitive classes without disrupting existing code that depends on them,
as described by Maurizio in his previous post.
On 2021-03-31 23:24, Douglas Surber wrote:
My understanding is that IEEE decimal floating point is intended for currency.
A large fraction of numeric values stored in databases are currency. It's not
obvious to me why an e-commerce web site would not want to use Decimal128 to
represent prices, extensions, taxes, discounts, totals, etc.
On Mar 31, 2021, at 2:17 PM, Raffaello Giulietti
<raffaello.giulie...@gmail.com> wrote:
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
