-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 
Author Message
 Post subject: Parameter index out of range error with SUBSELECT
PostPosted: Wed Jul 02, 2014 6:12 pm 
Newbie

Joined: Wed Jul 02, 2014 5:38 pm
Posts: 1
After changing the fetching strategy for an entity from SELECT to SUBSELECT, some of my queries result in an SqlException: Parameter index out of range (4 > number of parameters, which is 3).

After studying the queries generated, I believe that the problem might be related to the use of a bind variable in the ORDER BY clause. The problem does not occur when I remove the ORDER BY clause.

MAIN QUERY
Code:
select jpaorder0_.id as id1_0_0_, etc...
   from `order` jpaorder0_ inner join checkout jpacheckou1_ on jpaorder0_.checkout_id=jpacheckou1_.id
   where jpaorder0_.business_id=2 and (jpaorder0_.verified is not null) and jpaorder0_.state=? and (jpaorder0_.completed between ? and ?)
   order by case when jpaorder0_.state=? then jpaorder0_.completed else jpaorder0_.verified end desc limit ?
TRACE org.hibernate.type.EnumType - Binding [COMPLETED] to parameter: [1]
TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [2] as [TIMESTAMP] - [Tue Jul 02 17:52:06 EDT 2013]
TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [3] as [TIMESTAMP] - [Thu Jul 03 17:52:06 EDT 2014]
TRACE org.hibernate.type.EnumType - Binding [COMPLETED] to parameter: [4]


SUBSELECT QUERY
Code:
select refunds0_.order_id as order_id9_0_1_, etc...
from order_refund refunds0_
where refunds0_.order_id in (
   select jpaorder0_.id
   from `order` jpaorder0_ inner join checkout jpacheckou1_ on jpaorder0_.checkout_id=jpacheckou1_.id
   where jpaorder0_.business_id=2 and (jpaorder0_.verified is not null) and jpaorder0_.state=? and (jpaorder0_.completed between ? and ?) )
TRACE org.hibernate.type.EnumType - Binding [COMPLETED] to parameter: [1]
TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [2] as [TIMESTAMP] - [Tue Jul 02 17:52:06 EDT 2013]
TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [3] as [TIMESTAMP] - [Thu Jul 03 17:52:06 EDT 2014]
TRACE org.hibernate.type.EnumType - Binding [COMPLETED] to parameter: [4]


Code Generating Order By Clause
Code:
CriteriaBuilder cb, CriteriaQuery cq;
Case<Date> orderByCase = cb.<Date> selectCase();
SingularAttribute<JpaOrder, Date> dateField = getDateField(entry.getValue());
orderByCase.when(cb.equal(orderRoot.get(JpaOrder_.state), state), orderRoot.get(dateField));
cq.orderBy(cb.asc(orderByCase));


Is there a way to work around this issue?

On a semi-related note, if you look at the query, it contains "jpaorder0_.business_id=2". Why is it not a bind variable? The value is a BigInteger and is passed in like so:
Code:
criteriaBuilder.equal(root.get(JpaOrder_.businessId), businessId)


Is it possible to somehow hard-code the bind variable in the ORDER BY clause to fix the parameter out of range issue?

I've only posted code that I think is relevant. Please let me know if you would like me to provide the full Java code.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
cron
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.