Definition Query not showing NULL features when it should

Question

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.

Roads.lpkx

ZachBodenner_0-1737561950106.png

mlee · Answer

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

JoshuaBixby · Answer

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.

JenniferBorlick · Answer

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