Can someone confirm this?
I am running into a major issue where it seems Pro is omitting pairs of single quotes ('') in the display of the Definition Query in layer properties. Look at the attached image, and notice that at the location of the black arrow, no single quotes are visible.
The query clause should look like this (which is valid SQL):
(osm_covered IS NULL OR osm_covered IN ('','no'))
but instead reads like this:
(osm_covered IS NULL OR osm_covered IN (,'no'))
Hence, the query does not evaluate: if I click Verify the SQL expression is valid, the dialog displays an error stating the SQL is invalid. Note that with Clause Mode, I see the same issue, the consecutive single quotes are omitted and don't display in the Clause Mode display of the query.
The issue only seems to be limited to the dialog itself, because if I save the layer to *.lyrx layer file, and examine its contents using a text editor, the SQL Definition Query looks OK. Also, the layer properly displays in the map, which is only possible if internally in the layer object, the SQL is valid:
Yes, I just send an e-mail to the support desk of the Dutch branch of ESRI, with a reference to this thread.
I will post again when I get an answer of them. Could well be an already registered bug.
As to your suggestion: double quoting the single quotes is not the same as the clause I need. Double quoting the single quotes will test for the existence of a string with a contents of two consecutive single quotes. My clause should test for an empty string (which is not always the same as NULL in software systems, hence I test for this in the SQL).
This bug is still not fixed in 2.1.2. Can someone of ESRI inc. give an update? Was this ever logged at ESRI inc. as a bug?
It is sometimes difficult to get more information through the local ESRI distributors.
See the difference in the display of the Definition Query in ArcGIS Pro versus ArcMap in attached images:
ArcGIS Pro 2.1.2: