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

Reply via email to