SQLServer2005 Dialect creates invalid paging SQL for Native Queries. -------------------------------------------------------------------- Key: HHH-6567 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-6567 Project: Hibernate Core Issue Type: Bug Components: core Affects Versions: 3.6.4 Environment: Hibernate 3.6.4, MS SQL Server 2005 Reporter: Jonathan Crabtree With the following code.. SQLQuery query = getSession() .createSQLQuery("SELECT DISTINCT a.* FROM table_a a LEFT OUTER JOIN table_b b ON a.id = b.ref_id WHERE (a.sequence = 0 AND (b.status = 'X'))"); query.addEntity("entry", EntryStaging.class); query.setFirstResult(startIndex); query.setMaxResults(count); List result = query.list(); Hibernate creates the following SQL.. WITH query AS (select ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__, a.* from table_a a left outer join table_b b on a.id = b.ref_id where (a.sequence = 0 and (b.status = 'X')) group by a.* ) SELECT * FROM query WHERE __hibernate_row_nr__ BETWEEN ? AND ? Which throws the following exception.. hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) at org.hibernate.loader.Loader.doList(Loader.java:2536) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276) at org.hibernate.loader.Loader.list(Loader.java:2271) at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:316) at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1842) at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165) at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:157) at ...(xx.java:164) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '*'. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(Unknown Source) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208) at org.hibernate.loader.Loader.getResultSet(Loader.java:1953) at org.hibernate.loader.Loader.doQuery(Loader.java:802) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274) at org.hibernate.loader.Loader.doList(Loader.java:2533) ... 14 more This is due to the group by using * which MS SQL Server does not accept. A workaround is to list all columns in the query. Also, I had tried to work-around this problem by including the distinct in a sub query.. SELECT c.* FROM (SELECT DISTINCT a.* FROM table_a a LEFT OUTER JOIN table_b b ON a.id = b.ref_id WHERE (a.sequence = 0 AND (b.status = 'X'))) c but hibernate produced this sql.. WITH query AS (select ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__, c.* from (select a.* from table_a a left outer join table_b b on a.id = b.ref_id where (a.sequence = 0 and (b.status = 'X'))) c group by c.* ) SELECT * FROM query WHERE __hibernate_row_nr__ BETWEEN ? AND ? Looks like it has done a global search for the select distinct, instead of only checking at the 1st level. -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira