Null, NaN, and infinity values

Null, not-a-number (NaN), and infinity values can unexpectedly appear in your datasets and disrupt calculations if you don't handle them. In a real-time table, any update can bring in a new one, so it pays to handle them in the query itself.

This guide shows you how to keep your data analysis running smoothly. It lists the constants Deephaven provides for these special values, then shows how built-in functions treat them and how to replace them, filter them out, and check for them in your own Groovy closures.

Nulls represent missing data. For a primitive numeric type or char, Deephaven reserves one value of the type as its null. For example, it reserves -32768 as the null value for the primitive short data type, so the minimum short value is -32767 instead of the traditional -32768. Booleans and object types use Java null instead.

NaNs represent undefined mathematical results. For instance, the logarithm of a negative number is undefined, so log returns NaN for a negative input.

Infinities represent mathematically infinite values. For example, in floating-point arithmetic, dividing a positive number by 0.0 produces positive infinity.

Available constants

Deephaven provides a null constant for each primitive numeric type and char, plus NULL_BOOLEAN, which equals Java null, for Boolean columns. Only float and double have NaN and infinity constants.

The constants are built into the query language, so you can use them in Groovy code and in query strings without an import. In each subsection below, the first example prints the constants from Groovy, and the second uses them in a query string and shows the column types with meta.

Nulls

The following null constants are available in Deephaven:

The char null is cast to int so that it prints as a number instead of an unprintable character.

For float and double, the null value printed above is the most negative finite value of the type. The smallest non-null finite value is one step above it. For double, that value is MIN_FINITE_DOUBLE.

NaNs

The following NaN constants are available in Deephaven:

Infinities

The following infinity constants are available in Deephaven:

Handle null, NaN, and infinity values

Each example in this section builds columns that hold special values in selected rows. To choose those rows, the examples use the ternary conditional operator with ii, the built-in variable that holds the row position.

Built-in functions

The functions built into the query language accept null, NaN, and infinity values without extra checks in your query, but they treat each kind differently:

  • Null: most functions skip null inputs or return null.
  • NaN and infinity: functions follow IEEE floating-point rules, so a NaN or infinite input usually gives a NaN or infinite result.

For example, avg skips null values but returns NaN for an array that contains a NaN.

The following example passes each kind of value to the built-in sin function. Every fourth row holds the special value:

Where the input is null, Y1 is null. Where the input is NaN or infinite, Y2, Y3, and Y4 are NaN.

Arithmetic operators in query strings treat nulls the same way: if either operand is null, the result is null. To test for a null, use X == NULL_DOUBLE. To test for a NaN, use isNaN(X), because X == NAN_DOUBLE is never true. Comparison operators covers the other null and NaN comparison rules.

Replace values

Replacing null, NaN, and infinity values is a common practice when a dataset should have a default value. The following code block uses built-in functions to replace each kind of value with -1:

Filter out values

Use these built-in functions to filter out special values:

  • isNull returns true for null values.
  • isNaN returns true for NaN values.
  • isInf returns true for infinite values.
  • isFinite returns false for null, NaN, and infinite values, so a single check removes all three.

The following example negates the first three functions with the logical NOT operator ! to keep the rows without that value. It uses isFinite as is:

Groovy closures

To check for null or infinite inputs in a Groovy closure, compare each input against the built-in constants.

Check for NaN with Double.isNaN instead of comparing against NAN_DOUBLE. In Java, NaN never equals itself. Groovy's == doesn't always follow that rule: in a closure like the one below, inputValue == NAN_DOUBLE returns true for a NaN input, but the same comparison between two double variables returns false. Double.isNaN returns the right answer in both cases.

The following closure returns a different value for each kind of input. Without a cast, a closure call produces an Object column, so each query string casts the closure's result to double: