Pro 3.5 BUG-000177183 Select by Attributes tool query returns partial results for layers sourced from SQL Server

[RoseAmy]

Hello,

I currently have ArcGIS Pro 3.5.1 on Windows and discovered this bug the hard way when I was doing selections and then field calculates.  I want to let more people know about it and also to ask ESRI if they knew of a timeline when this might get fixed.  The bug page says its now in the "Product Plan." Does that mean it may just be a few months away? Or could it be longer?  I am really enjoying the new items in 3.5 but will have to revert to 3.4 if its going to take too long.  I also do deep learning so the uninstall and installs take quite awhile.

This is the bug page: https://support.esri.com/en-us/bug/the-select-by-attributes-tool-query-returns-partial-res-bug-00017...

Thank you for looking!

Amy

[DanPatterson]

To summarize what will be made public from that page, is the info link and the workaround.  

It should be noted that the more people that experience the same problem... subscribe to the bug, is recommended... the faster it will be addressed.

The issue has been added to the development team's backlog. The goal for issues with this status is to address them in a near-term release (next release or two). Check the issue's Additional Information or Alternate Solution section to see if there is helpful information until it can be fully addressed.

Workaround
Use a Definition Query to display the records and select all records from the attribute table.
Downgrade to ArcGIS Pro version 3.4.3 or earlier.

This might require more than a small fix/patch which would put it further in time.  Good luck

[AyanPalit]

@RoseAmy Thanks for your good review on Pro 3.5 - there are many exciting features.

Sorry for the issue on BUG-000177183. Did you try the suggested workaround:

• Use the Query Expression, that you would use for Select by Attributes tool in the Definition Query and apply the filter to the SQL Server layer.
• select all records from the attribute table

I realize this is more steps but, better option than downgrading ArcGIS Pro on account of this BUG.

[RoseAmy]

Hello Ayan,

Thank you so much for responding.  I was curious when the work around said to select all records using the definition query.  I thought it might be a mistake since normally you would use a definition query to select a subset. Would I just need to do something like SHAPE is not null?  Then would I be able to work in Pro and do select by attributes without having the bug? 

Or, does the work around mean to use the definition query as the way to make your subset selections and not use the select by attributes in the Pro interface?

Amy

[AyanPalit · Accepted solution]

@RoseAmy 

let's use an example:

1. I have a cities layer in SQL Server with city names and state names. 
2. I want to use Select by Attributes tool with Query Expression: 'where STATE = TX'
3. I use Select by Attributes and get partial results for layers due to the BUG

Workaround

• Use the Query Expression: 'where STATE = TX' in the Definition Query and apply the filter to the SQL Server layer.
• select all records from the attribute table
• I get all resulted selected for 'where STATE = TX'; my original intent

[RoseAmy]

This makes sense! I will check it out and accept as a work around solution.  I may still revert to 3.4 though since it's just second nature for me to use select by attribute.  The last thing I need is to mess up a lot of data. 

Amy

[CalvinHarmin]

Do we know if this affects all versions of SQL Server? And only SQL Server and not other db’s like postgres?

[RoseAmy]

Hello,

I don't know the complete answer but the bug showed up for me using SQL Server2019.

Hope this helps,

Amy