"where - in clause" problem with collections

mrtz · **Joined:** Mon Aug 01, 2005 3:10 pm **Posts:** 22

Hi,

I have exceptions for a query after upgrading from Hibernate 3.0 to 3.2.5. I read some posts and what I gathered is that dereferencing of collections as I have below: "myjobs.jobid" is actually unsupported, although it used to somehow work in previous versions of hibernate. I think the following bug report is similar - http://opensource.atlassian.com/project ... e/HHH-2667

Can someone please suggest how I should go about re-implementing the kind of query I am trying to run below (which works fine in version 3.0)

Hibernate version: 3.2.5.ga

Code between sessionFactory.openSession() and session.close():

Code:

List<Integer> jobIds = getJobIds();
Query q = session.createQuery("select distinct project from Project project"
                                + " left join project.studies as studies left join studies.jobs as myjobs"
                                + " where size(studies) < 1"
                                + " or myjobs.jobid in (:jobIdList)");
                        q.setParameterList("jobIdList", jobIds);

Exception

Code:

java.lang.StringIndexOutOfBoundsException: String index out of range: -5
   at java.lang.String.substring(String.java:1768)
   at java.lang.String.substring(String.java:1735)
   at org.hibernate.hql.CollectionSubqueryFactory.createCollectionSubquery(CollectionSubqueryFactory.java:32)
   at org.hibernate.hql.ast.tree.FromElementType.toColumns(FromElementType.java:300)
   at org.hibernate.hql.ast.tree.FromElementType.toColumns(FromElementType.java:290)
   at org.hibernate.hql.ast.tree.FromElement.toColumns(FromElement.java:390)
   at org.hibernate.hql.ast.tree.DotNode.getColumns(DotNode.java:111)
   at org.hibernate.hql.ast.tree.DotNode.initText(DotNode.java:230)
   at org.hibernate.hql.ast.tree.DotNode.resolve(DotNode.java:224)
   at org.hibernate.hql.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:94)
   at org.hibernate.hql.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:90)
   at org.hibernate.hql.ast.HqlSqlWalker.resolve(HqlSqlWalker.java:728)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.expr(HqlSqlBaseWalker.java:1216)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.exprOrSubquery(HqlSqlBaseWalker.java:4041)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.comparisonExpr(HqlSqlBaseWalker.java:3573)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1762)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1712)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.whereClause(HqlSqlBaseWalker.java:776)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:577)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229)
   at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:228)
   at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:160)
   at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
   at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
   at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
   at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
   at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
   at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
   at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)

ramnath · **Posted:** Sun Jan 06, 2008 1:17 am

Hi,

Try something lie this..

Query q = session.createQuery("select distinct project from Project project"
+ " left join project.studies as studies left join studies.jobs as myjobs"
+ " where size(studies) < 1"
+ " or myjobs.jobid in ('1', '2');

where 1, 2 are values in jobList. This worked for me in different scenario.

Try to convert it into Criteria as

crt.add(Expression.in("myjobs.jobid",jobList));

mrtz · **Joined:** Mon Aug 01, 2005 3:10 pm **Posts:** 22

Actually I found that the problem is with the "size(studies) < 1" condition in the HQL. If I remove it, it works fine like this:

Code:

List<Integer> jobIds = getJobIds();
Query q = session.createQuery("select distinct project from Project project"
                                + " left join project.studies as studies left join studies.jobs as myjobs"                                
                                + " where myjobs.jobid in (:jobIdList)");
                        q.setParameterList("jobIdList", jobIds); 

For now I removed that condition in the HQL to handle that logic elsewhere. But I have not found a way to make it run as it was.

kokog · **Posted:** Wed Jan 23, 2008 5:15 am

Try to change the order of the conditions in the query.

select distinct project from Project project
left join project.studies as studies left join studies.jobs as myjobs
where
myjobs.jobid in (:jobIdList)
or
size(studies) < 1

Alban Scholer · **Joined:** Fri Nov 19, 2010 5:41 am **Posts:** 1

You certainly figured out a solution a long time ago, but for others:
The problem is you were using "size" in your query, which also happens to be an hibernate function. The hibernate parser panics when it finds this token, and there it crashes.
You'd have had the same issue if you would have tried to use a field named "elements" (the reason I came here).
viewtopic.php?f=1&t=959566&view=previous