AnsweredAssumed Answered

Strange Subquery Behavior on Feature Service

Question asked by KenDinsmore on Mar 23, 2017

I have a feature service that includes about 100 tables. I can query these tables using subqueries because I have standardizedQueries set to false. In most cases, the subqueries work fine, but I am running into some problems with incorrect results in a couple of cases. Here is the database structure (simplified):

X
ObjectID - The SDE maintained object identifier
XField1 - a data field

 

Y
RID - The SDE maintained object identifier
XObjectID - a reference to the ObjectID in table X
ZObjectID - a reference to the ObjectID in table Z

 

Z
ObjectID - The SDE maintained object identifier
ZField1 - a data field

 

These tables are versioned with SQL access enabled so I have versioned views.
The data in the views looks like this (simplified):
X.ObjectID    X.XField1
10001            SomeValue

 

Y.RID    Y.XObjectID    Y.ZObjectID
23201    10001                40801

 

Z.ObjectID    Z.ZField1
40801            SomeOtherValue

 

I want to find all of the Zs that are related to a particular X through my many-to-many relationship Y.

In my feature service I can run queries with the following where clauses:
X: XField1 = 'SomeValue'
Y: XObjectID in (Select ObjectID from X_evw where XField1 = 'SomeValue')
Z: ObjectID in (40801)

If I run either of these two queries, I don't get any results:
Z: ObjectID in (Select ZObjectID from Y_evw Where XObjectID in (Select ObjectID from X_evw where XField1 = 'SomeValue'))
Z: ObjectID in (Select ZObjectID from Y_evw Where XObjectID in (10001))

 

However; In SQL Server, I can run any of these queries and get the results that I expect

Select * from X_evw where XField1 = 'SomeValue'
Select * from Y_evw Where XObjectID in (Select ObjectID from X_evw where XField1 = 'SomeValue')
Select * From Z_evw where ObjectID in (40801)
Select * From Z_evw where ObjectID in (Select ZObjectID from Y_evw Where XObjectID in (Select ObjectID from X_evw where XField1 = 'SomeValue'))
Select * From Z_evw where ObjectID in (Select ZObjectID from Y_evw Where XObjectID in (10001))

 

Notice that the where clauses are exactly the same and that I am using the versioned view in all of my queries. All of the data was entered using the feature service and all of the data is in the DEFAULT version.
One other key point, if I restart the feature service, or just wait around a while then re-run the queries all of my data shows up. That suggests that the site is caching the data. Caching for the service is set to "Dynamically from the data", but there is no spatial data in these tables so I don't think it should actually be caching for the feature service.

 

Here is my current configuration:
MS SQL Server 2014
SDE Geodatabase with database objects at 10.3
ArcGIS Server 10.3
Feature service published with ArcMap 10.3

 

Does anyone have any ideas about why the subqueries that go through the Y table would not work?

 

 

Outcomes