ArcObjects Warning: Nested Queries Break ArcSDE 10

[MarkCederholm]

End of the year reporting is here, and our GIS reports are crap since we migrated our geodatabase to ArcSDE 10 SP3/Oracle 11g.  I finally pinpointed the problem:  nested queries using ArcObjects corrupt the outermost feature cursor so that it repeatedly loops through the first 1900-2000 features.  This was, I most emphatically add, not an issue in ArcGIS 9.

I've submitted this incident to tech support.  I'm posting for two reasons:

1) To encourage others to look for the problem.  The simplest workaround at this point is to run your reports against a file geodatabase copy of your enterprise GDB.

2) To encourage the ESRI geodatabase team to elevate this to the highest levels. 

In an ideal world this would be an isolated incident, and there's simply something wrong with our geodatabase migration that can easily be fixed.  At worst, this could be an egg in the face of GIS departments worldwide.

[MarkCederholm]

It does seem that ST_GEOMETRY is the culprit.  I migrated my test data to SDELOB and the test succeeded, then migrated it back to ST_GEOMETRY and the problem reappeared.

This morning I thought that I was getting SDELOB to fail with production reports, but it turns out I was not pointing them to the test GDB.  The SDELOB storage is working fine so far, so unless I can get my tests to fail I'm still going to recommend migrating our production data away from ST_GEOMETRY.

[MarkCederholm]

#NIM076610

While the bug report specifically refers to a console application, I have determined that it also occurs in ArcMap.

[ForrestJones]

Hi Mark,

Does it occur with the out of the box tools in ArcMap or does it require some custom code to reproduce?

Thanks.

[MarkCederholm]

I saw the problem with custom code.  I'm not aware of any out-of-the-box tool in ArcMap that goes through the exact motions necessary to cause the problem: a spatial query must be made against a feature class, and for every feature returned, its shape is used to spatially query another feature class.  If both feature classes use ST_GEOMETRY storage, the error occurs; if both use SDELOB, it does not.  I have not tried a mixed case.

[TomBrown]

Mark,

Thank you for submitting the issue to support. We've identified the problem and are working on a QFE for you and this issue will be addressed in ArcGIS 10.1 and future ArcGIS 10.0 Service Packs.

The problem will only be encountered when the outer query will return a result set > 2000 objects and a second query has been executed (where its access path is via the spatial index). What is happening is the internal rid list (rowid list the domain index provides during the query execution) reaches 2000, which is the magical number in which it then hands over the set to Oracle in order to allow the query to continue processing. Once the consummer fetches the 2000 rows, the next 2000 or remaining rids are provided to Oracle and so on... As the cursor is waiting to return the next batch of rids to Oracle, the next query is executed and overwrites the first cursors rid list.

Unfortunately it was a critical bug as you discovered, but very unique and would only be exposed based upon your workflow of using nested queries and the first query fetching > 2000 rows.

Thank you  and if you have further questions or needs please don't hesitate to contact support or leverage the forums.

Thomas Brown
Geodatabase team