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

[Ahmed Ali Elsayed Ali Soliman (JIRA)]

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*

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira