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

[Piotr Findeisen (JIRA)]

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