[External] : Re: Proposal for Decimal64 and Decimal128 value-based classes

[Raffaello Giulietti]

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.giulietti at 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.giulietti at 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
>