Aller au contenu principal
Dernière mise à jour le

Special floating point values

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​

SQL
> 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
Sur cette page