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

[Kevin Urciolo (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5926?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=42606#action_42606 ] 

Kevin Urciolo commented on HHH-5926:
------------------------------------

Below are logs snips that show the HQL and the generated SQL.  The generation of cdbcollect2_ in the SQL is problematic.

ORA-01791: not a SELECTed expression

---------------------------
distinct(c.cdbCollections) from CdbAccessCtrl c where c.cdbCollections.collectionNum != 0 and  c.cdbCollections.collectionType like :collectionType order by c.cdbCollections.collectionName asc
2011-06-09 17:48:20,595 WARN  [org.hibernate.util.JDBCExceptionReporter] (http-0.0.0.0-8080-9) SQL Error: 1791, SQLState: 42000
2011-06-09 17:48:20,597 ERROR [org.hibernate.util.JDBCExceptionReporter] (http-0.0.0.0-8080-9) ORA-01791: not a SELECTed expression

2011-06-09 17:48:20,604 ERROR [STDERR] (http-0.0.0.0-8080-9) org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; SQL [select distinct cdbcollect1_.COLLECTION_NUM as COLLECTION1_552_, cdbcollect1_.COLLECTION_NAME as COLLECTION2_552_, cdbcollect1_.COLLECTION_TYPE as COLLECTION3_552_, cdbcollect1_.DESCRIPTION as DESCRIPT4_552_, cdbcollect1_.DATE_CREATED as DATE5_552_, cdbcollect1_.DATE_DELETED as DATE6_552_, cdbcollect1_.OWNER as OWNER552_, cdbcollect1_.OWNER_EMAIL as OWNER8_552_, cdbcollect1_.OWNER_PHONE as OWNER9_552_, cdbcollect1_.MAX_PRINTS as MAX10_552_, cdbcollect1_.MAX_MARKS as MAX11_552_, cdbcollect1_.MAX_LIFTS as MAX12_552_, cdbcollect1_.MAX_FACIALS as MAX13_552_, cdbcollect1_.TIME_STAMP as TIME14_552_, cdbcollect1_.STATUS as STATUS552_, cdbcollect1_.TP_INCLUDE_FLAG as TP16_552_, cdbcollect1_.MARK_INCLUDE_FLAG as MARK17_552_, cdbcollect1_.DUPLICATE_ADD as DUPLICATE18_552_, cdbcollect1_.DEFAULT_SRCH as DEFAULT19_552_, cdbcollect1_.MARK_ADDS_WITHOUT_SRCH as MARK20_552_, cdbcollect1_.SRCH_MASK_POSITION as SRCH21_552_, cdbcollect1_.OWNING_BUSINESS as OWNING22_552_, cdbcollect1_.rowid as rowid_ from IDENT1.CDB_ACCESS_CTRL cdbaccessc0_ inner join IDENT1.CDB_COLLECTIONS cdbcollect1_ on cdbaccessc0_.COLLECTION_NUM=cdbcollect1_.COLLECTION_NUM, IDENT1.CDB_COLLECTIONS cdbcollect2_ where cdbaccessc0_.COLLECTION_NUM=cdbcollect2_.COLLECTION_NUM and cdbaccessc0_.COLLECTION_NUM<>0 and (cdbcollect2_.COLLECTION_TYPE like ?) order by cdbcollect2_.COLLECTION_NAME asc]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query

> 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, HibernateTest.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