Decimal

Decimal data types represent signed fixed-point numbers, ensuring precise arithmetic for addition, subtraction, and multiplication. When performing division, any least significant digits are truncated rather than rounded.

This category includes Decimal, Decimal(P), Decimal(P, S), Decimal32(S), Decimal64(S), Decimal128(S), and Decimal256(S).

Parameters

  • P (Precision): Defines the total number of decimal digits a number can hold, including both the integer and fractional parts. Its valid range is [1 : 76]. If not specified, the default precision is 10.
  • S (Scale): Specifies the number of digits allowed in the fractional part of the number. Its valid range is [0 : P].

The syntax Decimal(P) is equivalent to Decimal(P, 0), meaning no fractional digits. Similarly, Decimal without any parameters defaults to Decimal(10, 0).

Based on the precision P, Decimal(P, S) is an alias for:

  • Decimal32(S) for P values from [1 : 9]
  • Decimal64(S) for P values from [10 : 18]
  • Decimal128(S) for P values from [19 : 38]
  • Decimal256(S) for P values from [39 : 76]

Decimal value ranges

The range of values a Decimal type can store depends on its precision and scale:

  • Decimal32(S): ( -1 * 10^(9 - S), 1 * 10^(9 - S) )
  • Decimal64(S): ( -1 * 10^(18 - S), 1 * 10^(18 - S) )
  • Decimal128(S): ( -1 * 10^(38 - S), 1 * 10^(38 - S) )
  • Decimal256(S): ( -1 * 10^(76 - S), 1 * 10^(76 - S) )

For example, a Decimal32(4) type can store numbers ranging from -99999.9999 to 99999.9999, with a minimum step of 0.0001.

Internal representation

Internally, Decimal values are stored as standard signed integers with varying bit widths. While the actual memory capacity might be slightly larger, the specified ranges are enforced during string conversions.

Operations on Decimal128 and Decimal256 are emulated because modern CPUs do not natively support 128-bit and 256-bit integers. This emulation means that Decimal128 and Decimal256 types perform significantly slower than Decimal32 or Decimal64.

Operations and result type

Binary operations involving Decimal types typically result in a wider Decimal type to maintain precision. The order of arguments does not affect this.

  • Decimal64(S1) <operator> Decimal32(S2) results in Decimal64(S)
  • Decimal128(S1) <operator> Decimal32(S2) results in Decimal128(S)
  • Decimal128(S1) <operator> Decimal64(S2) results in Decimal128(S)
  • Decimal256(S1) <operator> Decimal<32|64|128>(S2) results in Decimal256(S)

Rules for determining the scale (S) of the result:

  • For addition and subtraction: S = max(S1, S2).
  • For multiplication: S = S1 + S2.
  • For division: S = S1.

When performing operations between a Decimal type and an integer, the result will be a Decimal type of the same size as the original Decimal argument.

Direct operations between Decimal types and Float32 or Float64 are not supported. To perform such operations, you must explicitly cast one of the arguments using functions like toDecimal32, toDecimal64, toDecimal128, toFloat32, or toFloat64. Be aware that type conversions can lead to a loss of precision and are computationally intensive.

Some functions, such as var or stddev, may return results as Float64 even when operating on Decimal inputs. Intermediate calculations might still use Decimal precision, which could lead to different results compared to direct Float64 inputs with the same values.

Overflow checks

During calculations with Decimal types, integer overflows can occur. If the fractional part of a number exceeds its defined scale, the excess digits are truncated (not rounded). However, if the integer part overflows its capacity, an exception will be thrown.

Overflow checking is not implemented for Decimal128 and Decimal256. In the event of an overflow with these types, an incorrect result will be returned without an exception being thrown.

SELECT toDecimal32(2, 4) AS x, x / 3

Result:

┌──────x─┬─divide(toDecimal32(2, 4), 3)─┐
│ 2.0000 │                       0.6666 │
└────────┴──────────────────────────────┘

SELECT toDecimal32(4.2, 8) AS x, x * x

Result:

DB::Exception: Scale is out of bounds.

SELECT toDecimal32(4.2, 8) AS x, 6 * x

Result:

DB::Exception: Decimal math overflow.

Overflow checks can impact performance. If you are certain that overflows will not occur in your calculations, you can disable these checks using the decimal_check_overflow setting. If checks are disabled and an overflow happens, the result will be incorrect.

Overflow checks are also performed during value comparisons:

SELECT toDecimal32(1, 8) < 100

Result:

DB::Exception: Can't compare.
Updated