Hibernate version:3.1.2
Database on the backend: DB2/AS400
Dialect used: org.hibernate.dialect.DB2400Dialect

I have a simple HQL query: "select e.empId, e.empName from Employee as e order by e.empName"
The generated SQL will be: "select employee0_.EMP_ID as col_0_0_, employee0_.EMP_NAME as col_1_0_ from EMPLOYEE employee0_ order by e.empName"

Then an exception will thrown: cannot identify e.empName

This is because hibernate engine lables empName as col_1_0_ when it generates the SQL.

If I am obtaining the whole Employee object, then I can use the criteria queries to order the resulting list. This problem arises if you are trying to retrieve a certain number of columns from the databse and you are trying to sort the record based on one of those columns.

Does anybody know a way to work around this issue?
It will be highly appreciated.

Did you try to use SQLQuery instead of Query?

is empName the column name, or the alias in your class? I have this working, but the order by sorts on the class mapped name, not the database column name.


Good Luck

empName is not the database column name, it is a class mapped name.
If this has really worked for you, I am interested to know what database/dialect/or any other settings..etc. you are using, because if you look at the post at the bottom of this page, you will see that this is not supported.

Here is what I tried:
my query:
hqly.append( "select pr.priority_Id as {pr.priorityId}, pr.level as {pr.level}, pr.descript as {pr.descript}, pr.wo_type as {pr.woType} " )

.append( " from Priority {pr} where {pr.woType} = :woTypex" )

.append( " order by {pr.priorityId}" )

List resulty = SessionManager.getSession().createSQLQuery( hqly.toString() )

.addEntity( "pr",Priority.class )

.setString( "woTypex",wType )

.list();

The generated sql seemed ok in this case:
Hibernate: select pr.priority_Id as PRIORITY1_17_0_, pr.level as LEVEL17_0_, pr.descript as DESCRIPT17_0_, pr.wo_type as WO2_17_0_ from Priority pr where WO2_17_0_ = ? order by PRIORITY1_17_0_

However, this query was still reported as:
ERROR main org.hibernate.util.JDBCExceptionReporter - [SQL0206] Column WO2_17_0_ not in specified tables.

- I wonder why it is still saying WO2_17_0_ not in the specified tables when it is actually there, also wonder if this only AS/400 related issue.

Check this post out on the order-by clause from Hibernate-JIRA:
http://opensource2.atlassian.com/projec ... se/HHH-892

But, if this is how really hibernate is supposed to function, I think we got problems, because there are situations all the time when you need to retrieve only the selected columns from a table, especially those which have a large number of columns.

that is what I have,annd it works just fine. I needed to get distinct and order by to work. I am using Hibernate 3.1, oracle 10xe.

Does his help?

I have a very hard time believing that your first query, run through Session.createQuery() results in the SQL statement you specified. We have lots of tests testing the query parser, including order-by clauses. And that is a pretty basic order-by clause.

The JIRA case you linked is a distinctly different scenario. It is talking about using *HQL defined aliases* in the order-by clause, which is not supported.

Please post the AST trees for that query... (logger = org.hibernate.hql.ast.AST).

HQL Query: "select ei.empId, ei.empName from EmpInfo as ei order by col_1_0_"

Generated SQL: "Hibernate: select empinfo0_.EMP_ID as col_0_0_, empinfo0_.EMP_NAME as col_1_0_ from EMP_INFO empinfo0_ order by col_1_0_"

AST tree:
DEBUG http-8080-Processor24 org.hibernate.hql.ast.AST - --- HQL AST ---
\-[QUERY] 'query'
+-[SELECT_FROM] 'SELECT_FROM'
| +-[FROM] 'from'
| | \-[RANGE] 'RANGE'
| | +-[DOT] '.'
| | | +-[DOT] '.'
| | | | +-[DOT] '.'
| | | | | +-[DOT] '.'
| | | | | | +-[DOT] '.'
| | | | | | | +-[IDENT] 'com'
| | | | | | | \-[IDENT] 'adestagroup'
| | | | | | \-[IDENT] 'intercom'
| | | | | \-[IDENT] 'tac'
| | | | \-[IDENT] 'bo'
| | | \-[IDENT] 'EmpInfo'
| | \-[ALIAS] 'ei'
| \-[SELECT] 'select'
| +-[DOT] '.'
| | +-[IDENT] 'ei'
| | \-[IDENT] 'empId'
| \-[DOT] '.'
| +-[IDENT] 'ei'
| \-[IDENT] 'empName'
\-[ORDER] 'order'
\-[IDENT] 'col_1_0_'

