Possible Bug with Query Layer Attribute Table in Pro?

600
6
Jump to solution
03-29-2019 12:01 PM
LeaHarper
New Contributor II

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?

0 Kudos
1 Solution

Accepted Solutions
JoshuaBixby
MVP Esteemed Contributor

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.

View solution in original post

6 Replies
DanPatterson_Retired
MVP Esteemed Contributor

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

0 Kudos
LeaHarper
New Contributor II

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?

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

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

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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.

View solution in original post

Robert_LeClair
Esri Frequent Contributor

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
Occasional Contributor

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