Definition Query not showing NULL features when it should

[JenniferBorlick]

I am having an issue with the definition query (and label class expression) of my roads features.

Background: The roads are created and edited in ArcMap 10.8.x on ArcGIS Server 10.6.1.9270 and exported to a 10.8.x server for use in ArcGIS Pro. I tested this in Pro 3.1 and 3.2. I also exported the feature class to a file geodatabase and had the same results.

The Class field contains NULL, empty, EMER, H20, CTY, etc.

If I do any SQL Query on the Class field I have to include CLASS IS NULL in the query for those features to appear.

For example: CLASS IS NULL Or CLASS NOT IN ('EMER', 'H2O') instead of CLASS NOT IN ('EMER', 'H2O')

We plan on populating that field with LOCAL into the future, but if anyone else is having an issue with definition queries not working as expected, check your fields for NULL values. I have included a layer package so you can test it out yourself if you would like. I'm assuming a bug? I'll submit a bug report.

[JoshuaBixby]

What you are experiencing is expected behavior, not just for ArcGIS Pro but for SQL in general.  NULL is neither TRUE or FALSE, it is UNKNOWN.  In your case, the results do not return NULL records because the database engine evaluating the query has no idea whether the NULL record is one of the values you want to exclude.  Only results that are guaranteed to be TRUE are returned by the query.

It is always best to handle NULL records explicitly, like you are already doing with the "IS NULL" logic.

[JenniferBorlick]

After speaking with Esri Support, it is something that they had figured out in 3.2, but it isn't working in 3.3. Hence why all of the sudden my roads disappeared. It is reported as a bug.

• BUG-000167452

[JoshuaBixby]

The Support analyst that logged this defect didn't realize Esri made intentional corrections to file geodatabase SQL support with Pro 3.3 to be consistent with the SQL standards, including changing how NULL is handled in certain situations.  I saw the documentation for the changes during Holistic testing, but I can't seem to find the public documentation about these changes.  I did find the originally logged defect around mishandling of NULL with file geodatabases that prompted Esri to make the updates, and it shows they made the change at Pro 3.3:  BUG-000168378 for ArcGIS Pro

Regardless, I expect this defect gets closed as expected behavior.

[mlee]

I have various plans that are missing half their content. I don't care what the international standard is, whether it's logical or illogical. what bothers me more is the fact that arcgis (especially arcgis pro) is constantly being programmed without thinking about the consequences or without testing the behaviour. in my opinion, that's a shame