Decimal Operators¶

The result precision and scale computation of arithmetic operators contains two stages. First stage computes precision and scale using formulas based on the SQL standard and Hive when allow-precision-loss is true. The result may exceed maximum allowed precision of 38.

Second stage caps precision at 38 and either reduces the scale or not depending on allow-precision-loss flag.

The allow-precision-loss flag applies to both regular and checked (ANSI mode) arithmetic functions. In Spark, there are no separate checked expression classes. The same expression (e.g., Add) handles both ANSI and non-ANSI behavior, controlled by an EvalMode flag. In Velox, the checked variants are registered as separate functions (e.g., checked_add, checked_subtract) to support the TRY evaluation mode (e.g., try(checked_add(...)) returns NULL on overflow).

Regular functions return NULL on overflow, while checked functions throw an error.

For example, addition of decimal(38, 7) and decimal(10, 0) requires precision of 39 and scale of 7. Since precision exceeds 38 it needs to be capped. When allow-precision-loss, precision is capped at 38 and scale is reduced by 1 to 6. When allow-precision-loss is false, precision is capped at 38 as well, but scale is kept at 7. With allow-precision-loss all additions will succeed, but accuracy (number of digits after period) of some operations will be reduced. Without allow-precision-loss, some additions will return NULL.

For example,

The following queries keep accuracy or return NULL when allow-precision-loss is false:

select cast('1.1232154' as decimal(38, 7)) + cast('1' as decimal(10, 0)); -- 2.123215
select cast('9999999999999999999999999999999.2345678' as decimal(38, 7)) + cast('1' as decimal(10, 0)); -- NULL

These same operations succeed when allow-precision-loss is true:

select cast('1.1232154' as decimal(38, 7)) + cast('1' as decimal(10, 0)); -- 2.12321, lost the last digit
select cast('9999999999999999999999999999999.2345678' as decimal(38, 7)) + cast('1' as decimal(10, 0)); -- 10000000000000000000000000000000.234568

Decimal Precision and Scale Computation Formulas¶

The HiveQL behavior:

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

Additionally, the computation of decimal division adapts to the allow-precision-loss flag, while the decimal addition, subtraction, multiplication and integer division do not.

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¶

When allow-precision-loss is true:

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

When allow-precision-loss is false:

wholeDigits = min(38, p1 - s1 + s2);
fractionalDigits = min(38, max(6, s1 + p2 + 1));
p = wholeDigits + fractionalDigits
s = fractionalDigits

Integer Division¶

precision = p1 - s1 + s2
p = precision == 0 ? 1 : min(38,  precision)
s = 0

Decimal Precision and Scale Adjustment¶

When allow-precision-loss is true, rounds the decimal part of the result if an exact representation is not possible. Otherwise, returns NULL. Notice: some operations succeed if precision loss is allowed and return NULL if not.

For example,

select cast(0.1234567891011 as decimal(38, 18)) * cast(1234.1 as decimal(38, 18));
-- 152.358023 if allow-precision-loss, NULL otherwise.

Below formula illustrates how the result precision and scale are adjusted.

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

When precision loss is not allowed, caps p at 38, and keeps scale as is. The below formula shows how the precision and scale are adjusted for decimal addition, subtraction, and multiplication.

precision = 38
scale = min(38, s)

Decimal division uses a different formula:

precision = 38
scale = fractionalDigits - (wholeDigits + fractionalDigits - 38) / 2 - 1

Returns NULL when the actual result cannot be represented with the calculated decimal type.

Arithmetic Functions¶

add(x: decimal(p1, s1), y: decimal(p2, s2)) -> r: decimal(p3, s3)¶

Returns the result of adding x and y. The result type is determined by the precision and scale computation rules described above. Returns NULL when the result overflows. Corresponds to Spark’s operator + with spark.sql.ansi.enabled set to false.

SELECT CAST(1.1 as DECIMAL(3, 1)) + CAST(2.2 as DECIMAL(3, 1)); -- 3.3
SELECT CAST('99999999999999999999999999999999999999' as DECIMAL(38, 0)) + CAST(1 as DECIMAL(38, 0)); -- NULL

checked_add(x: decimal(p1, s1), y: decimal(p2, s2)) -> r: decimal(p3, s3)¶: Returns the result of adding x and y. The result type is determined by the precision and scale computation rules described above. Throws an error when the result overflows. Corresponds to Spark’s operator + with spark.sql.ansi.enabled set to true.

