Incorrect or no results returned when using st_distance

Description

With ArcGIS 9.2 Service Pack 2 installed, when the st_distance operator is applied as a filter in a Where clause, SQL statements may return incorrect or no results.

Cause

ArcGIS 9.2 Service Pack 2 introduced the binding between relational operators, such as st_intersects, st_contains, st_within, and the st_spatial_index domain index. The binding associates the operators with the spatial index, improving performance.

The st_distance operator was mistakenly included in the binding to the spatial index. As a result of this incorrect binding, SQL statements containing the st_distance operator in the Where clause can return incorrect results or no results.

For example, the SQL statement below is attempting to discover all buildings that are less than 5000 feet from the input fire polygon feature.

SQL> SELECT b.name, b.address 
     FROM buildings b, fire_perimeter f
     WHERE st_distance(f.shape,b.shape) < 5000;

no rows selected

SQL> SELECT b.name NAME, b.address ADDRESS, st_distance(f.shape,b.shape) DISTANCE  
     FROM buildings b, fire_perimeter f
     WHERE f.event_name = 'Sand Canyon';

Workaround

SQL> ALTER INDEXTYPE sde.st_spatial_index DROP st_distance(st_geometry,st_geometry);