How Does FME Handle Null Attribute Values?

Question

What is a null attribute value? How does FME handle null attribute values?
 

Answer

A null attribute value can be seen as representing an unknown value. It is distinct from an empty string attribute value, which is a known value, or a missing attribute. In a database table, for example, if we record someone's middle name as an empty string, it means this person has no middle name. On the other hand, if his middle name is recorded as null, it means we do not know what his middle name is.

Whereas a null attribute is present on the feature, a missing attribute is absent. Using the same database example, if we need to update someone's record, we can choose to supply attribute values for only the fields of interest, for example, for the income and address fields, but leave the first name, middle name, and last name as missing attributes, so that they would not be updated.
 

Support for Null in Expressions

• Null values are evaluated in numeric expressions. For example: 5 + <null> = <null>
• Missing values and empty strings are treated as null values in numeric expressions. For example 5 + “” = <null>
• Math functions that use a fixed number of arguments - Null, missing, empty string, and non-numeric arguments cause the function to return null. For example: @cos(null) = null
• Functions that take in a variable number of arguments - Null, missing, empty string, and non-numeric arguments are skipped. However, if the variable argument list contains nothing but null, missing, and empty string values, the function returns null. For example: @sum(5,null) = 5
• For math operators - If any operand is null, missing, the empty string, or non-numeric, the operator will return null. For example: 5  <= null = null
• Error handling:
  • Most expression evaluation errors result in expressions returning null. 
  • Divide by zero (0) returns the “inf” value
  • Do not rely on translation failures to identify expression evaluation errors. Instead, look for nulls in the results, or features with the list attribute fme_expression_warnings{}. See the help for more details.

SQL support

The handling of null, missing, and empty string arguments and operands are to a large extent SQL compatible, and to a lesser extent Excel compatible.
 

Format Support for Null

Most formats that are null aware (i.e. databases) will support null

• Attributes can be set to null for formats that are null-aware
• Null attributes will explicitly be displayed as null
• In cases where null cannot be propagated, it will be converted to an empty string
• Writers ensure that null fields (attributes) are written out as null values. Dataset UPDATE mode, existing field values can be updated to nulls.

Visualization

Data Inspector and Visual Preview distinguish between null, missing, and empty string attribute values. In Table View, null and missing values are displayed as <null> and <missing>, respectively, with a gray font color. Empty string values are displayed as blank fields. In Feature Information, null values are displayed as <null> with a gray font color, missing values are not displayed, and empty string values are displayed as blank fields.

FME logs distinguish between null and missing attribute values. Null values will be logged as <null>, and missing values will not be logged. Null values will be logged as "is <null>", whereas strings whose content is a literal "null" will be logged as "has value 'null'".

Transformers

NullAttributeMapper – can be used to do bulk mapping of attribute values to/from null, missing, and empty strings.
Many transformers have options for handling attributes containing null values, or to allow for the setting of attributes to null values – in particular:

• AttributeCreator – can be used to set null values
• AttributeValueMapper – will map to and from nulls
• Tester, TestFilter, Matcher, ChangeDetector, AttributeFilter – now differentiate between null, missing, and empty attribute values
• AttributeValidator – has a "not Null" validation rule
• AttributeCopier/Renamer/ValueMapper – can set null as a default value
• SQLCreator, SQLExecutor – include null attributes in the database tables attribute picklist
• DatabaseJoiner, InlineQuerier & ArcSDEQuerier - have null support.

FME Objects

FME Objects supports null and missing values through its FME Objects APIs. Support is added both for features. Existing API methods and functions that wrap around the string versions of getAttribute() and getTrait() from the FME core return empty strings for nulls.

The Python API is special in that its getAttribute() and getTrait() methods wrap around both string and non-string versions of getAttribute() and getTrait() from the FME core. The Python getAttribute() and getTrait() return empty strings for null attribute values.