Oracle paging fails with ORA-01445

[bartvde]

A DATA statement with NONE, e.g.:

DATA "GEOMETRY FROM (SELECT GID, CONTRACTNR, VERVALLEN, WEG, BAAN, STROOK, HM_VANAF, HM_TOT, DEKLAAG, AANTAL_LAGEN, BONUS, MALUS, BONUSVERVALLEN, EINDDAT, INGANGSDATUM, MEETMOMENT1, MEETMOMENT2, OPMERKING, DISTRICTNAAM, DISTRICT, GEOMETRY FROM GARANTIEBANK.GBA_V_VERHARDING2) USING UNIQUE GID NONE"

will result in the following ORA error when doing a WFS GetFeature request with startindex:

msWFSGetFeature(): WFS server error. ms_error->code not found msOracleSpatialLayerWhichShapes(): OracleSpatial error. Error: ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

. Query statement: SELECT * from (SELECT atmp.*, ROWNUM rnum from (SELECT GID, CONTRACTNR, VERVALLEN, WEG, BAAN, STROOK, HM_VANAF, HM_TOT, DEKLAAG, AANTAL_LAGEN, BONUS, MALUS, BONUSVERVALLEN, EINDDAT, INGANGSDATUM, MEETMOMENT1, MEETMOMENT2, OPMERKING, DISTRICTNAAM, DISTRICT, rownum, GEOMETRY FROM (SELECT GID, CONTRACTNR, VERVALLEN, WEG, BAAN, STROOK, HM_VANAF, HM_TOT, DEKLAAG, AANTAL_LAGEN, BONUS, MALUS, BONUSVERVALLEN, EINDDAT, INGANGSDATUM, MEETMOMENT1, MEETMOMENT2, OPMERKING, DISTRICTNAAM, DISTRICT, GEOMETRY FROM GARANTIEBANK.GBA_V_VERHARDING2) order by rowid) atmp where ROWNUM<=9) where rnum >=5 . Check your data statement.

msQueryByRect(): Search returned no results. No matching record(s) found.

​http://ontwikkel.intranet.rijkswaterstaat.nl/services/geoservices/60/project/garantiebank/garantiebank?service=WFS&request=GetFeature&maxfeatures=5&typename=GARBANK001&version=1.1.0&startindex=5

[bartvde]

I am closing this one, this is more to do with Oracle than with Mapserver.

[bartvde]

Okay after a bit more research I am reopening.

Seems that the "order by rowid" is causing the issue, and I am not really sure why Mapserver needs to include this in a paging query? Assefa, do you remember why this was necessary?

[assefa]

Hi Bart,

From what I remember is that the return set of results is not always guaranteed to be in the same order for the same query. Ordering by rowid would make sure the results are always ordered the same way, thus allowing correct pagination.

I have added Mike in cc to see if this makes sense.

[msmitherdc]

Correct. Query order is not consistent unless there is an order by in the query.