[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-6679?page=c...
]
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