WFS SortBy only applied within Oracle pages #7463
Description
When using Oracle, SortBy is only applied to each page, it is not applied to all the data before paging. Is this deliberate? ie, if I submit a pull request to fix this is it likely to be accepted or rejected?
eg, in the following request note the startIndex and SortBy:
<GetFeature xmlns="http://www.opengis.net/wfs/2.0" service="WFS" version="2.0.0" startIndex="1000" count="1000" xsi:schemaLocation="http://www.opengis.net/wfs/2.0 http://schemas.opengis.net/wfs/2.0/wfs.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Query typeNames="wfs:Planning_Application_Polygons" srsName="EPSG:27700" xmlns:wfs="http://mapserver.gis.umn.edu/mapserver">
<Filter xmlns="http://www.opengis.net/fes/2.0">
<BBOX>
<ValueReference>SHAPE</ValueReference>
<Envelope xmlns="http://www.opengis.net/gml/3.2" srsName="EPSG:27700">
<lowerCorner>139987.2407923497 -3524.05645833332</lowerCorner>
<upperCorner>316170.54920765024 157942.01645833335</upperCorner>
</Envelope>
</BBOX>
</Filter>
<SortBy xmlns="">
<SortProperty>
<PropertyName>DATEMODIFIED</PropertyName>
<SortOrder>DESC</SortOrder>
</SortProperty>
</SortBy>
</Query>
</GetFeature>
This results in the following SQL, note that ROWNUM is included in the inner query:
SELECT * from (
SELECT atmp.*, ROWNUM rnum from (
SELECT "REFVAL", "KEYVAL", "DATEMODIFIED", "ADDRESS", "DESCRIPTION", "OBJECTID", rownum, SHAPE
FROM PA_V1_6_DCAPPLICATIONS_POLY
WHERE ISPAVISIBLE = 1 AND SDO_FILTER( SHAPE, MDSYS.SDO_GEOMETRY(2003, :srid, NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),:ordinates ),'querytype=window') = 'TRUE'
ORDER BY "DATEMODIFIED" DESC
) atmp
where ROWNUM<=2000
)
where rnum >=1001
What I would like the SQL to be is this, so that the ordering is done before the paging:
SELECT * from (
SELECT atmp.*, ROWNUM rnum from (
SELECT btmp.*, ROWNUM from (
SELECT "REFVAL", "KEYVAL", "DATEMODIFIED", "ADDRESS", "DESCRIPTION", "OBJECTID", SHAPE
FROM PA_V1_6_DCAPPLICATIONS_POLY
WHERE ISPAVISIBLE = 1 AND SDO_FILTER( SHAPE, MDSYS.SDO_GEOMETRY(2003, :srid, NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),:ordinates ),'querytype=window') = 'TRUE'
ORDER BY "DATEMODIFIED" DESC
) btmp
) atmp
where ROWNUM<=2000
)
where rnum >=1001