INF and NAN

TimesTen supports the IEEE floating-point values Inf (positive infinity), -Inf (negative infinity), and NaN (not a number).

Constant Values

You can use constant values in places where a floating-point constant is allowed. The following constants are supported:

  • BINARY_FLOAT_INFINITY

  • -BINARY_FLOAT_INFINITY

  • BINARY_DOUBLE_INFINITY

  • -BINARY_DOUBLE_INFINITY

  • BINARY_FLOAT_NAN

  • BINARY_DOUBLE_NAN

In the following example, a table is created with a column of type BINARY_FLOAT and a column of type TT_INTEGER. BINARY_FLOAT_INFINITY and BINARY_FLOAT_NAN are inserted into the column of type BINARY_FLOAT. 

Command> CREATE TABLE bfdemo (id BINARY_FLOAT, Ii2 TT_INTEGER);
Command> INSERT INTO bfdemo VALUES (BINARY_FLOAT_INFINITY, 50);
1 row inserted.
Command> INSERT INTO bfdemo VALUES (BINARY_FLOAT_NAN, 100);
1 row inserted.
Command> SELECT * FROM bfdemo;
< INF, 50 >
< NAN, 100 >
2 rows found.

Primary Key Values

Inf, -Inf, and NaN are acceptable values in columns defined with a primary key. This is different from NULL, which is not allowed in columns defined with a primary key.

You can only insert Inf, -Inf, and NaN values into BINARY_FLOAT and BINARY_DOUBLE columns.

Selecting Inf and NaN (Floating-Point Conditions)

Floating-point conditions determine whether an expression is infinite or is the undefined result of an operation (NaN, meaning not a number).

Consider the following syntax:

Expression IS [NOT] {NAN|INFINITE}

Expression must either resolve to a numeric data type or to a data type that can be implicitly converted to a numeric data type.

The following table describes the floating-point conditions.

Condition Operation Example

IS [NOT] NAN

Returns TRUE if Expression is the value NaN when NOT is not specified. Returns TRUE if Expression is not the value NaN when NOT is specified. 

SELECT * FROM bfdemo WHERE id IS
NOT NAN;
ID, ID2
< INF, 50 >
1 row found.

IS [NOT] INFINITE

Returns TRUE if Expression is the value +Inf or -Inf when NOT is not specified. Returns TRUE if Expression is neither +Inf nor -Inf when NOT is specified. 

SELECT * FROM bfdemo WHERE id IS
NOT INFINITE;
ID, ID2
< NAN, 100 >
1 row found.

Note:

The constant keywords represent specific BINARY_FLOAT and BINARY_DOUBLE values. The comparison keywords correspond to properties of a value and are not specific to any type, although they can only evaluate to TRUE for BINARY_FLOAT or BINARY_DOUBLE types or types that can be converted to BINARY_FLOAT or BINARY_DOUBLE.

The following rules apply to comparisons with Inf and NaN:

  • Comparison between Inf (or -Inf) and a finite value are as expected. For example, 5 > -Inf.

  • (Inf = Inf) and (Inf > -Inf) both evaluate to TRUE.

  • (NaN = NaN) evaluates to TRUE.

In reference to collating sequences:

  • -Inf sorts lower than any other value.

  • Inf sorts lower than NaN and NULL and higher than any other value.

  • NaN sorts higher than Inf.

  • NULL sorts higher than NaN. NULL is always the largest value in any collating sequence.

Expressions Involving Inf and NaN

  • Expressions containing floating-point values may generate Inf, -Inf, or NaN. This can occur either because the expression generated overflow or exceptional conditions or because one or more of the values in the expression was Inf, -Inf, or NaN. Inf and NaN are generated in overflow or division-by-zero conditions.

  • Inf, -Inf, and NaN values are not ignored in aggregate functions. NULL values are. If you want to exclude Inf and NaN from aggregates, or from any SELECT result, use both the IS NOT NAN and IS NOT INFINITE predicates.