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

[Piotr Findeisen (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-6679?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=43714#comment-43714 ] 

Piotr Findeisen commented on HHH-6679:
--------------------------------------

I suppose this is because method {{org.hibernate.dialect.SQLServer2005Dialect.replaceDistinctWithGroupBy}}, which is designed to handle {{SELECT DISTINCT ...}} cases, triggers its logic also when {{'DISTINCT'}} keyword comes from nested select.

Maybe matching the sql string against a regex like below would do?
{code}
Pattern.compile("^SELECT (DISTINCT)\\b", Pattern.CASE_INSENSITIVE)
{code}


> 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