The Expression Handler has the following behavior relative to the processing of columns with NULL values:
-
Definition: If any column value used in an expression contains a NULL value, then the result of the expression is characterized as “undefined”. This is true for both a condition expression and a result expression.
-
During the parsing and validation pass of the expression, if any “nulls allowed” columns are referenced in an expression, then a warning message is issued to the log for each column, indicating that an undefined result is possible.
-
During the runtime processing of a condition expression (that is, a gate condition), if the value of any column referenced in the expression is NULL, then an exception is thrown indicating an “undefined” result. In the case of a gate condition, an “undefined” result causes a message to be issued and then “false” is returned as the result of the condition.
-
During the runtime processing of a result expression, if the value of any column referenced in the expression is NULL, then an exception is thrown indicating “undefined”. In the case of a result expression, an “undefined” result causes some messages to be issued and the following occurs:
-
If the target column is “nulls allowed”, a NULL value is returned as the result.
-
If the target column is defined as NOT NULL, a warning message is issued and a NULL result is returned.
-
It is possible for the user to code the explicit return of a NULL value for a target result column that allows it. That is, the target column must be defined with the “nulls allowed” attribute. However, this can only be specified in the return statement of a complex expression (that is, “return NULL;”).
Specifying a NULL result (that is, “NULL;”) is currently NOT allowed in a simple expression.
For example: “begin returns int; return NULL; end;” is allowed and is essentially equivalent to “NULL;”.
-
In a complex expression, it is possible for the user to code an explicit return of a NULL value (see previous point). In this case, regardless of the values that referenced columns have in the expression, the return of an explicit NULL value by the expression is characterized as a “NULL result” instead of an “undefined result”. However, despite different characterization, the action that is taken (that is, the result that is returned) is exactly the same as in the previous situation. The only difference between the two situations where the target column is “nulls allowed” and NOT NULL is the set of messages that is issued to the log.
-
Keep in mind the following restriction:
Only column identifiers can be checked directly for null in a predicate as shown in the following 2 examples:
<column> eq NULL
<column> ne NULL
Because of this rule, expressions such as the following return an error.
UpdateBeforeValue(<column>) == NULL
concat(<column1>,<column2>) == NULL