[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3873) DB2Dialect.getLimitString raise DB2 error message when called with limit=0

DB2Dialect.getLimitString raise DB2 error message when called with limit=0 -------------------------------------------------------------------------- Key: HHH-3873 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3873 Project: Hibernate Core Issue Type: Bug Components: core Affects Versions: 3.3.1 Environment: DB2, http://viewvc.jboss.org/cgi-bin/viewvc.cgi/hibernate/core/branches/Branch... http://viewvc.jboss.org/cgi-bin/viewvc.cgi/hibernate/core/branches/Branch... Reporter: Julien Kronegg Assignee: Strong Liu Priority: Minor Original Estimate: 4 hours Remaining Estimate: 4 hours Executing a query called with setMaxResults(n) makes the underlying Hibernate code to call DB2Dialect.getLimitString. While this is working fine in general case, the following database error is raised when setting the maximum number of results to zero: SqlException: NUMBER 0 DIRECTLY SPECIFIED IN AN SQL STATEMENT IS OUTSIDE THE RANGE OF ALLOWABLE VALUES IN THIS CONTEXT (1, 2147483647) This is due to the getLimitString method in classes DB2400Dialect and DB2390Dialect : public String getLimitString(String sql, int offset, int limit) { return new StringBuffer(sql.length() + 40) .append(sql) .append(" fetch first ") .append(limit) .append(" rows only ") .toString(); } When the limit parameter is set to zero, the fetch limit is appended, which explains why the error message occur. Solution: ------------ When the limit is 0, there should be no "fetch first" instruction appended (as 0 is the general convention for "no limit"). The code would be: public String getLimitString(String sql, int offset, int limit) { + if (limit==0) return sql; return new StringBuffer(sql.length() + 40) .append(sql) .append(" fetch first ") .append(limit) .append(" rows only ") .toString(); } Workaround: ------------------ In order to avoid the error message, getLimitString(n) or setMaxResults(n) should be called with the 'newN' argument: int newN = (n==0?Integer.MAX_VALUE:n); Note: since the workaround is quite easy, I set the priority to "Minor"