Select by location giving unexpected results when selecting from a query layer

[LarryMcEvoy]

Hi I am trying to replicate a workflow that I had in ArcMAP 10.2.2 using ArcGIS Pro 2.3.3, I have a table with native SQL geometry stored in SQL server 2008 10.0.6241.0. I load this table up as a query layer I then need to select all the polygons from this query layer contained within a geometry stored in a SDE versioned feature class (located on the same SQL server with the geometry also stored in native SQL server format). This works perfectly in arcmap 10.2.2 using the select by location tool. When I try the same process in PRO the resulting selection appears to be random with some polygons 10's of km's outside of the selecting polygon. I can apply a definition query which visually returns the right results using this SQL Syntax  

                 ref in (select rtrim(ref)from databasename.databaseuser.querylayertablename a

                 inner join (select shape from databasename.databaseuser.selectingtablemultiversionedview_EVW                  where id=17) b

                 on a.geom.STIntersects(b.Shape)=1)

(this also works in SQL Server Management Studio) unfortunately although the visual representation is correct, the attribute table fails to open as it errors with a Failed to parse the where clause error.

Anybody else experience a similar issue or aware of a fix?

Ta

Larry

[MichaelVolz]

Do you have wire shark to see how the actual query is being sent to SQL Server?  From my own experience with query layers in both ArcMap and Pro, the where clause was being dropped when executed which was proven out with wire shark.  There was no resolution in my case, so this architecture did not work for my use case.

[LarryMcEvoy]

Thanks Michael, Wire shark does show up some questionable activity in the sql queries, it does seem to be passing through the where clause in my case but is running the in sub select first then nesting my query inside a new select with the results of the sub select as the where clause.

[MichaelVolz]

Have you tried this workflow in a newer version of ArcMap than 10.2.2 to see if you also the same unexpected results there?

[George_Thompson]

Are you also connecting ArcGIS Pro to the SQL 2008 database?

If so, the mininmum SQL Server version is 2014 SP3; https://pro.arcgis.com/en/pro-app/help/data/databases/database-requirements-sqlserver.htm. That could be the cause of some of the behavior.

[LarryMcEvoy]

Thanks George, that is whats probably causing the issue with the select by location. We are waiting on our IT section to migrate us to 2016 and I was just trying to get ahead of the curve with workflows for staff. I will run up a sql server 2014 express editon for the query layer and move the selecting layer to a FGDB to test the select by location functionality.  

[LarryMcEvoy]

For Info just ran a test using a query layer stored on SQL server Express 2014 SP3 and the select layer stored in a file Geo - database and the same issue occurs with the select by location tool. 

[LaurentP]

The issue is still occurring with SQL Server 2019 using ArcGIS Pro 2.8 service pack 3.