Using Nulls in StreamBase Applications

Effect of Null Values in Expressions

In most cases with StreamBase expressions, any time you apply an arithmetic operator or function with a field that is null, the result is null. The three exceptions are the isnull() and notnull() functions, which test whether a field is null, and the coalesce() function, which selects a non-null value from a list of potentially null arguments.

If you want to use an expression that intentionally sets the result to null, you can use one of the data type-specific null literals, as shown in the list below.

blob(null)
bool(null)
int(null)
double(null)
long(null)
string(null)
timestamp(null)

Each null literal is case sensitive. For example, Int(NULL) is invalid. The data type of each null is never implicit: you must specify which type of null you are using in the expression.

Expression Example	Result
3 + int(null)	A null int
int(null) + int(null)	A null int
int(null) + bool(null)	A typecheck error. You cannot add an int and a bool.
if boolean(null) then 3 else 4	A null int
int(null) == int(null) or int(null) = int(null)	A null bool, because null is not equal to itself
int(null) != int(null)	A null bool, because null is not equal to itself
isnull(int(null))	A bool that evaluates to true
notnull(int(null))	A bool that evaluates to false

Effect of null values in Boolean logic

In general, expressions involving null will evaluate to null. However, Boolean logic statements can sometimes evaluate to true or false even if one argument is null. In the following table, the order of the arguments does not matter.

Expression	Result
bool(null) && bool(null)	A null bool
bool(null) AND true	A null bool
bool(null) && false	false
bool(null) \|\| bool(null)	A null bool
bool(null) OR true	true
bool(null) \|\| false	A null bool

The isnull and notnull Functions

StreamBase provides the isnull() and notnull() functions, allowing you to check whether fields in expressions are null.

isnull(value): Returns true if the argument is a null value.
notnull(value): Returns true if the argument is not a null value. This function always returns the opposite of isnull(value).

For example, an expression in a StreamBase operator could use:

if (isnull(fieldA)) then notnull(fieldB) else true

The coalesce Function

The coalesce() function returns the first non-null value from its list of arguments.

coalesce(value1, value2...): Returns the first argument that is non-null, or a null value if all arguments are null. All arguments must have the same type. For tuple arguments, returns the first tuple that is non-null.

Since literal values are never null, you can specify a literal value as the last argument to provide an effective default value for the list. In this way, coalesce() can be used to emulate the NVL() function provided by Oracle PL/SQL and the two-argument ISNULL() function provided by Microsoft T/SQL. For example, the following expression returns the value of fieldA if it is non-null, or 0 if it is null:

coalesce(fieldA, 0)

The following example returns the first non-null field among fieldA, fieldB, and fieldC in that order, or returns -99999 if all three fields are null.

coalesce(fieldA, fieldB, fieldC, -99999)

How to Specify nulls in Input Data

To illustrate how to specify nulls, assume an example where the schema for a tuple contains these fields:

fieldA, an integer (int)
fieldB, a string
fieldC, an int

In the input data CSV file:

To designate all three values of a tuple row to be null, enter: null,null,null.
To indicate that just fieldB is null, enter: 120,null,40.
To indicate that fieldA has a value, fieldB is an empty string, and fieldC is null, enter: 120,"",null.

The same rules apply when you use the Manual Input view in the SB Test/Debug perspective.

How each Operator Handles nulls

This table explains how each StreamBase operator handles null values in tuple fields.

