Precision and Accuracy in DECIMAL Calculations

In DolphinDB, a DECIMAL value is divided into two parts for storage: a raw integer containing the raw data, and a scale denoting the number of decimal places. For example, the decimal value 1.23 with a precision of 2 decimal places would be stored as the raw integer 123 and the scale: 2. This storage approach allows calculations to be performed directly on the integer data to avoid precision loss.

When calculating DECIMAL values with functions that return Floating results, calculations are first performed on the integers. The eventual result is converted to Floating type to avoid precision loss during calculation. For example, when calculating the average of DECIMAL32(2) values [1.11, 2.22, 3.33], DolphinDB first computes the sum of the integers 111 + 222 + 333 = 666, and then converts the result to DOUBLE type and divides by the count double(666) / 10^2 / 3 = 2.22.

For the rules of DECIMAL arithmetic operations, see DECIMAL Data Type.

This section discusses the return types when a computational function takes DECIMAL values as inputs.

Only the following DDB functions take DECIMAL inputs and also return DECIMAL results to retain the full decimal precision throughout: sum, max, min, first, last, firstNot, lastNot, and their corresponding cum, m, tm and TopN functions, as well as cumPositiveStreak.

a = decimal64(rand(10,100),4)

typestr(sum(a))
// output: DECIMAL128

typestr(cummax(a))
// output: FAST DECIMAL64 VECTOR

typestr(mmin(a,5))
// output: FAST DECIMAL64 VECTOR

T = 2023.03.23..2023.06.30
typestr(tmsum(T, a, 3))
// output: FAST DECIMAL128 VECTOR

typestr(cumPositiveStreak(a))
// output: FAST DECIMAL128 VECTOR

Note that DECIMAL128 is introduced since version 2.00.10, and the input and output of sum-related functions (including sum/cumsum/msum/tmsum/msumTopN/cumsumTopN/tmsumTopN/cumPositiveStreak) are as follows:

Except the functions listed above, functions such as avg, std, var, and skew, and their corresponding cum, m, tm, TopN functions, take DECIMAL inputs and return DOUBLE results.

a = decimal64(rand(10,100),4)

typestr(avg(a))
// output: DOUBLE

typestr(cumstd(a))
// output: FAST DOUBLE VECTOR

typestr(mvar(a,5))
// output: FAST DOUBLE VECTOR

T = 2023.03.23..2023.06.30
typestr(tmskew(T, a, 3))
// output: FAST DOUBLE VECTOR

The following table lists the output data types when calculating DECIMAL64 data:

Floating numbers have inherent limitations in accurately representing real values in computer systems. This stems from two main reasons. First, binary floating-point formats can only approximate decimal values that have finite representations, such as 0.1. The binary representation cannot precisely encode such decimal fractions. Second, Floating types have a finite range and precision. If a number exceeds the maximum or minimum representable value, the computer system will round or truncate it, introducing further imprecision. In contrast, the DECIMAL data type is capable of precisely representing and computing with data that has an exact decimal representation.

For example, the Floating type is unable to precisely represent the value 123.0001, whereas the DECIMAL type can accurately represent it.

a =123.0001
print(a)
// output: 123.000100000000003

b = decimal64(`123.0001,15)
print(b)
// output: 123.000100000000000

Calculating avg of Floating numbers may yield precision errors, whereas DECIMAL returns an accurate result of DOUBLE type.

a = array(DOUBLE,0)
for (i in 1..100){
	a.append!(123.0000+0.0003*i)
}
avg(a)
// output: 123.015149999999
avg(a) == 123.01515
// output: false
eqFloat(avg(a),123.01515)
// output: true

b= array(DECIMAL64(4),0)
for (i in 1..100){
	b.append!(123.0000+0.0003*i)
}
avg(b)
// output: 123.015150000000
typestr(avg(b))
// output: DOUBLE
avg(b) == 123.01515
// output: true

Overflow Issues

The numeric ranges for the DECIMAL32/DECIMAL64/DECIMAL128 types are as shown in the table below, where S designates the preserved decimal digits.

DECIMAL values may encounter overflow issues constrained by the valid numeric range and maximum number of digits. As of version 2.00.10, DolphinDB supports automatic conversion to a higher precision DECIMAL type to avoid overflow in arithmetic operations:

version 2.00.9.6: 
a = decimal32(4.0000,4)
b = decimal32(8.0000,4)
c = a*b
// Server response: 'c = a * b => Decimal math overflow'

version 2.00.10: 
a = decimal32(4.0000,4)
b = decimal32(8.0000,4)
c = a*b
print(c)
// output: 32.00000000
typestr(c)
// output: DECIMAL64

However, there still remains overflow risks with DECIMAL128:

a = decimal128(36.00000000,8)
b = a*a*a*a
// Server response: 'b = a * a * a * a => Decimal math overflow'

As shown in the above example, when multiplying two DECIMAL values in DolphinDB, the result scale is determined by summing the operand scales. This can lead to an overflow error if the number of digits exceeds the DECIMAL precision limit. In the case of variable "b", the expected result is 1679616.00000000000000000000000000000000, which contains 39 digits. This exceeds the maximum representation of 38 digits for DECIMAL128 and leads to an overflow.

As of version 2.00.10, the decimalMultiply function is introduced for DECIMAL multiplication. Compared with the multiply function (and * operator), this function allows specifying the precision of result. In cases where multiplying DECIMAL values could lead to overflow due to accumulated scales, decimalMultiply can be used to set the result precision explicitly and avoid overflow.

a = decimal64(36.00000000,8) decimalMultiply(a, a, 8)
// output: 1296.00000000

Conversion Errors

If we generate DECIMAL values from constants, errors might occur due to the conversion of floating-point numbers:

a = 0.5599
decimal64(a, 4) 
// output: 0.5598

To mitigate such errors, using strings to generate DECIMAL types is recommended:

a = "0.5599"
decimal64(a, 4)
// output: 0.5599

Memory Usage

DECIMAL32, DECIMAL64, and DECIMAL128 types occupy 4 bytes, 8 bytes, and 16 bytes of memory. FLOAT and DOUBLE types use 4 bytes and 8 bytes. DECIMAL128 could take up twice the memory of DOUBLE to represent the same value. Additionally, arithmetic overflows in DECIMAL32 and DECIMAL 64 that trigger type promotion may double the memory usage. This can pose a potential memory risk when working with large datasets of DECIMAL type.

Computing Performance

Compared to FLOAT and DOUBLE types, calculations of DECIMAL are slower due to the more complex internal representation. Specific performance comparisons will be presented in the next chapter.

Limitations

Currently, the DECIMAL type in DolphinDB has fewer functions and less compatibility than FLOAT/DOUBLE:

• Some computational functions do not yet support DECIMAL data types.
• Most statistical functions will generate Floating results even when taking DECIMAL numbers as inputs.
• Matrices and sets do not support DECIMAL data types.
• For data type conversion, the DolphinDB system currently does not support the conversion between DECIMAL types and BOOL/CHAR/SYMBOL/UUID/IPADDR/INT128 types or temporal types. To convert STRING or BLOB type to DECIMAL, the string must be convertible to a numeric type.