Why floating point representation are discouraged in ClickHouse tables?

user_3589232 0
Viewed 1733

The documentation is not really explaining the behavior of the Float32 and Float64 and why they are discouraged.

I'm asking this question because I'm seeing strange behavior when using theses with console cli requests or Rest requests. Float values that are sent to clickhouse are slightly modified at the last digit, whatever the precision is.

Example: 1258.021545 became 1258.0215453.

Each time I insert these values, the last digit is changed. I don't think the problem comes from a too high precision value because these values come from Java doubles.

clickhouse floating-point decimal-point
2 Answers

Floating-point numbers problem

Generally, computations with floating-point numbers might produce a rounding error.

Java

Please, consider learning the difference between double (a floating binary point type) and BigDecimal (a floating decimal point type) in the Java programming language.

For example, there is a related question: java - Double vs. BigDecimal? - Stack Overflow.

ClickHouse

From Float32, Float64 | ClickHouse Documentation:

Using Floating-point Numbers

  • Computations with floating-point numbers might produce a rounding error.

    SELECT 1 - 0.9

┌───────minus(1, 0.9)─┐
│ 0.09999999999999998 │
└─────────────────────┘

  • The result of the calculation depends on the calculation method (the processor type and architecture of the computer system).

  • Floating-point calculations might result in numbers such as infinity (Inf) and “not-a-number” (NaN). This should be taken into account when processing the results of calculations.

  • When parsing floating-point numbers from text, the result might not be the nearest machine-representable number.

Some possible solutions

Use integer numbers (recommended)

From Float32, Float64 | ClickHouse Documentation:

We recommend that you store data in integer form whenever possible. For example, convert fixed precision numbers to integer values, such as monetary amounts or page load times in milliseconds.

Use decimal numbers

From Decimal | ClickHouse Documentation:

Decimal(P, S), Decimal32(S), Decimal64(S), Decimal128(S), Decimal256(S)

Signed fixed-point numbers that keep precision during add, subtract and multiply operations. For division least significant digits are discarded (not rounded).

Parameters

  • P - precision. Valid range: [ 1 : 76 ]. Determines how many decimal digits number can have (including fraction).
  • S - scale. Valid range: [ 0 : P ]. Determines how many decimal digits fraction can have.

<…>

Decimal Value Ranges

  • 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, Decimal32(4) can contain numbers from -99999.9999 to 99999.9999 with 0.0001 step.

user_4900180
Related