Dealing with NULL (in Filters)

Filter strings are dynamically translated by Nimblex into backend specific SQL. As such, some quirks of the SQL language are partially visible and have to be managed.

SQL defines specific behaviour for dealing with NULLs that is confusing to a lot of people, but which are hard to entirely work around.

From postgres documentation (http://www.postgresql.org/docs/9.0/static/functions-comparison.html):

Binary comparison operators yield null (signifying “unknown”), not true or false, when either input is null. For example, 7 = NULL yields null

Wikipedia article about NULL 3 valued logic: http://en.wikipedia.org/wiki/Null_%28SQL%29#Comparisons_with_NULL_and_the_three-valued_logic_.283VL.29

This table is from Wikipedia and explains some of the weirdness involved.

Please note: our equality operator ‘=’ handles NULLs gracefully and thus all Unknowns (marked in green) in the ‘p = q’ column will be actually treat null like any other value.

p q p OR q p AND q p = q
False True True False False
False False False False True
False Unknown Unknown False Unknown (False)
True True True True True
True False True False False
True Unknown True Unknown Unknown (False)
Unknown True True Unknown Unknown (False)
Unknown False Unknown False Unknown (False)
Unknown Unknown Unknown Unknown Unknown (True)

Be aware that any value compared with NULL will in turn return NULL. This includes comparisons like:

25 > NULL

It also includes some boolean logic:

A AND B

This is likely to trip people up when more complex situations arise:

(A > 5) AND TRUE

If A happens to be NULL, this whole expression will return NULL.
If you need to do comparisons or logical operations with a column that MAY be NULL, you might need to include a protective IS NULL.
E.g.

(A IS NOT NULL AND (A > 5)) AND TRUE

We have put in special provisions for equality and negative equality. I.e. 1 = NULL will return FALSE, and 1 NULL will return TRUE.
Effected operations:

Related Articles