[MDEV-30376] DECFLOAT data type Created: 2023-01-10  Updated: 2023-10-04

Status: Open
Project: MariaDB Server
Component/s: Data types
Fix Version/s: None

Type: Task Priority: Minor
Reporter: Robert Palm Assignee: Sergei Golubchik
Resolution: Unresolved Votes: 0
Labels: None

Attachments: PNG File db2.png    

 Description   

I'd like to see the DECFLOAT (= Decimal floating-point) data type get implemented in MariaDB.

The type offers base 10 floating point arithmetic.

References:

  1. IEEE 754-2008
  2. Decimal floating-point in z9: An implementation and testing perspective
  3. DB2 11.5 Numbers
  4. Book: Numbers and Computers - Chapter 7
  5. Wikipedia: Decimal floating-point


 Comments   
Comment by Sergei Golubchik [ 2023-02-03 ]

what for?

Comment by Robert Palm [ 2023-02-08 ]

Thank you for asking back. In short I think it is the ideal type for base 10 calculations.

Some quotes from Ref. 4:

Humans work in base ten almost universally so making calculations in base ten seems quite reasonable. 
 
The exactness of the results of DFP calculations makes this a perfect number format for commercial or financial work. 
 
Many, if not most, databases are using decimal storage for monetary values, often as character strings in base ten, so that the possible application of DFP to these databases is obvious.
It would probably also result in smaller storage requirements and would certainly result in more accuracy or fewer checks for edge cases that cannot be allowed in financial calculations. 
 
The logistic example above clearly shows that DFP results are sometimes “truer” than binary floating-point. 
The preservation of knowledge that there are meaningful trailing zeros in a value is also potentially of significance. 
The Python example returning “0.10” as the answer instead of “0.1” is helpful as it tells users that the digit in the hundredths place is truly zero and not simply unknown. 
A scientific result might require a great deal of effort to know that that digit is exactly zero so it would be good if the computer representation preserved it.
 
The thought perhaps should not be “should I use decimal floating-point?” but rather, “Is there any valid reason why I should not use decimal floating-point?”

Comment by Sergei Golubchik [ 2023-02-28 ]

MariaDB's DECIMAL type is doing base 10 calculations. Would that serve your needs?

Comment by Robert Palm [ 2023-03-15 ]

Here a example on db2:

create table test.tbl (c1 decfloat(34), c2 decimal(31,15));
insert into test.tbl (c1, c2) values (1.003, 1.003);
select * from test.TBL;

For C1 I can immediately see which number it is. For C2 I need to strengthen my eyes if there are only 0s after the last significant digit.

It is a bit like someone asking for JSON and you ask back if you can do it with XML

Generated at Thu Feb 08 10:15:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.