Spatial Query Returns Duplicate Rows W/Event 54670 Set, Range Partitioning & Local Spatial Index (Doc ID 1322761.1)

Last updated on MAY 17, 2011

Applies to:

Oracle Spatial - Version: 11.2.0.2 to 11.2.0.2 - Release: 11.2 to 11.2
Information in this document applies to any platform.

Symptoms

With event 54670 set (as a workaround for <Bug 10407268> "SDO_ANYINTERACT QUERY ON PARTITIONED TABLE RETURNS ROWS FROM 1ST PARTITION ONLY") and a local Spatial index, this query returns the expected 1 row:

SQL> SELECT a.a_id
  2  FROM   a
  3  WHERE  a_id = 2 -- works - retrieves 1 row as expected
  4  AND    a.a_id IN
  5           (SELECT  t1.a_id
  6              FROM  test_sdo t1
  7              WHERE time_stamp BETWEEN
  8                      to_timestamp('2009-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
  9                    AND
 10                      to_timestamp('2011-12-31 00:00:00','yyyy-mm-dd hh24:mi:ss')
 11                AND SDO_ANYINTERACT
 12                      (t1.geo_location,
 13                       SDO_GEOMETRY
 14                         (2003,
 15                          8307,
 16                          NULL,
 17                          SDO_ELEM_INFO_ARRAY(1,1003,3),
 18                          SDO_ORDINATE_ARRAY(-77.29255,36.0, -75.28856,40)
 19                         )
 20                      ) = 'TRUE'
 21           );

A_ID
----------
2

but this query returns 2 rows when only 1 row is expected:

SQL> SELECT  a.a_id
  2    FROM  a
  3    WHERE a_id >= 2 -- retrieves 2 rows for ID 2: duplicates - this is the problem
  4    AND   a.a_id IN
  5            (SELECT  t1.a_id
  6               FROM  test_sdo t1
  7               WHERE time_stamp BETWEEN
  8                       to_timestamp('2009-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
  9                     AND
 10                       to_timestamp('2011-12-31 00:00:00','yyyy-mm-dd hh24:mi:ss')
 11               AND   SDO_ANYINTERACT
 12                       (t1.geo_location,
 13                        SDO_GEOMETRY
 14                          (2003,
 15                           8307,
 16                           NULL,
 17                           SDO_ELEM_INFO_ARRAY(1,1003,3),
 18                           SDO_ORDINATE_ARRAY(-77.29255,36.0, -75.28856,40)
 19                          )
 20                       ) = 'TRUE'
 21            );

A_ID
----------
2
2

Note: This problem does not occur without event 54670 set nor if the Spatial index is created without the 'local' keyword.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms