[hibernate-issues] [Hibernate-JIRA] Resolved: (HHH-7285) Using distinct in Criteria, using first- and maxResults, fails with SQLGrammarException when a colum in the distinct clause starts with 'from'

Using distinct in Criteria, using first- and maxResults, fails with SQLGrammarException when a colum in the distinct clause starts with 'from' ---------------------------------------------------------------------------------------------------------------------------------------------- Key: HHH-7285 URL: https://hibernate.onjira.com/browse/HHH-7285 Project: Hibernate ORM Issue Type: Bug Components: query-criteria Affects Versions: 3.6.9 Environment: Spring 3.1.1, Hibernate 3.6.9, SQL Server 2008 Reporter: Markus Horehled Assignee: Strong Liu Fix For: 4.1.4 Using Hibernate Criteria with setting first- and maxResults fails with an SQLGrammarException in SQLServer 2008 when one of the columns in the select distinct clause starts with a 'from', like fromDate. The original query would be something like: select distinct this_.Id as y0_, this_.FromDate as y1_ from ... whereas the query eventually issued by Hibernate is something like: WITH query AS (select ROW_NUMBER() OVER (order by this_.fromdate desc, this_.id asc) ... group by this_.id, this_.) SELECT * FROM query WHERE __hibernate_row_nr__ BETWEEN ? AND ? The fromDate column is apparently missing in the query's group by. Reason is found in the SQLServer2005Dialect class in getSelectFieldsWithoutAliases(StringBuilder sql), where a substring is constructed till the FROM (i.e. 'from') string. This should rather be 'from ', i.e. including a blank, since in that particular case the 'from' from the 'fromDate' column is being regarded as the end of the substring being constructed. Workaround is for example to not name any column being used in a distinct starting with 'from'