[hibernate-issues] [Hibernate-JIRA] Created: (HHH-6869) SQLQuery setMaxResults with SQLServer 2008 or 2005 dialect causes a magic ROW_NUMBER() column to be inserted as the first column rather than the last

[Keith Mashinter (JIRA)]

SQLQuery setMaxResults with SQLServer 2008 or 2005 dialect causes a magic ROW_NUMBER() column to be inserted as the first column rather than the last
-----------------------------------------------------------------------------------------------------------------------------------------------------

                 Key: HHH-6869
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-6869
             Project: Hibernate Core
          Issue Type: Bug
          Components: query-sql
    Affects Versions: 3.6.8
         Environment: Hibernate 3.6.8, SQLServer 2008
            Reporter: Keith Mashinter


SQLQuery setMaxResults with SQLServer 2008 or 2005 dialect causes a magic ROW_NUMBER() column to be inserted as the first column rather than the last.  This creates an unexpected shift in column order when setMaxResults() is used compared to when it is not, breaking the promise of least surprise from an API.

NOT Using setMaxResults:
SQLQuery qry = hs.createSQLQuery("select a,b,c from t");
qry.list():
a, b, c
a1, b1, c1

But WITH Using setMaxResults the specified columns are shifted:
SQLQuery qry = hs.createSQLQuery("select a,b,c from t");
qry.setMaxResults(1);
qry.list():
__hibernate_row_nr__, a, b, c
1, a1, b1, c1

A compromise would be to put the __hibernate_row_nr__ last, although that still adds something not requested but it least it doesn't change the index of the columns that were requested.
SQLQuery qry = hs.createSQLQuery("select a,b,c from t");
qry.setMaxResults(1);
qry.list():
a, b, c, __hibernate_row_nr__
a1, b1, c1, 1


--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira