Database Software

Hi,

I have the following problem. The last query below returns the bad number of rows and I suspect some RDBMS bug behinde this, but am not really shure. Could some one please have a look?

-- Preparation steps:

-- Create 'Varos' Table

CREATE TABLE VAROS (
GMIPRIMARYKEY NUMBER (10) NOT NULL,
TNEV VARCHAR2 (50),
NEV VARCHAR2 (50),
GEOMETRY MDSYS.SDO_GEOMETRY,
PRIMARY KEY ( GMIPRIMARYKEY ));


-- Insert 5 records
-- 2 have no name (nev attribute contains 'nincs neve')
-- 3 have meaningful name
insert into varos values (615484,'Kisszentmarton','58131',MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(18.04725,45.788384,18.04698,45.786578)));
insert into varos values (617351,'Czn','nincs neve',MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(18.060483,45.785141,18.060615,45.784949,18.06079,45.783938,18.061508,45.782226,18.061641,45.781539,18.061686,45.780696,18.061677,45.780531)));
insert into varos values (617352,'Czn','nincs neve',MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(18.061677,45.780531,18.061638,45.779749,18.061463,45.778458,18.061103,45.777196,18.060708,45.776133,18.060355,45.775182)));
insert into varos values (622732,'Vejti','Tancsics Mihaly utca',MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(17.964469,45.809845,17.964149,45.809448,17.9641,45.809356,17.964009,45.809186,17.964004,45.808933,17.964007,45.808886,17.964169,45.808335,17.964781,45.807061,17.965233,45.806119,17.965492,45.805637,17.965682,45.805283,17.965802,45.805063,17.965932,45.804825,17.966221,45.804298)));
insert into varos values (629498,'Pisks','5821',MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(17.912804,45.80585,17.913105,45.805645,17.922503,45.803835,17.935384,45.803584)));

-- Insert metadata record
insert into USER_SDO_GEOM_METADATA values('VAROS','GEOMETRY',MDSYS.SDO_DIM_ARRAY( MDSYS.SDO_DIM_ELEMENT('Lon',16,23,0.00000001),MDSYS.SDO_DIM_ELEMENT('Lat',45.5,48.8,0.00000001)),NULL);

-- Commit
commit;

-- Create spatial index
CREATE INDEX VAROS_INDEX ON VAROS(GEOMETRY) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS('SDO_LEVEL=10 SDO_NUMTILES=8');

-- Test steps:

-- Select records with not 'nincs neve'

select gmiprimarykey,tnev,nev
from varos
where nev <> 'nincs neve';

-- It fetches 3 records
-- 615484,Kisszentmarton,58131
-- 622732,Vejti,Tancsics Mihaly utca
-- 629498,Pisks,5821
-- Right.

-- Embed the above select statement as a subselect to a nearest-neighbour spatial query.
-- It is requested to return the 3 nearest records ('sdo_num_res=3').

select gmiprimarykey,tnev,nev
from ( select gmiprimarykey,tnev,nev,geometry
from varos
where nev <> 'nincs neve'
) y
where mdsys.SDO_NN(y.geometry,mdsys.sdo_geometry(2001,NULL,mdsys.sdo_point_type(18,45.5,0),NULL,NULL),'sdo_num_res=3')='TRUE';

-- It is expected to return 3 records (the same ones as above), but it returns only 1 single record
-- 615484,Kisszentmarton,58131
-- Wrong.

It seems that Oracle executes the neaest neighbour spatial query BEFORE evaluating the inline view.

Regards,

Tamas Szecsy
tszecsy@geometria.hu