DEBUG http-8080-Processor24 org.hibernate.hql.ast.AST - --- SQL AST ---
\-[SELECT] QueryNode: 'SELECT' querySpaces (EMP_INFO)
+-[SELECT_CLAUSE] SelectClause: '{select clause}'
| +-[DOT] DotNode: 'empinfo0_.EMP_ID' {propertyName=empId,dereferenceType=4,propertyPath=empId,path=ei.empId,tableAlias=empinfo0_,className=com.adestagroup.intercom.tac.bo.EmpInfo,classAlias=ei}
| | +-[ALIAS_REF] IdentNode: 'empinfo0_.EMP_ID' {alias=ei, className=com.adestagroup.intercom.tac.bo.EmpInfo, tableAlias=empinfo0_}
| | \-[IDENT] IdentNode: 'empId' {originalText=empId}
| +-[SELECT_COLUMNS] SqlNode: ' as col_0_0_'
| +-[DOT] DotNode: 'empinfo0_.EMP_NAME' {propertyName=empName,dereferenceType=4,propertyPath=empName,path=ei.empName,tableAlias=empinfo0_,className=com.adestagroup.intercom.tac.bo.EmpInfo,classAlias=ei}
| | +-[ALIAS_REF] IdentNode: 'empinfo0_.EMP_ID' {alias=ei, className=com.adestagroup.intercom.tac.bo.EmpInfo, tableAlias=empinfo0_}
| | \-[IDENT] IdentNode: 'empName' {originalText=empName}
| \-[SELECT_COLUMNS] SqlNode: ' as col_1_0_'
+-[FROM] FromClause: 'from' FromClause{level=1, fromElementCounter=1, fromElements=1, fromElementByClassAlias=[ei], fromElementByTableAlias=[empinfo0_], fromElementsByPath=[], collectionJoinFromElementsByPath=[], impliedElements=[]}
| \-[FROM_FRAGMENT] FromElement: 'EMP_INFO empinfo0_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=ei,role=null,tableName=EMP_INFO,tableAlias=empinfo0_,origin=null,colums={,className=com.adestagroup.intercom.tac.bo.EmpInfo}}
\-[ORDER] OrderByClause: 'order'
\-[IDENT] IdentNode: 'col_1_0_' {originalText=col_1_0_}

------------------------------------------------------------------------------------
I tried query with aliases for selected attributes such as:
"select ei.empId as e1, ei.empName as e2 from EmpInfo as ei order by e2"
But, again Hibernate still resolved the individual attributes as col_0_0_, col_1_0_ and so on...
Hibernate: select empinfo0_.EMP_ID as col_0_0_, empinfo0_.EMP_NAME as col_1_0_ from EMP_INFO empinfo0_ order by e2
and I got the exception:
WARN http-8080-Processor25 org.hibernate.util.JDBCExceptionReporter - SQL Error: -208, SQLState: 42707
ERROR http-8080-Processor25 org.hibernate.util.JDBCExceptionReporter - [SQL0208] ORDER BY column E2 or expression not in result table.

Caused by: java.sql.SQLException: [SQL0208] ORDER BY column E2 or expression not in result table.

AST Tree:
DEBUG http-8080-Processor25 org.hibernate.hql.ast.AST - --- HQL AST ---
\-[QUERY] 'query'
+-[SELECT_FROM] 'SELECT_FROM'
| +-[FROM] 'from'
| | \-[RANGE] 'RANGE'
| | +-[DOT] '.'
| | | +-[DOT] '.'
| | | | +-[DOT] '.'
| | | | | +-[DOT] '.'
| | | | | | +-[DOT] '.'
| | | | | | | +-[IDENT] 'com'
| | | | | | | \-[IDENT] 'adestagroup'
| | | | | | \-[IDENT] 'intercom'
| | | | | \-[IDENT] 'tac'
| | | | \-[IDENT] 'bo'
| | | \-[IDENT] 'EmpInfo'
| | \-[ALIAS] 'ei'
| \-[SELECT] 'select'
| +-[AS] 'as'
| | +-[DOT] '.'
| | | +-[IDENT] 'ei'
| | | \-[IDENT] 'empId'
| | \-[IDENT] 'e1'
| \-[AS] 'as'
| +-[DOT] '.'
| | +-[IDENT] 'ei'
| | \-[IDENT] 'empName'
| \-[IDENT] 'e2'
\-[ORDER] 'order'
\-[IDENT] 'e2'

