Library Articles
Understanding How Null Values are Processed in a StreamBase Application
Author: Simon Keen
StreamBase Systems
10-January-2007
Topics:
The concept of a null value in relational theory has a very specific meaning. Null means not known as opposed to no value. Any expression involving a null value will produce a null value. Additionally, comparing null values using a conditional operator will yield null and not the anticipated true or false. For example, if the value of Price is null, then Price > 0 and Price <=0 will both return null.
Similarly, the expression Price == null will also return null and not true. Consequently, to test for null, always use the isnull() or notnull() functions.
StreamBase Simple Functions (e.g., abs() or sqrt()) applied to a null value will return null. So while sqrt(25) returns the anticipated 5, sqrt(null) returns null.
StreamBase Aggregate Functions (e.g., sum(), max(), min(), avg(), or count(field_identifier) will ignore any value that is null, so a valid return will be generated even if null values are present. The aggregate function count(), however, includes all tuples in its calculation, even if a tuple contains null fields.
As an example, consider five tuples with an integer field named myValue that contains the following values: 1, 2, null, 4, and 5. The function sum(myValue) returns 12, the function avg(myValue) returns 3, and the function count(myValue) returns 4; in all cases, the null value was not included in the calculation. But the function count() returns 5; that is, all tuples, even those containing null values, were included in the calculation.
Custom Java functions may be written using either primitive types (e.g., integer, double, boolean) or boxed types (e.g., Integer, Double, Boolean) as parameters and return values. Functions with primitive type parameters will not be invoked if any of the parameters are null and the return will be null. Functions accepting boxed type parameters will be invoked even if their enclosed values are null. Boxed return values may also encapsulate a null value. Therefore, if you want your application logic to process null parameters, you should write your custom functions using boxed parameter and return values.
Null is an indicator that the value of something is not known, not that it is not present. Therefore, null has the type of the underlying tuple field. To specify a null value, you must use the cast functions to indicate the type of the underlying field.
The cast functions are:
int(null)double(null)bool(null)string(null)timestamp(null)
Back to Top ^