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|
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.
(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.