DEBUG http-8080-Processor25 org.hibernate.hql.ast.AST - --- SQL AST ---
\-[SELECT] QueryNode: 'SELECT' querySpaces (EMP_INFO)
+-[SELECT_CLAUSE] SelectClause: '{select clause}'
| +-[DOT] DotNode: 'empinfo0_.EMP_ID' {propertyName=empId,dereferenceType=4,propertyPath=empId,path=ei.empId,tableAlias=empinfo0_,className=com.adestagroup.intercom.tac.bo.EmpInfo,classAlias=ei}
| | +-[ALIAS_REF] IdentNode: 'empinfo0_.EMP_ID' {alias=ei, className=com.adestagroup.intercom.tac.bo.EmpInfo, tableAlias=empinfo0_}
| | \-[IDENT] IdentNode: 'empId' {originalText=empId}
| +-[SELECT_COLUMNS] SqlNode: ' as col_0_0_'
| +-[DOT] DotNode: 'empinfo0_.EMP_NAME' {propertyName=empName,dereferenceType=4,propertyPath=empName,path=ei.empName,tableAlias=empinfo0_,className=com.adestagroup.intercom.tac.bo.EmpInfo,classAlias=ei}
| | +-[ALIAS_REF] IdentNode: 'empinfo0_.EMP_ID' {alias=ei, className=com.adestagroup.intercom.tac.bo.EmpInfo, tableAlias=empinfo0_}
| | \-[IDENT] IdentNode: 'empName' {originalText=empName}
| \-[SELECT_COLUMNS] SqlNode: ' as col_1_0_'
+-[FROM] FromClause: 'from' FromClause{level=1, fromElementCounter=1, fromElements=1, fromElementByClassAlias=[ei], fromElementByTableAlias=[empinfo0_], fromElementsByPath=[], collectionJoinFromElementsByPath=[], impliedElements=[]}
| \-[FROM_FRAGMENT] FromElement: 'EMP_INFO empinfo0_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=ei,role=null,tableName=EMP_INFO,tableAlias=empinfo0_,origin=null,colums={,className=com.adestagroup.intercom.tac.bo.EmpInfo}}
\-[ORDER] OrderByClause: 'order'
\-[IDENT] IdentNode: 'e2' {originalText=e2}

-------------------------------------------------------------------------
Steve, what I am trying to say is Hibernate automatically generates aliases for the columns and it always overrides any user defined aliases, that's why e2 is being shown as an unknown column which was the similar issue on the JIRA, even though it was for an aggregate column. But this behaviour is inconsistent because there were situations in my project when hibernate generated aliases like survey2_, etc... which is similar to the comment posted by Jere Krischel on the same JIRA when HQL generated alias was LASTNAME2_.

If I run a query like this: "select ei.empId, ei.empName from EmpInfo as ei order by ei.empName"

"Hibernate: select empinfo0_.EMP_ID as col_0_0_, empinfo0_.EMP_NAME as col_1_0_ from EMP_INFO empinfo0_ order by empinfo0_.EMP_NAME"

Exception thrown:
WARN http-8080-Processor24 org.hibernate.util.JDBCExceptionReporter - SQL Error: -208, SQLState: 42707
ERROR http-8080-Processor24 org.hibernate.util.JDBCExceptionReporter - [SQL0208] ORDER BY column EMP_NAME or expression not in result table

I was experimenting with MySQL Database and suddenly realized that in MySQL (and most possibly in almost any other enterprise DBMS) the order by queries I was trying to run were valid., i.e.,
"select ix.emp_id as e1, ix.emp_name as e2 from itrscmgte.emp_info as ix order by ix.emp_name"
or,
"select ix.emp_id as e1, ix.emp_name as e2 from itrscmgte.emp_info as ix order by e2"

are both valid, where as in AS/400, it must be the second one.

As HQL will define its own aliases, I must pick up those from the generated SQLs whenever there is a need to order the resultst.