When using pagination feature (setMaxResults) without setting an offset (setFirstResult( 0 )), it is not necessary to perform the complex sql-transformation into a common_table_expression.
In such case it is much more simplier to use the old approach by using the "top" keyword.
createSQLQuery("select id from Product2 order by id").setMaxResults(2)
Instead to transform it into:
we would trasform it into:
- the resulting sql is smaller and easier to read
- the trasformation is faster (probably also the execution on sqlserver)
- the trasformation is more robust against bugs, as it is much siplier.
Pullrequest will follow asap...