Hibernate pagination generated script execution return incorrect data.
I found this when I have retrieved data from a view. I have created table, data, view [generation script|^mytable.sql] for you to reproduce this bug.
According to hibernate sources *getLimitString* method from *org.hibernate.dialect.Oracle9iDialect*
Lets first select all rows from view:
{code:sql}
select * from v_mytable_last order by status desc
{code}
Result:
||RowNumber||ID||VERSION||CAPTION||STATUS||
|1|1 |7 |t40 |2|
|2|16 |1 |t47 |2|
|3|11 |2 |t43 |2|
|4|6 |4 |t31 |2|
|5|15 |1 |t46 |2|
|6|2 |6 |t39 |2|
|7|14 |1 |t45 |2|
|8|4 |5 |t38 |2|
|9|8 |2 |t29 |2|
|10|17 |1 |t48 |2|
|11|3 |3 |t21 |2|
|12|7 |2 |t23 |2|
|13|9 |2 |t30 |2|
|14|10 |3 |t42 |2|
|15|12 |1 |t41 |2|
|16|5 |6 |t37 |1|
|17|13 |1 |t44 |1|
After that is we are using hibernate pagination for first page then we have an appended code at first and last line of request:
{code:sql}
select * from (
select * from v_mytable_last order by status desc
) where rownum <= 5
{code}
Result:
||RowNumber||ID||VERSION||CAPTION||STATUS||
|1|1 |7 |t40 |2|
|2|14 |1 |t45 |2|
|3|2 |6 |t39 |2|
|4|6 |4 |t31 |2|
|5|11 |2 |t43 |2|
And for second page we are having offset so hibernate execute:
{code:sql}
select * from ( select row_.*, rownum rownum_ from (
select * from v_mytable_last order by status desc
) row_ where rownum <= 10) where rownum_ > 5
{code}
Result:
||RowNumber||ID||VERSION||CAPTION||STATUS||ROWNUM_||
|1 |4 |5 |t38 |2 |6 |
|2 |14 |1 |t45 |2 |7 |
|3 |2 |6 |t39 |2 |8 |
|4 |6 |4 |t31 |2 |9 |
|5 |11 |2 |t43 |2 |10 |
You can see for the second page ROWNUM_ seem to be correct but the data is *not*.
I was wonder to find this bug. I found it reproducible only for my view, not for table.
To solve this issue I has to override dialect and use my own implementation:
{code:groovy}
final StringBuilder pagingSelect = new StringBuilder( sql.length() + 100 )
pagingSelect.append( "select * from ( select row_.*, rownum rownum_ from ( " )
pagingSelect.append( sql )
if (hasOffset) {
pagingSelect.append( " ) row_ ) where rownum_ <= ? and rownum_ > ? " )
}
else {
pagingSelect.append( " ) row_ ) where rownum_ <= ?" )
}
{code}
This code is working correct for views as well as for tables. |
|
