Missing or null values

Within the Message Data Model (MDM) there are many optional fields. For example, body.html might be missing if an email message wasn't encoded with HTML. Within Message Query Language (MQL), null values are handled carefully and automatically to maintain consistent behavior.

📘

Rule of thumb for how MQL handles missing/null values

If an expression can't be evaluated because one of the required arguments is missing (null), then the result is also null. This also means that with not or !=, you want to take extra caution for the case where a field is null

To see how the boolean operations and, or, and not are performed in the presence of null, see boolean-logic-with-null

For example, a rule might consist of this logic like(body.html.raw, "*cloudtracking.gif*). What happens if body.html is missing? What if body.html.raw is missing?

This is easy to test by substituting null for the value. If a field is missing, like body.html, then any subfields will always be missing, like body.html.raw.

To test the behavior ahead of time, substitute null in its place.

like(null, "*cloudtracking.gif*")  -> null

# if body.html is missing:
like(body.html.raw, "*cloudtracking.gif*")  -> null

Comparisons to null values

If either side of a comparison (e.g. ,<, <=, ==, ...) is null, the full comparison will evaluate as null, even if both sides are null.

# if body.html is missing:
length(body.html.raw) > 4096 -> null

null == null -> null
null != null -> null

Test if a value is null/missing

To explicitly test if a value is missing, use is null or is not null syntax, which will always return true or false

# if body.html is missing
body.html is null     -> true
body.html is not null -> false

# if body.html is present
body.html is null     -> false
body.html is not null -> true

Boolean logic with null

A null boolean adds another level of logic to the boolean operators and, or and not. There are certain logical rules that must be maintained for consistency. For example, not (x == y) is the same as x != y. Similarly, not (x < y) is always equivalent to the expression x >= y. Because this is always true when the values are not null, it must remain true if a null is present.

null is never implicitly converted to false when performing boolean logic. Let's see what happens if we explicitly check for null using is not null for the below expression. Note how it changes the result so that it no longer returns null.

not (length(body.html.raw) > 4096)

not (length(body.html.raw) > 4096)
=> not (7000 > 4096)
=> not (true)
=> false

not (body.html is not null and (length(body.html.raw) > 4096))
=> not (true and (7000 > 4096))
=> not (true and true)
=> not (true)
=> false

not (length(body.html.raw) > 4096)
=> not (600 > 4096)
=> not (false)
=> true

not (body.html is not null and (length(body.html.raw) > 4096))
=> not (true and (600 > 4096))
=> not (true and false)
=> not (false)
=> true

not (length(body.html.raw) > 4096)
=> not (null > 4096)
=> not (null)
=> null

not (body.html is not null and (length(body.html.raw) > 4096))
=> not (false and null)
=> not (false and null)
=> not (false)
=> true

The truth tables for boolean operations are below. Both and and or are associative, meaning that x and y is equivalent to y and x.

x and y

x or y

not (x)

x of (... N clauses ...)
The table from of is derived from the behavior of and and or and is fully compatible. That is, 2 of (a, b, c) is always logically equivalent to (a and b) or (a and c) or (b and c).