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

[Douglas Surber]

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