[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - parentheses not enforcing evaluation order.

[awclemen]

Greetings Forum Folks,

I'm attempting to do some EJB QL on Jboss 4.0.4 compiled for ejb3 and I've run into a problem.  The query I am attempting is the following:


  |             Query query = entityManager.createQuery("FROM ProjectEstimate AS pe WHERE 
  | ((pe.subStartDate IS NOT NULL and pe.subEndDate IS NULL and :date >= pe.subStartDate) 
  | or (pe.subEndDate IS NOT NULL and pe.subStartDate IS NULL and :date <= pe.subEndDate) 
  | or (pe.subStartDate IS NOT NULL and pe.subEndDate IS NOT NULL and :date >= pe.subStartDate and :date <= pe.subEndDate)) 
  | ORDER BY pe.name ");
  | query.setParameter("date", new java.util.Date());
  | 

However, when this runs, the where clause of the sql is:


  | WHERE (pe.subStartDate IS NOT NULL) and (pe.subEndDate IS NULL) and (:date >= pe.subStartDate) 
  | or (pe.subEndDate IS NOT NULL) and (pe.subStartDate IS NULL) and (:date <= pe.subEndDate) 
  | or (pe.subStartDate IS NOT NULL) and (pe.subEndDate IS NOT NULL) and (:date >= pe.subStartDate and :date <= pe.subEndDate)
  | 

We all know that those two where clauses don't return the same data.  Reading the J2EE tutorial on sun, it states the following:

anonymous wrote : A WHERE clause consists of a conditional expression, which is evaluated from left to right within a precedence level. 
  | You can change the order of evaluation by using parentheses.

So, theoretically, it should process my query, with the parentheses intact.  But it is not.  What am I doing wrong?  or is there another way of doing this?

Thanks,
Andy


View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4054127#4054127

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4054127