How to have order by inside a query with pageable

Question

I am using Spring batch with hibernate and JPA, I am trying to implement partitioning.

My partition simpler return from and to, which will slice the data into 10 chunks. Example from is 1 and to is 10, read row between 1 and 10, if it is 20-30, then read row between 20 and 30.

I have a simple Query.

@Query(value="select * from (
select t1.*, rownum as rowCol from
(select * from {h-scheme}Data where processed=:flag order by col1, col2 desc) t1 where rownum < :toRow) 
where rowCol >=:fromRow",  nativeQuery=true)
public Page<Data> findByProcessedFromAndTo(@Param("flag")char flag, (@Param("fromRow")long from, (@Param("toRow")long to, Pageable page);

I am doing a manual pagination first based on from and to, then I will apply regular pageable to process the data. For pageable, it has pageSize of 10, pageNumber keeps increasing, and Sort is by col1 asc and col2 desc.

When I run this batch process, somehow hibernate has trouble covert in to oracle sql,

This is what hibernate has:

select * from(
select * from (
select t1.*, rownum as rowCol from
(select * from Data where processed=? order by col1 asc, col2 desc) t1 
where rownum < ?) where rowCol >=?, col3 asc, col4 desc) where rownum <?

Why after the fromRow params, it add a comma instead? it supposes to be order by.

I tried to remove the sort in pageable, then it will complains about missing right parenthesis because of unknown parameter toRow. The sql generated by hibernate is basically broken. Why hibernate has trouble to convert above query with pageable?

However, if I remove order by col1, col2 desc, both add/remove sort from pageable works.

So how to add a custom order by inside a query with pageable? I could'n find any solutions