[hibernate-issues] [Hibernate-JIRA] Created: (HHH-6567) SQLServer2005 Dialect creates invalid paging SQL for Native Queries.
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