Understanding Infinity and -Infinity in ClickHouse Arithmetic

  

When working with numerical data in ClickHouse, especially during divisions, you might come across values like inf (positive infinity) or -inf (negative infinity). This behavior may surprise those coming from traditional relational databases where such operations typically result in errors.

 

This post explains:

·      What inf and -inf mean in ClickHouse.

·      When they appear.

·      Why they are allowed.

·      Best practices when handling such values.

 

What Are inf and -inf?

In ClickHouse (and in IEEE 754 floating-point standard), certain operations that are mathematically undefined (like dividing by zero) result in special values:

 

·      inf: Positive Infinity (result of division like 1 / 0).

·      -inf: Negative Infinity (result of -1 / 0).

·      These are not errors, but valid Float64 values.

 

Example

SELECT 
1 / 0 AS pos_inf,
-1 / 0 AS neg_inf,
0.0 / 0.0 AS not_a_number;

krishna :) SELECT 
1 / 0 AS pos_inf,
-1 / 0 AS neg_inf,
0.0 / 0.0 AS not_a_number;

SELECT
    1 / 0 AS pos_inf,
    -1 / 0 AS neg_inf,
    0. / 0. AS not_a_number

Query id: de447700-70f1-4220-87e0-8ec620807cde

   ┌─pos_inf─┬─neg_inf─┬─not_a_number─┐
1. │     inf │    -inf │          nan │
   └─────────┴─────────┴──────────────┘

1 row in set. Elapsed: 0.001 sec.

 

Previous                                                    Next                                                    Home