# Special floating point values

Applies to: Databricks SQL Databricks Runtime

Several special floating point values are treated in a case-insensitive manner:

• Inf, +Inf, Infinity, +Infinity: positive infinity

• -Inf, -Infinity: negative infinity

• NaN: not a number

## Positive and negative infinity semantics

Positive and negative infinity have the following semantics:

• Positive infinity multiplied by any positive value returns positive infinity.

• Negative infinity multiplied by any positive value returns negative infinity.

• Positive infinity multiplied by any negative value returns negative infinity.

• Negative infinity multiplied by any negative value returns positive infinity.

• Positive or negative infinity multiplied by 0 returns NaN.

• Positive or negative infinity is equal to itself.

• In aggregations, all positive infinity values are grouped together. Similarly, all negative infinity values are grouped together.

• Positive infinity and negative infinity are treated as normal values in join keys.

• Positive infinity sorts lower than NaN and higher than any other values.

• Negative infinity sorts lower than any other values.

## NaN semantics

When dealing with `float` or `double` types that do not exactly match standard floating point semantics, NaN has the following semantics:

• NaN = NaN returns true.

• In aggregations, all NaN values are grouped together.

• NaN is treated as a normal value in join keys.

• NaN values go last when in ascending order, larger than any other numeric value.

## Examples

```> SELECT double('infinity');
Infinity

> SELECT float('-inf');
-Infinity

> SELECT float('NaN');
NaN

> SELECT double('infinity') * 0;
NaN

> SELECT double('-infinity') * (-1234567);
Infinity

> SELECT double('infinity') < double('NaN');
true

> SELECT double('NaN') = double('NaN');
true

> SELECT double('inf') = double('infinity');
true

> SELECT COUNT(*), c2
FROM VALUES (1, double('infinity')),
(2, double('infinity')),
(3, double('inf')),
(4, double('-inf')),
(5, double('NaN')),
(6, double('NaN')),
(7, double('-infinity'))
AS test(c1, c2)
GROUP BY c2;
2       NaN
2 -Infinity
3  Infinity
```