English

Bug: Incorrect query results when using st_disjoint in Oracle when the access path is the spatial index

Description

It is possible to receive incorrect results when executing a SQL statement that uses the sde.st_disjoint operator.

For example, the following query should return one row (the street that is disjoint from the input lake but its shape's envelope intersects the specified input envelope), but no rows are returned in the result set.

Code:
SQL> SELECT roads.street_name
2 FROM roads, lakes
3 WHERE lakes.name = 'Jenks Lake'
4 AND sde.st_disjoint(roads.shape, lakes.shape) = 1
5 AND sde.st_envintersects(roads.shape,-114.4,32.7,-113.6,33.4) = 1;

no rows selected

Cause

Oracle's optimizer chooses to use the sde.st_spatial_index as the access path instead of applying the operator as a filter.

In the example using st_disjoint with the roads and lakes table, the Oracle optimizer selected the spatial index as the access path using the lake (where lakes.name = 'Jenks Lake') as the input geometry to search the roads spatial index. Since no rows were returned from the roads spatial index, there were no candidate rows to meet the st_envintersects operator.

If the optimizer applies the st_disjoint operator as a filter in the SQL statement, then every road's shape envelope that intersects the input envelope in st_envintersects would be passed as input geometries to the st_disjoint operator, and therefore return the correct result.

Workaround

Disassociate the sde.st_disjoint operator with the st_spatial_index domain index.

Execute the following command as the SDE user in SQL*Plus:

Code:
ALTER INDEXTYPE sde.st_spatial_index
2 DROP st_disjoint (sde.st_geometry, sde.st_geometry);