Hi
I have a problem running native queries with hibernate.
When I execute this query:

with this as data in the order data table (columns: dataSource, orderNumber, supplierNumber, customerNumber)

G 101967 O1 100999
G 101968 O1 100999
G 101969 O1 100999
T 101967 O1 100999
T 101968 O2 100999
T 101969 O1 49978

I expect to get this result (as returned by the eclipse sql scrap book):

101967 101967 100999 100999 O1 O1 T G
101968 101968 100999 100999 O2 O1 T G
101969 NULL 49978 NULL O1 NULL T NULL

Yet, when I execute this with Hibernate using this statement:

I get a String array with this values (notive that the second row should contain "O1 O2" and the 3rd the null values):

[101967, 101967, 100999, 100999, O1, O1, T, T]
[101968, 101968, 100999, 100999, O2, O2, T, T]
[101969, 101969, 49978, 49978, O1, O1, T, T]

Why? The value for the supplier number of 101969 (100999 vs. 49978) is different so hibernate should return null values as does the sql scrap book for these entries.

Thanks in advance for you help

I finally found out what the problem is.
The ScalarResultColumnProcessor uses insufficent alias for the columns as it omits the "Table":

So i have orderdata, orderdata, suppliernumber, suppliernumber...

Is there anyway to force Hibernate to also include this "domain" ("T.orderdata", "G.orderdata"; i have no idea how this is called).

Thank you

This seems (still) to be an open bug in hibernate:

http://opensource.atlassian.com/project ... e/HHH-4956

Cheers, Arndt

PS: Would be great if this would be fixed at some point...

I am writing a native query with a right outer join. But i keep getting a syntax error from hibernate-core-4.2.2.CR1.jar as stated below. Can someone please tell me where i am going wrong with my query. The query is:
select distinct app.app_id, app.app_name, "
+ " obj.object_id, obj.object_name, obj.object_type, "
+ " obj.object_descr, pobj.object_name "
+ " from BasisObjectEntity AS pobj RIGHT OUTER JOIN BasisObjectEntity AS obj, BasisApplicationEntity AS app, BasisAccessEntity AS acc"
+ " pobj.object_id = obj.func_object_id "
+ " and obj.app_id = app.app_id "
+ " and app.app_name = :appName "
+ " and obj.object_id = acc.object_id "
+ " and acc.role_id in (" + this.roles + ")"




The error message i get is(i also get the same error when i remove the AS):
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: pobj near line 1, column 402 [select distinct app.app_id, app.app_name, obj.object_id, obj.object_name, obj.object_type, obj.object_descr, pobj.object_name from com.ec.eccore.util.security.entities.BasisObjectEntity as pobj RIGHT OUTER JOIN BasisObjectEntity as obj, com.ec.eccore.util.security.entities.BasisApplicationEntity as app, com.ec.eccore.util.security.entities.BasisAccessEntity as acc pobj.object_id = obj.func_object_id and obj.app_id = app.app_id and app.app_name = :appName and obj.object_id = acc.object_id and acc.role_id in ('SYST.ADM','COST','ROOT','WEB','REVENUE','JBossAdmin')]
at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1]
at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1]
at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:79) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:276) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:180) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1]
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:105) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1]
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:80) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1]
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:168) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1]
at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:221) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1]
at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:199) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1]
at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1734) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1]
at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:291) [hibernate-entitymanager-4.2.0.CR1.jar:4.2.0.CR1]

Ok, i made a typing mistake ommiting the where clause. But now i am have the following query; and i am having the exception below. Can you please advice on that? Please see below:

Query: select distinct app.app_id, app.app_name, obj.object_id, obj.object_name, obj.object_type, obj.object_descr, pobj.object_name from BasisObjectEntity AS pobj RIGHT OUTER JOIN fetch BasisObjectEntity AS obj, BasisApplicationEntity AS app, BasisAccessEntity AS acc where pobj.object_id = obj.func_object_id and obj.app_id = app.app_id and app.app_name = :appName and obj.object_id = acc.object_id and acc.role_id in (" + this.roles + ")"

Exception: Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Path expected for join! [select distinct app.app_id, app.app_name, obj.object_id, obj.object_name, obj.object_type, obj.object_descr, pobj.object_name from com.ec.eccore.util.security.entities.BasisObjectEntity AS pobj RIGHT OUTER JOIN fetch BasisObjectEntity AS obj, com.ec.eccore.util.security.entities.BasisApplicationEntity AS app, com.ec.eccore.util.security.entities.BasisAccessEntity AS acc where pobj.object_id = obj.func_object_id and obj.app_id = app.app_id and app.app_name = :appName and obj.object_id = acc.object_id and acc.role_id in ('SYST.ADM','COST','ROOT','WEB','REVENUE','JBossAdmin')] at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1] at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1] at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:79) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1] at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:255) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1] at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:183) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1] at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1] at org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:105) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1] at org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:80) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1] at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:168) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1] at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:221) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1] at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:199) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1] at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1734) [hibernate-core-4.2.0.CR1.jar:4.2.0.CR1] at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:291) [hibernate-entitymanager-4.2.0.CR1.jar:4.2.0.CR1] ... 193 more