NULL values, relational algebra, and BMC Remedy AR System

This section explains how NULL values are used and interpreted within relational algebra qualifications, and discusses some special interpretations and overrides used within BMC Remedy AR System.

NULL values and BMC Remedy AR System

The NULL value is a special value that is different from any valid value for any data type. It is a legal value in any data type, and is used to represent missing or inapplicable information. Another way of thinking about NULL is as no value or the lack of information about the value.

In qualifications, a NULL value follows all the characteristics of relational algebra as defined in the previous section. Since search operations are passed directly to the database and they base their searches on relational algebra, this means BMC Remedy AR System server is based on relational algebra.

However, you are in full control of value assignment and the use of values within workflow. In these cases, you do not follow the strict rules about NULL handling. This allows the system to return a result more in line with what is expected. For example, if you wanted to perform a Set Fields operation where you assigned a Name field by concatenating the First Name, Middle Initial, and Last Name field, you might make an assignment like this:

'Name' = $First Name$ + $Middle Initial$ + $Last Name$

If processing used strict relational algebra, and the Middle Initial was NULL, the name assigned is be NULL. According to relational algebra, a NULL value within an operation causes the result of the operation to be NULL.

Instead, BMC Remedy AR System treats a NULL value within workflow actions (other than queries) as follows:

• Character, Diary, Timestamp, Selection — An empty string
• Integer, Real, Decimal — A value of 0

In the example, the name is assigned the First and Last name without middle initial.

By treating a NULL value as an empty value rather than as UNKNOWN in a true relational algebra sense, the result is what is expected instead of a NULL value.

Relational algebra and qualifications involving NULL values

All searching in the database uses the SQL language, which is based on the concepts of relational algebra. The following example illustrates how these concepts apply to the NULL value.

Suppose there is a form with a selection field called Field X, which allows two possible states: Yes or No. The form contains 20 records of which Field X is set to Yes for 7 of them, No for 8 of them, and NULL for 5 of them. When you search for the records, you receive the following results:

• Search for all tickets (without any conditions) results in 20 records returned.
• Search for all tickets where 'Field X' = NULL results in 5 records returned.
• Search for all tickets where 'Field X' = "Yes" results in 7 records returned.
• Search for all tickets where 'Field X' = "No" results in 8 records returned.

If you now search for all tickets where 'Field X' != "Yes", you might expect to receive 13 records returned (8 records where the field is No and 5 where it is NULL ) but you receive only 8 records. This is correct behavior according to the rules of relational algebra. NULL is NULL ; that is, it has no value. You must explicitly look for NULL values as they are not implicitly included in queries that search for values.

Relational algebra does not follow Boolean logic, where conditions are either true or false. Instead, every condition evaluates as one of TRUE, FALSE, or UNKNOWN. This is called three valued logic. The result of a comparison is UNKNOWN if either value being compared is the NULL value. Rows satisfy a search condition if the result of the condition is TRUE. Rows for which the condition is UNKNOWN do not satisfy the search condition.

The only way to find a row with a NULL value for a field in a search is to explicitly search for whether the field has a NULL value. Testing for NULL is the only case that a NULL value matches. So, in the example, to find all entries that are not Yes or are NULL, the qualification is

'Field X' != "Yes" OR 'Field X' = NULL

These criteria find the 13 records in the example data set.

Any comparison other than equal to and not equal to results in a value of UNKNOWN. In AND operations, at least one item is NOTTRUE, so the qualification does not succeed. In OR operations, this clause with NULL is UNKNOWN, so the qualification depends on the result of the other clauses. If all clauses evaluate to UNKNOWN, the qualification fails as a qualification.

If a NULL value is involved in an arithmetic operation, the result of the operation is NULL. In other words, any time a NULL value is involved, the entire operation becomes NULL.

As an example for this functionality, look at the following qualification

('Field A' = 5) OR ('Field B' > 'Field C' + 37)

If Field C has a NULL value, the second clause evaluates to UNKNOWN. Since the operation is an OR, the result of the qualification depends on whether Field A is set to 5 (success) or not (failure).

An alternative to using NULL is to have a value or state that represents unknown. Then, you always assign the value of Yes, No, or Unknown. Filters can be used to assign Unknown if the field is NULL. Then, the field always has a value and you do not have the issue about working with a NULL value.