checked_div(x: decimal(p1, s1), y: decimal(p2, s2)) → bigint¶: Performs integer division and returns the bigint result of dividing x by y, truncating toward zero. Truncation occurs if the result is within the result precision but exceeds the BIGINT range. Division by zero or overflow results in an error. Does not have allow-precision-loss variants because IntegralDivide always returns LongType (result scale is 0), so precision loss is not applicable. Corresponds to Spark’s operator div with spark.sql.ansi.enabled set to true.

checked_subtract(x: decimal(p1, s1), y: decimal(p2, s2)) -> r: decimal(p3, s3)¶: Returns the result of subtracting y from x. The result type is determined by the precision and scale computation rules described above. Throws an error when the result overflows. Corresponds to Spark’s operator - with spark.sql.ansi.enabled set to true.

div(x: decimal(p1, s1), y: decimal(p2, s2)) → bigint¶

Performs integer division and returns the bigint result of dividing x by y, truncating toward zero. Truncation occurs if the result is within the result precision but exceeds the BIGINT range. Division by zero or overflow results in NULL. Does not respect the allow-precision-loss configuration. Corresponds to Spark’s operator div with spark.sql.ansi.enabled set to false.

SELECT CAST(1 as DECIMAL(17, 3)) div CAST(2 as DECIMAL(17, 3)); -- 0
SELECT CAST(21 as DECIMAL(20, 3)) div CAST(20 as DECIMAL(20, 2)); -- 1
SELECT CAST(1 as DECIMAL(20, 3)) div CAST(0 as DECIMAL(20, 3)); -- NULL
SELECT CAST(99999999999999999999999999999999999 as DECIMAL(38, 1)) div CAST(0.001 as DECIMAL(7, 4)); -- 687399551400672280 // Result is truncated to int64_t.

subtract(x: decimal(p1, s1), y: decimal(p2, s2)) -> r: decimal(p3, s3)¶

Returns the result of subtracting y from x. The result type is determined by the precision and scale computation rules described above. Returns NULL when the result overflows. Corresponds to Spark’s operator - with spark.sql.ansi.enabled set to false.

SELECT CAST(1.1 as DECIMAL(3, 1)) - CAST(2.2 as DECIMAL(3, 1)); -- -1.1
SELECT CAST('-99999999999999999999999999999999999999' as DECIMAL(38, 0)) - CAST(1 as DECIMAL(38, 0)); -- NULL

Decimal Functions¶

ceil(x: decimal(p, s)) -> r: decimal(pr, 0)¶

Returns x rounded up to the type decimal(min(38, p - s + min(1, s)), 0).

SELECT ceil(cast(1.23 as DECIMAL(3, 2))); -- 2 // Output type: decimal(2,0)

decimal_equalto(x, y) → boolean¶: Returns true if x is equal to y. Supports decimal types with different precisions and scales. Corresponds to Spark’s operator ==.

decimal_greaterthan(x, y) → boolean¶: Returns true if x is greater than y. Supports decimal types with different precisions and scales. Corresponds to Spark’s operator >.

decimal_greaterthanorequal(x, y) → boolean¶: Returns true if x is greater than y or x is equal to y. Supports decimal types with different precisions and scales. Corresponds to Spark’s operator >=.

decimal_lessthan(x, y) → boolean¶: Returns true if x is less than y. Supports decimal types with different precisions and scales. Corresponds to Spark’s operator <.

decimal_lessthanorequal(x, y) → boolean¶: Returns true if x is less than y or x is equal to y. Supports decimal types with different precisions and scales. Corresponds to Spark’s operator <=.

decimal_notequalto(x, y) → boolean¶: Returns true if x is not equal to y. Supports decimal types with different precisions and scales. Corresponds to Spark’s operator !=.

floor(x: decimal(p, s)) -> r: decimal(pr, 0)¶

Returns x rounded down to the type decimal(min(38, p - s + min(1, s)), 0).

SELECT floor(cast(1.23 as DECIMAL(3, 2))); -- 1 // Output type: decimal(2,0)

in(x: decimal(p, s), array(decimal(p, s))) → boolean¶: Returns true if x matches at least one of the elements of the array.

unaryminus(x: decimal(p, s)) -> r: decimal(p, s)¶

Returns negated value of x (r = -x). Corresponds to Spark’s operator -.

SELECT unaryminus(cast(-9999999999999999999.9999999999999999999 as DECIMAL(38, 19))); -- 9999999999999999999.9999999999999999999

unscaled_value(x) → bigint¶: Return the unscaled bigint value of a short decimal x. Supported type is: SHORT_DECIMAL.

Decimal Special Forms¶

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

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.