thematic rules, expressions and null values

The issue I described earlier today involves a bit more than I  thought initially. Here are results for some further tests I did - all related to thematic rules and how <null> values in treated in Map expressions. All tests were done using just one thematic rule which was applied to column "CREATION_CODE_XY_1":

value                    count
<null>                    1222
2                            1
8                         5016   


Test 1 rule: "NOT CREATION_CODE_XY_1 IN (8)"

result in Map:
features with <null> value in column are displayed but cannot be snapped to
(features with CREATION_CODE_XY_1 = 2 are displayed as well but you can snap to them).

result in Oracle:
select count(*) from wdp_dp_punktelement where creation_code_xy_1 not in (8);

COUNT(*)
--------
       1

conclusion:
- results in Map and Oracle are different
- object snap doesn't work on  features with <null> values

If you add a second rule without any expression, set the symbol color to "none" and move the rule to the bottom then snapping does work (but additionally the "hidden" symbol from the second rule is snapped to as well).

    
Test 2 - rule: "CREATION_CODE_XY_1 <> 8"

result in Map:
features with <null> value are not displayed
(features with CREATION_CODE_XY_1 = 2 are displayed  and symbols can be snapped).

result in Oracle:
select count(*) from wdp_dp_punktelement where creation_code_xy_1 <> 8;

COUNT(*)
--------
       1

conclusion:
- results in Map and Oracle are the same
- expression from Test 1 is not that different from expression in Test 2 but the results in Map are quit  different


Test 3a - rule: CREATION_CODE_XY_1  NULL   OR   NOT  CREATION_CODE_XY_1  IN ( 8 )

results in Map:
features with  <null>  values are displayed and can be snapped

results in Oracle:
select count(*) from wdp_dp_punktelement where creation_code_xy_1 is null or creation_code_xy_1 not in (8);

COUNT(*)
--------
    1223

conclusion:
- results in Map and Oracle are the same
 
Test 3b - rule : NOT  CREATION_CODE_XY_1  IN ( 8 ) OR  CREATION_CODE_XY_1  NULL

results in Map:
features with <null> value are displayed, features can be snapped

results in Oracle:
select count(*) from wdp_dp_punktelement where creation_code_xy_1 not in (8) or creation_code_xy_1 is null;

COUNT(*)
--------
    1223

conclusion:
- results in Map and Oracle are the same

Test 4a - rule: CREATION_CODE_XY_1   NULL   OR   CREATION_CODE_XY_1   <> 8

results in Map:
features with <null> value are displayed, features can be snapped

results in Oracle:
select count(*) from wdp_dp_punktelement where creation_code_xy_1 is null or creation_code_xy_1 <> 8;

COUNT(*)
--------
    1223

conclusion:
- results in Map and Oracle are the same
 
Test 4b - rule: CREATION_CODE_XY_1  <>8  OR  CREATION_CODE_XY_1  NULL
 
results in Map:
features with <null> value are not displayed

results in Oracle:
select count(*) from wdp_dp_punktelement where creation_code_xy_1 <> 8 or creation_code_xy_1 is null;
COUNT(*)
--------
    1223

conclusion:
- results in Map and Oracle are different, order of expression changes result in Map but does not change it in Oracle (in Test 3b change of order didn't change result either in Map or Oracle)

summary:
- special attention is required when working with <null> values, the person who creates display models is not necessarily a database expert and doesn't know about <null> peculiarities
- always consider <null> values and use special operators such as "is null" "is not null"
- verify your rules with a test data set
- order of expressions might change result in Map
- Map and Oracle deliver different results where you would expect the same outcome

Finally another example in this context: the following rules were applied and the last rule was meant to deal with an "empty" column (but which actually contains <null> values):

ID_SYMBOLART  =  15  AND  ID_FARBE  =  1
….
to_char(ID_SYMBOLART)  = '' AND ID_FARBE  =  1

It was not possible to snap to the symbols on this layer.

The last rule needs to be rectified to:
NOT ID_SYMBOLART NULL AND ID_FARBE = 1

Map 2013, SP2