[DISCUSS] DB2 query with limit returns random order results

[Gagan Juneja <ga...@gmail.com>]

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

Re: [DISCUSS] DB2 query with limit returns random order results

[Kasper Sørensen <i....@gmail.com>]

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 <ga...@gmail.com>

> 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
>