Possible Bug with Query Layer Attribute Table in Pro?

[Anonymous User]

I created a query layer in ArcGIS Pro from a feature hosted in our SDE that looks like so:

select distinct parcel_id, primary_address,owner, shape from parcel.parcels_master_mv where (UPPER(owner) LIKE 
'MANATEE COUNTY%'
 OR owner LIKE 'MANATEE, COUNTY%'
 OR owner LIKE 'COUNTY OF MANATEE%'
 OR owner LIKE '%CEMETERY%'
 OR owner LIKE '%CEMATERY%'
 OR owner LIKE 'SIXA INV%'
 OR owner LIKE 'TIITF%' )
 AND (LUC IN ('7600','8086','8600','9400','9500'))
 AND
 ( UPPER(owner) NOT LIKE '%MOSQUITO%'
 AND UPPER(owner) NOT LIKE '%PORT%'
 AND UPPER(owner) NOT LIKE '%AUTHORITY%'
 AND UPPER(owner) NOT LIKE '%HOUSING%'
 AND UPPER(owner) NOT LIKE '%HABITAT%'
 AND UPPER(owner) NOT LIKE '%AUDOBON%'
 AND UPPER(owner) NOT LIKE '%BANK%'
 AND UPPER(owner) NOT LIKE '%CHURCH%'
 AND UPPER(owner) NOT LIKE '%FAMILY%'
 AND UPPER(owner) NOT LIKE '%FARM%')
 order by parcel_id desc‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Why I believe there's a bug is because when I open the attribute table and start scrolling, I get an error a couple rows in that says "Failed to retrieve a page of rows." After I select okay, the attribute table crashes - and it does so at the same point every time I reopen the table.

When I remove the "order by" command, I can scroll freely though the attribute table without error.

It seems to be very similar to this bug that was reported resolved with Pro 2.3: What does "Failed to retrieve a page of rows" imply?‌

[DanPatterson_Retired]

Lea, did you follow the related and buried thread?

https://community.esri.com/thread/222727-error-opening-table-in-pro-failed-to-retrieve-a-page-of-row... it has more recent posts

[Anonymous User]

Hi Dan - I've defined the primary key as parcel_id (which does have duplicate values in the original dataset), but I made sure to call distinct values in the query.

Could Pro somehow be picking up on duplicates in the background when the query runs?

[DanPatterson_Retired]

Lea, don't use query layers, but it first Note in this link Choose a unique identifier field—Query layers | ArcGIS Desktop might be an issue so I don't understand how distinct would handle this

[JoshuaBixby · Accepted solution]

My guess is that it is tied to your use of DISTINCT and ORDER BY.  Since query layers are dynamic, the SQL you use to define the layer is constantly being called to retrieve records.  Mixing DISTINCT and ORDER BY is messy because both involve sorting, but typically each sorts using a different mechanism.  My guess is that the records being returned by the query are changing during one of the subsequent calls of the SQL statement, and that having different records returned is causing an issue with some kind of internal processing by ArcGIS.

I recommend you read How SQL DISTINCT and ORDER BY are Related – Java, SQL and jOOQ.  and consider restructuring your SQL.

[Robert_LeClair]

Lea - in some of the Support Services cases I've been reviewing it mentions the issue was resolved "by changing query to exclude duplicates on the unique id, enhancement request also logged.  ENH-000119340:  Customer requests behavior in Pro be changed to be the same as ArcMap when adding a query layer with duplicate OID rows."

[YungKaiChin]

If all else fails, try create a new id field and populate with row_number().