1 Reply Latest reply on Dec 4, 2008 2:30 PM by Hildingur Mahanti

    parentheses not enforcing evaluation order.

    Andy W. Clements Newbie

      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:

      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