Definition Query Bug

[BrianKaplan]

I'm using ArcGIS Pro 2.4.3

I'm using the interactive Definition Query window as shown in the attached.

'Congo, Dem Rep of the' is not added correctly to the SQL string.

Below is the result in the SQL window  The interactive window is incorrectly parsing 'Congo, Dem Rep of the' into two pieces.  

COUNTRY_NA IN ('Angola', 'Congo', 'Dem Rep of the', 'Malawi', 'Tanzania', 'Zimbabwe', 'Mozambique', 'Congo', 'Dem Rep of the', 'Botswana', 'Namibia')

 These data are from the US Department of State. http://geonode.state.gov/layers/geonode%3AGlobal_LSIB_Polygons_Detailed

[Anonymous User]

Hi Brian, 

Does this include a comma in the name? I think this might be related to a bug we have logged.

BUG-000111347: The layer definition query with Add Clause and 'Include(s) the value(s)' on a text field with comma-separated single values is interpreted incorrectly and separates one value in the data into two separate values.

[AndySiegel]

I'm on 2.4.3 and also have this problem. I haven't upgraded to 2.5 yet, but does 2.5 provide the fix?

[Anonymous User]

Hi Andy, it doesn't look like this bug is supposed to be fixed in 2.5. There is a workaround, using delimiters other than commas.

[AndySiegel]

Hi Luba. Thanks for the response. What's an example of workaround using different delimiters, in the case mentioned by the OP? 

Expected result:

COUNTRY_NA IN ('Angola', 'Congo, Dem Rep of the', 'Malawi', 'Tanzania', 'Zimbabwe', 'Mozambique', 'Congo', 'Dem Rep of the', 'Botswana', 'Namibia')

Actual result:

COUNTRY_NA IN ('Angola', 'Congo', 'Dem Rep of the', 'Malawi', 'Tanzania', 'Zimbabwe', 'Mozambique', 'Congo', 'Dem Rep of the', 'Botswana', 'Namibia')

[Anonymous User]

Hi Andy, 

For example, instead of 'Congo, Dem of the' it would be 'Congo; Dem of the'-- or slashes. I haven't tested this out my end so I don't know what happens if the comma is removed completely and left with a space. 

[MarcoBoeringa]

Luba Hristova‌, do you know what is the status of this bug, how is it classified in the ESRI bug tracking system?

I ran into something similar back in the days in Pro 1.0. This is really a *major* issue that needs fixing a.s.a.p. Things like this are a real PITA when using Pro, and should have top priority fixing. This is core functionality that should not fail.

[Pål_Herman_Sund]

Hi Luba Hristova - I received a supportcase from a Norwegian client with the exact same problem but related to "Select by Attributes".

As Andy Siegel pointed out the expected SQL is not produced by the "expression builder" - but it is possible to make a perfect SQL query by switching to directly writing SQL syntax (or edit the invalid result from the "expression builder"). 

I will raise a supportcase to add one more vote for fixing the "expression builder". In my case I have a text-field "opphav" with (some of the) possible values:

• khm, kulturhistorisk museum, oslo
• sametinget, hovedkontor
• snåsa regionkontor, sametinget

Expression builder

Which gives 0 returns, due to the SQL being translated to:

opphav IN ('khm', 'kulturhistorisk museum', 'oslo', 'khm', 'kulturhistorisk museum', 'oslo', 'sametinget', 'hovedkontor', 'khm', 'kulturhistorisk museum', 'oslo', 'sametinget', 'hovedkontor', 'snåsa regionkontor', 'sametinget', 'khm', 'kulturhistorisk museum', 'oslo', 'snåsa regionkontor', 'sametinget', 'sametinget', 'hovedkontor')

But if I edit this SQL to (by switching to the SQL editor:

opphav IN ('khm, kulturhistorisk museum', 'sametinget, hovedkontor', 'snåsa regionkontor') I get the expected result

So no need to alter the data by replacing commas with other charachters, but indeed a need for making the expression builder produce correct SQL syntax.

[Pål_Herman_Sund]

Andy Siegel‌, Luba Hristova,‌ Marco, Brian

As this problem also affects "select by attributes" a raised a case last night and a new bug was accepted in minutes (thanks to Jacob Hartle for a prompt response).

Bug Number: BUG-000128909

Synopsis: In ArcGIS Pro, when using the Includes the value(s) or does not include the value(s) evaluators in the Select by Attributes tool, records fail to return if the field contains a comma.

Now it should be at least 3 bugs related to this problem in Pro, please fix

[DanPatterson_Retired]

BUG-000128909: When using 'includes the value(s)' or 'does not incl.. 

Strange, it says "not in current production plan"

but its 'duplicate' says Fixed in 2.6

BUG-000119957: In ArcGIS Pro, when using the Includes the value(s) ..