[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-5926) Bugs in HQL (order by)

Bugs in HQL (order by) ---------------------- Key: HHH-5926 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5926 Project: Hibernate Core Issue Type: Bug Components: query-hql, query-sql Affects Versions: 3.6.1 Environment: Application Server: Tomcat 7.0.8 Database: Oracle 11g Reporter: Ahmed Ali Elsayed Ali Soliman Attachments: Beans & Mappings.rar *there is a big problem in HQL & the generated SQL* this is the hibernate properties in XML <prop key="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</prop> <prop key="hibernate.query.substitutions">true 'T', false 'F'</prop> <prop key="hibernate.show_sql">true</prop> <prop key="hibernate.format_sql">true</prop> <prop key="hibernate.query.factory_class">org.hibernate.hql.ast.ASTQueryTranslatorFactory</prop> <prop key="hibernate.cglib.use_reflection_optimizer">true</prop> <prop key="hibernate.generate_statistics">true</prop> there is a bug in HQL when i make query.list(hql) HQL: select distinct service.module.component.system, service.module.component , service.module from Service as service where service.id in (928, 415) order by service.module.component.system.orderBy the generated SQL is select distinct system3_.ID as ID149_0_, component2_.ID as ID150_1_, module1_.ID as ID151_2_, system3_.DESCRIPTION as DESCRIPT2_149_0_, system3_.NAME as NAME149_0_, system3_.ORDER_BY as ORDER7_149_0_, system3_.MENU_ID as MENU8_149_0_, component2_.NAME as NAME150_1_, component2_.SYSTEM_ID as SYSTEM3_150_1_, component2_.DESCRIPTION as DESCRIPT4_150_1_, component2_.ORDER_BY as ORDER8_150_1_, component2_.PARENT_COMPONENT_ID as PARENT9_150_1_, module1_.NAME as NAME151_2_, module1_.WEB_DIRECTORY as WEB3_151_2_, module1_.MODULE_PATH as MODULE4_151_2_, module1_.DESCRIPTION as DESCRIPT5_151_2_, module1_.COMPONENT_ID as COMPONENT9_151_2_, module1_.ORDER_BY as ORDER10_151_2_ from SERVICES service0_, MODULES module1_, COMPONENTS component2_ inner join SYSTEMS system3_ on component2_.SYSTEM_ID=system3_.ID, *SYSTEMS system9_* where service0_.MODULE_ID=module1_.ID and module1_.COMPONENT_ID=component2_.ID and component2_.SYSTEM_ID=system9_.ID and ( service0_.ID in ( 928 , 415 ) ) order by *system9_.ORDER_BY* the errors are - SQL Error: 1791, SQLState: 42000 - ORA-01791: not a SELECTed expression the generated SQL is wrong because it add system9_ and no need for it, it has already system3_ there is a column in System bean called orderBy the hierarchy of beans like this (Service --> Module --> Component --> System) i attached the beans and mapping *NOTE: the hql works fine on Hibernate 3.4*