Special floating point values

  • Article

Applies to: check marked yes Databricks SQL check marked yes 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