11:12 a.m.
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
11:15 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-5926) Bugs in HQL (order by)
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-5926?page=c...
]
Ahmed Ali Elsayed Ali Soliman commented on HHH-5926:
----------------------------------------------------
i use Hibernate 3.6.1
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
11:34 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-5926) Bugs in HQL (order by)
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-5926?page=c...
]
Ahmed Ali Elsayed Ali Soliman commented on HHH-5926:
----------------------------------------------------
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
11:34 a.m.
New subject: [Hibernate-JIRA] Updated: (HHH-5926) Bugs in HQL (order by)
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-5926?page=c...
]
Ahmed Ali Elsayed Ali Soliman updated HHH-5926:
-----------------------------------------------
Attachment: HibernateTest.rar
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
2:38 p.m.
New subject: [Hibernate-JIRA] Issue Comment Edited: (HHH-5926) Bugs in HQL (order by)
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-5926?page=c...
]
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
6:37 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-5926) Bugs in HQL (order by)
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-5926?page=c...
]
Gail Badner commented on HHH-5926:
----------------------------------
Thanks for the test case.
I believe this is a duplicate of an existing issue.
Please do some searches to check.
Thanks,
Gail
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
11:59 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-5926) Bugs in HQL (order by)
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-5926?page=c...
]
Ahmed Ali Elsayed Ali Soliman commented on HHH-5926:
----------------------------------------------------
i didn't found any issue like this, i saw this bug after 3 days from hibernate 3.6.1
being released.
the question: is this bug will be solved and in what version?
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
8:52 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-5926) Bugs in HQL (order by)
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-5926?page=c...
]
Kevin Urciolo commented on HHH-5926:
------------------------------------
I have encountered the same issue in the version of hibernate distributed with JBoss
6.0.0. I tried Hibernate 3.6.5. However, it has the same problem.
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
8:57 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-5926) Bugs in HQL (order by)
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-5926?page=c...
]
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
4760
days inactive
4881
days old
hibernate-issues@lists.jboss.org
8 comments
3 participants
participants (3)
-
Ahmed Ali Elsayed Ali Soliman (JIRA) -
Gail Badner (JIRA)
-
Kevin Urciolo (JIRA)