Operator or Component	Result
Group-by parameter	Key fields that are null are accepted as a group.
Order-by parameter	Key fields that are null are dropped. StreamBase throws a NullValueException. For example, if a Merge operator encounters a null value on the Order-by field, the Error Log View in the SB Test/Debug perspective contains a message such as: error Non-Shutdown Error on Error Stream: (time=2006-02-20 12:17:44.579-0400,tupleid=1, subsystem="merge::Merge1", description="Null order-by value",streamname="InputStream1",nodename="mymachine", type="non-fatal-error",action="continue", time=2006-02-20 12:17:44.579-0400)
Filter operator	The evaluation of an expression in a Filter predicate that results in a NullValueException causes the tuple to be dropped.
Map operator	A Map operator assigns a value of `null` to a field whose expression evaluates to null. That is, if the evaluation of the field results in a NullValueException, then the value of the field `is null`.
Join operator	Boolean expressions return null. If tuple fields with null values are used in the boolean expression or the Order-by field, the tuple is ignored in the Join operation.
Merge operator	Any tuple with a `null` value in the ordering field is ignored.
Gather operator	Any tuple with a `null` value in the ordering field is ignored.
BSort operator	Any tuple with a `null` value in the ordering field is ignored. Note that a `null` value in the Group-by is grouped.
Query operator and Query Table data construct	If the key field for a tuple record being written to a Query Table is `null`, the tuple IS stored. In a Query Table with a sorted (btree) index, the null-keyed stored records are evaluated as less (in value) than other non-null records. On a subsequent read (lookup) operation, the null-keyed tuples can be located.
Lock and Unlock operators, and the Lock Set data construct	Any tuples with a `null` value in its lock field is ignored.
Aggregate operator	In Aggregate windows, if a field's value is `null`, the null value is not included in the calculation. For example: an average, such as `avg(price)` of ten tuples in an aggregate's window, if one of the values is null, then the average is calculated for the nine values. As noted previously, key fields that are null will be accepted as a group. In a aggregate's window, the aggregating function calculates a value, but it may be for a grouped set of tuples that have an unknown value (for example, if the stock's Symbol was missing). Also as noted previously, tuples with the key fields set to null are dropped (not figured into the aggregate's calculation).
Union operator	No impact.
Custom functions	Custom functions that you write should handle the processing of the NullValueException thrown by `get()` methods in Value.hpp of the StreamBase C++ API.

Using nulls in the StreamBase Client APIs

When developing StreamBase client applications, you will generally interact with nulls in the StreamBase API when you are either creating tuples to be enqueued, or receiving dequeued tuples.

When creating a tuple, it is important to note that all the values in the tuple will be initialized to null, and it is your responsibility to set the value. When dequeuing a tuple, determine whether a tuple field value is null before accessing the value. The following client code snippets demonstrate these concepts:

C++

// assume that s1 is a Schema with 5 fields
BufferedTuple t1(s1);
for ( unsigned int i = 0; i < s1.getNumFields(); i++ )
  assert( t1.isNull(i) );
t1.setBool("fbool",true);
t1.setInt("fint",1);
t1.setDouble("fdouble",1.11);
t1.setTimestamp("ftimestamp", Timestamp::now());
t1.setString("fstring","AA");
for ( unsigned int i = 0; i < s1.getNumFields(); i++ )
  assert( !t1.isNull(i) );
for ( unsigned int i = 0; i < s1.getNumFields(); i++ )
  t1.setNull(i);
for ( unsigned int i = 0; i < s1.getNumFields(); i++ )
  assert( t1.isNull(i) );
t1.setBool("fbool",true);
t1.setInt("fint",1);
t1.setDouble("fdouble",1.11);
t1.setTimestamp("ftimestamp", Timestamp::now());
t1.setString("fstring","AA");
for ( unsigned int i = 0; i < s1.getNumFields(); i++ )
  assert( !t1.isNull(i) );
t1.clear();
for ( unsigned int i = 0; i < s1.getNumFields(); i++ )
  assert( t1.isNull(i) );

Java

Tuple t1 = s1.createTuple();
for ( int i = 0; i < s1.getNumFields(); i++ )
  assert( t1.isNull(i) );
t1.setBoolean("fbool",true);
t1.setInt("fint",1);
t1.setDouble("fdouble",1.11);
t1.setTimestamp("ftimestamp", Timestamp.now());
t1.setString("fstring","AA");
for ( int i = 0; i < s1.getNumFields(); i++ )
  assert( !t1.isNull(i) );
for ( int i = 0; i < s1.getNumFields(); i++ )
t1.setNull(i);
for ( int i = 0; i < s1.getNumFields(); i++ )
  assert( t1.isNull(i) );
t1.setBoolean("fbool",true);
t1.setInt("fint",1);
t1.setDouble("fdouble",1.11);
t1.setTimestamp("ftimestamp", Timestamp.now());
t1.setString("fstring","AA");
for ( int i = 0; i < s1.getNumFields(); i++ )
  assert( !t1.isNull(i) );
t1.clear();
for ( int i = 0; i < s1.getNumFields(); i++ )
  assert( t1.isNull(i) );

For more information on using nulls in the API, see:

Changing the Null Token Shown in the SB Test/Debug perspective

By default, the SB Test/Debug perspective of StreamBase Studio uses the keyword null to indicate a null value. You can specify a different null token by changing the value in the Indicate nulls using field in the StreamBase Studio → Test/Debug pane of the Window → Preferences dialog.

The null token is used in editors and views when a field contains no value. You can substitute any string to indicate nulls. This change does not affect the use of null literals in expressions, which must use the reserved keyword shown in the previous sections, such as int(null).

Back to Top ^