Why floating point representation are discouraged in ClickHouse tables?

Question

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.

Answer 1

The only reason why ClickHouse documentation dicourages Float is a rounding error. It's has nothing to do with the ClickHouse itself, but rather an algorithm for real number representations.

• If you don't mind some rounding errors every now and then, go ahead and use floats.
• But shall you store numbers representing money, floats are a no-go zone. In this case store parts left and right to the decimal point in two different fields as integers.

Answer 2

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.