[hibernate-issues] [Hibernate-JIRA] Created: (HHH-3344) DB2Dialect.getLimitString doesn't work for some queries

[Damon Horrell (JIRA)]

DB2Dialect.getLimitString doesn't work for some queries
-------------------------------------------------------

                 Key: HHH-3344
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3344
             Project: Hibernate3
          Issue Type: Bug
            Reporter: Damon Horrell
            Priority: Minor


If a query uses * but doesn't use a table alias then the modified query created by DB2Dialect.getLimitString is invalid.

e.g.

  select * from mytable

becomes

  select * from ( select rownumber() over() as rownumber_, * from mytable ) as temp_ where rownumber_ <= ?

which causes

  DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: *;ver() as rownumber_,;<select_sublist>

I have worked around this for my project by extending DB2Dialect with:

public String getLimitString(String sql, boolean hasOffset) {
  return "select * from ( select rownumber() over() as rownumber_, original_query.* from (" + sql
    + ") original_query ) as temp_ where rownumber_ <= ?";
}

This code will need extending to handle the hasOffset case etc.

Real code probably won't be using select * anyway so this probably isn't a big problem.  My example allows the user to enter a custom query so I have to handle this situation.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira