Improper SQL is generated for Take (pagination) for dialects which have UseMaxForLimit = true (like Oracle)
Description
QueryTakeParameterSpecification has a flaw in Bind because it does not take into account the absolute limit for certain dialects which have the flag UseMaxForLimit = true. The generated SQL does not return any results when executed for a query with Take(10).Skip(10) for example.
select c1, c2 from ( select row_.*, rownum rownum_ from ( SELECT c1,c2 FROM t1 WHERE this_.Order_Date >= to_date('10/08/2011 21:00:00', 'DD/MM/YYYY HH24:MI:SS') this_.Order_Date <= to_date('11/08/2011 20:59:59', 'DD/MM/YYYY HH24:MI:SS') ORDER BY this_.Order_Date desc ) row_ where rownum <=10) where rownum_ >10;
The proper SQL must have rownum <= 20 clause instead of rownum <=10. The original QueryTakeParameterSpecification.Bind method always used the MaxRows corresponding to Take() without considering the dialect requirements.
Attached is the patch which solves the above issue for all dialects which require absolute limit (Oracle included).
It seems the issue fixed for QueryOver<>, but not fixed for Query<>. For example: session.QueryOver<Order>().Skip(10).Take(10).List().Count; // return 10, ok session.Query<Order>().Skip(10).Take(10).ToList().Count; // return 0, wrong
Richard Brown
August 24, 2011 at 11:51 AM
Fixed - thanks for the patch.
Patrick Earl
August 21, 2011 at 11:04 PM
It'd be nice to have the Oracle tests up on the build server before resolving this issue. Hopefully that will happen in the near future.
QueryTakeParameterSpecification has a flaw in Bind because it does not take into account the absolute limit for certain dialects which have the flag UseMaxForLimit = true. The generated SQL does not return any results when executed for a query with Take(10).Skip(10) for example.
select
c1, c2
from
( select
row_.*,
rownum rownum_
from
( SELECT c1,c2
FROM
t1
WHERE
this_.Order_Date >= to_date('10/08/2011 21:00:00', 'DD/MM/YYYY HH24:MI:SS')
this_.Order_Date <= to_date('11/08/2011 20:59:59', 'DD/MM/YYYY HH24:MI:SS')
ORDER BY
this_.Order_Date desc ) row_
where
rownum <=10)
where
rownum_ >10;
The proper SQL must have rownum <= 20 clause instead of rownum <=10. The original QueryTakeParameterSpecification.Bind method always used the MaxRows corresponding to Take() without considering the dialect requirements.
Attached is the patch which solves the above issue for all dialects which require absolute limit (Oracle included).