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

[Markus Horehled (JIRA)]

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


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'



--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira