[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'

[Strong Liu (JIRA)]

     [ https://hibernate.onjira.com/browse/HHH-7285?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Strong Liu resolved HHH-7285.
-----------------------------

       Resolution: Fixed
    Fix Version/s: 4.1.4
         Assignee: Strong Liu

> 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'

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