orderClause in Datasource overridden by ROW_NUMBER() OVER(PK) paging clause

[tece321]

SmartClient Version: v9.1p_2014-09-17/PowerEdition Deployment (built 2014-09-17)

I have seen several similar posts in the Forum but this sure seems like a bug.

I have a DataSource with this fetch operation definition:

<operationBinding operationType="fetch">
<selectClause>
				Authorized,
				CT_on_change,
				CT_on_create,
				UREQ.FullName as RequestedBy,
				WaiverRun.WCorner,
				Path_Group,
				Path_GroupType,
				StartPt_Chip_Inst_Path,
				EndPt_Chip_Inst_Path,
				StartPoint,
				EndPoint,
				STA_SW_State,
				WaiverRun.STA_WaiverRunState,
				Comment,
				WaiverRun.Waiver_Comment,
				idSetup_Waiver
</selectClause>
<tableClause>
STA_Waiver_Run as WaiverRun
JOIN STA_Setup_Waiver ON STA_Setup_Waiver.FK_STA_Waiver_Run = 	WaiverRun.PK_STA_Waiver_Run
JOIN Users as UREQ on UREQ.PK_Users_Userid = STA_Setup_Waiver.Requestor
</tableClause>
<orderClause>
CASE WHEN LOWER(STA_SW_State) = 'dangling' THEN '1'
	 WHEN LOWER(STA_SW_State) = 'rejected' THEN '2'
	 WHEN LOWER(STA_SW_State) = 'invalid'  THEN '3'
        WHEN LOWER(STA_SW_State) = 'pending'  THEN '4'
    ELSE                                       '5'  END   ASC			
</orderClause>
</operationBinding>

From the debug log this produces the correct SELECT statement on a
grid.fetchdata() call

<operationBinding operationType="fetch">
			<selectClause>
				Authorized,
				CT_on_change,
				CT_on_create,
				UREQ.FullName as RequestedBy,
				WaiverRun.WCorner,
				Path_Group,
				Path_GroupType,
				StartPt_Chip_Inst_Path,
				EndPt_Chip_Inst_Path,
				StartPoint,
				EndPoint,
				STA_SW_State,
				WaiverRun.STA_WaiverRunState,
				Comment,
				WaiverRun.Waiver_Comment,
				idSetup_Waiver
			</selectClause>
			<tableClause>
	STA_Waiver_Run as WaiverRun
JOIN STA_Setup_Waiver ON STA_Setup_Waiver.FK_STA_Waiver_Run = 	WaiverRun.PK_STA_Waiver_Run
JOIN Users as UREQ on UREQ.PK_Users_Userid = STA_Setup_Waiver.Requestor
</tableClause>
<orderClause>
		CASE WHEN LOWER(STA_SW_State) = 'dangling' THEN '1'
			 WHEN LOWER(STA_SW_State) = 'rejected' THEN '2'
		         WHEN LOWER(STA_SW_State) = 'invalid'  THEN '3'
		          WHEN LOWER(STA_SW_State) = 'pending'  THEN '4'
		   ELSE                                       '5'  END   ASC			
</orderClause>
</operationBinding>

However in the next step it is fetching by rows 1 to 75, and introducing a new order clause, ORDER by idSetup_Waiver, the PK of the base table. This is the windowing / paging function.

So it does this:

=== 2015-01-30 19:20:49,553 [0-40] DEBUG SQLDataSource - [builtinApplication.BCG_STA_Setup_Waiver_fetch] SQL windowed select rows 0->75, result size 75. Query: SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY idSetup_Waiver) AS rowID FROM (SELECT TOP 100 PERCENT  
                     Authorized,
                     CT_on_change,
                     CT_on_create,
                      UREQ.FullName as RequestedBy,
                      WaiverRun.WCorner,
                      Path_Group,
                      Path_GroupType,
                      StartPt_Chip_Inst_Path,
                      EndPt_Chip_Inst_Path,
                      StartPoint,
                      EndPoint,
                      STA_SW_State,
                      WaiverRun.STA_WaiverRunState,
                      Comment,
                      WaiverRun.Waiver_Comment,
                      idSetup_Waiver
FROM 
      STA_Waiver_Run as WaiverRun
      JOIN STA_Setup_Waiver ON STA_Setup_Waiver.FK_STA_Waiver_Run =             WaiverRun.PK_STA_Waiver_Run
      JOIN Users as UREQ on UREQ.PK_Users_Userid = STA_Setup_Waiver.Requestor
 WHERE (WaiverRun.FK_BCG_UIWaiverMaster=78)
 ORDER BY 
     CASE WHEN LOWER(STA_SW_State) = 'dangling' THEN '1'
            WHEN LOWER(STA_SW_State) = 'rejected' THEN '2'
             WHEN LOWER(STA_SW_State) = 'invalid'  THEN '3'
             WHEN LOWER(STA_SW_State) = 'pending'  THEN '4'
              ELSE                                       '5'  END   ASC                                 
    ) x) y WHERE y.rowID BETWEEN 1 AND 75

So the ROW_NUMBER() of paging is overriding the complicated ORDER BY clause of the DataSource.

I know there is a possible work around by turning off paging. But of course that would impact fetch performance for the user.

I can't see how using a SortSpecifier object I can define a client side ORDER specification using my complicated clause. So that doesn't seem like a solution when using the setSortBy on a DSRequest.

Is this a bug?

[Isomorphic]

No, not a bug, this is a documented limitation stemming from the fact that Oracle implements such a hokey mechanism for selecting specific row ranges; there's no way to safely modify arbitrary queries so that you can add paging to them.

You've already noted that you can get around this by not using sqlPaging:"sqlLimit". Another approach is to turn off paging entirely but take over more of the SQL generation for this particular query, so that you can implement the row range selection behavior yourself, via using Velocity expressions like $dsRequest.startRow.

[tece321]

No, not a bug, this is a documented limitation stemming from the fact that Oracle implements such a hokey mechanism for selecting specific row ranges; there's no way to safely modify arbitrary queries so that you can add paging to them.

You've already noted that you can get around this by not using sqlPaging:"sqlLimit". Another approach is to turn off paging entirely but take over more of the SQL generation for this particular query, so that you can implement the row range selection behavior yourself, via using Velocity expressions like $dsRequest.startRow.

Thanks for reply. Understood.

Is this documented limitation in your documentation, and if so where?
I need to reference the document for other users.

[Isomorphic]

Actually, looking more closely at this, you aren't using <customSQL> so our earlier comments don't apply. It turns out this really is a bug - a regression introduced by an unrelated change back in July 2014. This has now been fixed - please try your use case with tomorrow's 4.1p nightly build (the one dated Feb 5th) and let us know if you still see problems.

Regards,
Isomorphic Support

[tece321]

Actually, looking more closely at this, you aren't using <customSQL> so our earlier comments don't apply. It turns out this really is a bug - a regression introduced by an unrelated change back in July 2014. This has now been fixed - please try your use case with tomorrow's 4.1p nightly build (the one dated Feb 5th) and let us know if you still see problems.

Regards,
Isomorphic Support

Thanks. Finally got back to this issue and I can confirm that the nightly build of 02-07-2015 fixes this problem.

The complex order clause of the fetch is replicated in the paging ORDER BY clause.