[hibernate-issues] [Hibernate-JIRA] Created: (HHH-6679) SQL Server: Query with limit fails for HQL with 'DISTINCT'

SQL Server: Query with limit fails for HQL with 'DISTINCT' ---------------------------------------------------------- Key: HHH-6679 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-6679 Project: Hibernate Core Issue Type: Bug Affects Versions: 3.6.7 Environment: Hibernate 3.6.7, Microsoft SQL Server 2008 Reporter: Piotr Findeisen When I issue HQL query like below (this query is of course simplified) with limit and/or offset (e.g. {{1}} and {{0}}) {code} SELECT u.id, (SELECT COUNT(DISTINCT role.id) FROM Role role) FROM User u {code} then Hibernate issues something like this to the SQL Server: {code} WITH query AS (select ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__, user0_.urid as col_0_0_, (select count(role1_.srid) from roles role1_) as col_1_0_ from users user0_ group by user0_.urid, (select count(role1_.srid) ) SELECT * FROM query WHERE __hibernate_row_nr__ BETWEEN ? AND ? {code} The above SQL is seriously malformed, it even has more opening brackets {{'('}} than closing {{')'}}. SQL Server response with obvious exception: {code} com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'SELECT'. {code} -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira