Did a little googling on the subject and I think it sounds correct to add the ORDER BY parameter to the OVER clause. My only concern is with the "id" you mention. The fix should be applied in a way where we either always select something sensible for a particular table, or (preferably IMO) we find the ORDER BY clause of the MetaModel query and apply that to the OVER clause, while removing it from the subquery.
Please make sure to check DB2QueryRewriterTest (methods testRewriteFirstRow and testRewriteFirstRowAndMaxRows). 2014/1/7 Gagan Juneja <[email protected]> > I am using metamodel with DB2 database. I have one query where I want > to fetch records out of database with firstRow and MaxRow clauses. > What I found is this query doesn't work as expected and returns > duplicate records in different pages/batches. > > I looked into the DB2QueryRewriter.java class and found that we are > using ROW_NUMBER() OVER() function in inner query to define order but > this is not working correctly, If I change this function call to > include order by clause on table Id field (ROW_NUMBER() OVER(order by > id)every thing works perfectly fine. > > Please provide your valuable inputs on how to fix this for DB2 database. > > We are using 3.4.7 version of metatmodel. > > Regards, > Gagan >
