NaN in ClickHouse: What It Means, When It Appears, and How to Handle It

In numerical computing, especially when working with floating-point operations, special values like NaN (Not a Number) can appear.

 

This post explains:

 

·      What NaN is in ClickHouse.

·      How it differs from null.

·      When NaN is generated.

·      How to detect and work with it safely.

 

What is NaN?

NaN stands for "Not a Number", and it is a special floating-point value defined by the IEEE 754 standard. In ClickHouse, NaN is used to represent the result of operations that don’t produce valid numbers, such as:

 

·      0.0 / 0.0

·      sqrt(-1)

·      log(-1)

·      inf - inf

krishna :) SELECT 0.0 / 0.0;

SELECT 0. / 0.

Query id: 7d62e4dc-ad92-496d-b6c5-69abadf92577

   ┌─divide(0., 0.)─┐
1. │            nan │
   └────────────────┘

1 row in set. Elapsed: 0.002 sec. 

krishna :) ;

Empty query

krishna :) SELECT sqrt(-1);

SELECT sqrt(-1)

Query id: cd7581cd-19e9-4f00-b768-3274f5eecda5

   ┌─sqrt(-1)─┐
1. │      nan │
   └──────────┘

1 row in set. Elapsed: 0.001 sec. 

krishna :) ;

Empty query

krishna :) SELECT log(-1);

SELECT log(-1)

Query id: 7df725b9-26d9-4a8d-9b06-6a39eb296acd

   ┌─log(-1)─┐
1. │     nan │
   └─────────┘

1 row in set. Elapsed: 0.001 sec. 

krishna :) ;

Empty query

krishna :) SELECT inf - inf;

SELECT inf - inf

Query id: 314fc13b-174a-4ac1-9da4-a153927e2fbd

   ┌─minus(inf, inf)─┐
1. │             nan │
   └─────────────────┘

1 row in set. Elapsed: 0.002 sec.

  

How to detect NaN in ClickHouse?

ClickHouse provides a built-in function isNaN(x) return 1 if x is NaN, else 0.

krishna :) SELECT isNaN(0/0);

SELECT isNaN(0 / 0)

Query id: 63542911-ee66-491b-8ac9-72dbc44fc2df

   ┌─isNaN(divide(0, 0))─┐
1. │                   1 │
   └─────────────────────┘

1 row in set. Elapsed: 0.002 sec. 

krishna :) ;

Empty query

krishna :) SELECT isNaN(16/2);

SELECT isNaN(16 / 2)

Query id: fe8e6666-e754-4223-bc60-1464c4a6bbfc

   ┌─isNaN(divide(16, 2))─┐
1. │                    0 │
   └──────────────────────┘

1 row in set. Elapsed: 0.002 sec.

   

In summary,

·      NaN is a special floating-point value representing invalid or undefined numeric results.

·      It appears in operations like 0.0 / 0.0, sqrt(-1), inf - inf, etc.

·      NaN is not the same as null.

·      Use isNaN() to detect and filter it.

·      Be cautious with NaN values in analytics and reporting, they can silently propagate incorrect values if not handled properly.

 

 

Previous                                                    Next                                                    Home