LPS-39031 DB2Dialect, if doing sql query directly then first column should not contain row number

Hey Miguel,

Sampsa has found a special corner case when DB2Dialect caused trouble.

If we have a query like this:

SELECT id, value FROM QueryUtilTest ORDER BY id ASC

DB2Dialect does the paging in the following way:

SELECT outerQuery.* FROM
  (
    SELECT ROW_NUMBER() OVER() AS rowNumber_, innerQuery.* FROM
    (
      SELECT id, value FROM QueryUtilTest ORDER BY id ASC FETCH FIRST 10 ROWS ONLY
    ) AS innerQuery
  ) AS outerQuery
WHERE
  rowNumber_ > 5 OPTIMIZE FOR 10 ROWS

As you can see, the value of ROW_NUMBER() becomes the first column. When the end user tries to process the result set, he gets an unexpected result, because getting the first value from a record gives the value of "rowNumber_" instead of the value of "id".

If you take a look at http://grepcode.com/file/repo1.maven.org/maven2/org.hibernate/hibernate/3.2.7.ga/org/hibernate/dialect/Oracle9iDialect.java#Oracle9iDialect.getLimitString%28java.lang.String%2Cboolean%29, you can see that the "rownum" placed as the last column; this way it causes no trouble.

Thanks,
László.