Decimal Operators

When calculating the result precision and scale of arithmetic operators, the formulas follow Hive which is based on the SQL standard and MS SQL:

https://cwiki.apache.org/confluence/download/attachments/27362075/Hive_Decimal_Precision_Scale_Support.pdf

https://msdn.microsoft.com/en-us/library/ms190476.aspx

Addition and Subtraction

p = max(p1 - s1, p2 - s2) + max(s1, s2) + 1
s = max(s1, s2)

Multiplication

p = p1 + p2 + 1
s = s1 + s2

Division

p = p1 - s1 + s2 + max(6, s1 + p2 + 1)
s = max(6, s1 + p2 + 1)

For above arithmetic operators, when the precision of result exceeds 38, caps p at 38 and reduces the scale, in order to prevent the truncation of the integer part of the decimals. Below formula illustrates how the result precision and scale are adjusted.

precision = 38
scale = max(38 - (p - s), min(s, 6))

Users experience runtime errors when the actual result cannot be represented with the calculated decimal type.

Decimal Functions

unscaled_value(x) → bigint

Return the unscaled bigint value of a short decimal x. Supported type is: SHORT_DECIMAL.

Decimal Special Forms

make_decimal(x[, nullOnOverflow]) → decimal

Create decimal of requsted precision and scale from an unscaled bigint value x. By default, the value of nullOnOverflow is true, and null will be returned when x is too large for the result precision. Otherwise, exception will be thrown when x overflows.

decimal_round(decimal[, scale]) → [decimal]

Returns decimal rounded to a new scale using HALF_UP rounding mode. In HALF_UP rounding, the digit 5 is rounded up. scale is the new scale to be rounded to. It is 0 by default, and integer in [INT_MIN, INT_MAX] is allowed to be its value. When the absolute value of scale exceeds the maximum precision of long decimal (38), the round logic is equivalent to the case where it is 38 as we cannot exceed the maximum precision. The result precision and scale are decided with the precision and scale of input decimal and scale. After rounding we may need one more digit in the integral part.

::

SELECT (round(cast (9.9 as decimal(2, 1)), 0)); – decimal 10 SELECT (round(cast (99 as decimal(2, 0)), -1)); – decimal 100

When scale is negative, we need to adjust -scale number of digits before the decimal point, which means we need at least -scale + 1 digits after rounding, and the result scale is 0.

SELECT round(cast (0.856 as DECIMAL(3, 3)), -1); -- decimal 0
SELECT round(cast (85.6 as DECIMAL(3, 1)), -1); -- decimal 90
SELECT round(cast (85.6 as DECIMAL(3, 1)), -2); -- decimal 100
SELECT round(cast (85.6 as DECIMAL(3, 1)), -99);  -- decimal 0
SELECT round(cast (12345678901234.56789 as DECIMAL(32, 5)), -9); -- decimal 12346000000000

When scale is 0, the result scale is 0.

SELECT round(cast (85.6 as DECIMAL(3, 1))); -- decimal 86
SELECT round(cast (0.856 as DECIMAL(3, 3)), 0); -- decimal 1

When scale is positive, the result scale is the minor one of input scale and scale. The result precision is decided with the number of integral digits and the result scale, but cannot exceed the max precision of decimal.

SELECT round(cast (85.681 as DECIMAL(5, 3)), 1); -- decimal 85.7
SELECT round(cast (85.681 as DECIMAL(5, 3)), 999); -- decimal 85.681
SELECT round(cast (0.1234567890123456789 as DECIMAL(19, 19)), 14); -- decimal 0.12345678901235