[hibernate-issues] [Hibernate-JIRA] Issue Comment Edited: (HHH-5926) Bugs in HQL (order by)
Ahmed Ali Elsayed Ali Soliman (JIRA)
noreply at atlassian.com
Wed Feb 23 08:38:08 EST 2011
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5926?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=40063#action_40063 ]
Ahmed Ali Elsayed Ali Soliman edited comment on HHH-5926 at 2/23/11 7:37 AM:
-----------------------------------------------------------------------------
I made a test for this error in a simple application the error is the combination between distinct & order by
i'll attach this simple project to test it in any java IDE like eclipse
the error is
org.hibernate.exception.SQLGrammarException: could not execute query
Caused by: java.sql.SQLException: ORA-01791: not a SELECTed expression
this is the main project file
import java.util.Iterator;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
public class MainProject {
public static void main(String[] args)
{
Session session = null;
try
{
SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
session = sessionFactory.openSession();
String sb = "select distinct u.unitTypes from Unit u order by u.unitTypes.name";
Query query = session.createQuery(sb);
List<Object> list = query.list();
for(Iterator<Object> itr = list.iterator(); itr.hasNext(); )
{
UnitType type = (UnitType) itr.next();
System.out.println(type.getName());
}
}
catch (Exception e) {
e.printStackTrace();
}
finally
{
session.close();
}
}
}
the generated SQL is
select
distinct unittype1_.ID as ID1_,
unittype1_.NAME as NAME1_
from
UNITS unit0_
inner join
UNIT_TYPES unittype1_
on unit0_.UNIT_TYPE_ID=unittype1_.ID,
UNIT_TYPES unittype2_
where
unit0_.UNIT_TYPE_ID=unittype2_.ID
order by
unittype2_.NAME
i noticed that in the inner join hibernate add an extra join with UNIT_TYPES unittype2_
was (Author: ahmed1001):
I made a test for this error in a simple application the error is the combination between distinct & order by
i'll attach this simple project to test it in any java IDE like eclipse
the error is
org.hibernate.exception.SQLGrammarException: could not execute query
Caused by: java.sql.SQLException: ORA-01791: not a SELECTed expression
this is the main project file
import java.util.Iterator;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
public class MainProject {
public static void main(String[] args)
{
Session session = null;
try
{
SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
session = sessionFactory.openSession();
String sb = "select distinct u.unitTypes from Unit u order by u.unitTypes.name";
Query query = session.createQuery(sb);
List<Object> list = query.list();
for(Iterator<Object> itr = list.iterator(); itr.hasNext(); )
{
UnitType type = (UnitType) itr.next();
System.out.println(type.getName());
}
}
catch (Exception e) {
e.printStackTrace();
}
finally
{
session.close();
}
}
}
the generated SQL is
select
distinct unittype1_.ID as ID1_,
unittype1_.NAME as NAME1_
from
UNITS unit0_
inner join
UNIT_TYPES unittype1_
on unit0_.UNIT_TYPE_ID=unittype1_.ID,
UNIT_TYPES unittype2_
where
unit0_.UNIT_TYPE_ID=unittype2_.ID
order by
unittype2_.NAME
i notice that in the inner join hibernate add an extra join with UNIT_TYPES unittype2_
> 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
More information about the hibernate-issues
mailing list