[hibernate-issues] [Hibernate-JIRA] Created: (HHH-6567) SQLServer2005 Dialect creates invalid paging SQL for Native Queries.

[Jonathan Crabtree (JIRA)]

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