[jira] [Created] (OPENJPA-2698) Query cache incorrectly handles parameters for BETWEEN expressions

["Will Dazey (JIRA)" <ji...@apache.org>]

Will Dazey created OPENJPA-2698:
-----------------------------------

             Summary: Query cache incorrectly handles parameters for BETWEEN expressions
                 Key: OPENJPA-2698
                 URL: https://issues.apache.org/jira/browse/OPENJPA-2698
             Project: OpenJPA
          Issue Type: Bug
          Components: jdbc, jpa, sql
    Affects Versions: 2.2.2
            Reporter: Will Dazey
            Priority: Minor
             Fix For: 2.2.2


When query cache is enabled: 
<property name="openjpa.jdbc.QuerySQLCache" value="true"/>

If a query containing a BETWEEN expression is executed multiple times with different parameter values, the parameter values can persist across queries.

Example:
//Query1
String jpql2 = "SELECT e FROM Employee e WHERE :baseDate between e.startDate AND e.endDate";
TypedQuery<Employee> q1 = em.createQuery(jpql2, Employee.class);
q1.setParameter("baseDate", new GregorianCalendar(2016, Calendar.JUNE, 1).getTime());
q1.getResultList();

//Query2
TypedQuery<Employee> q2 = em.createQuery(jpql2, Employee.class);
q2.setParameter("baseDate", new GregorianCalendar(2017, Calendar.JUNE, 1).getTime());
q2.getResultList();

Produces the following queries:

Q1:
SELECT t0.id, t0.endDate, t0.hireStatus, t0.isManager, t0.name, t0.startDate, t0.status 
    FROM EMPLOYEE_PQC t0  
    WHERE (? >= t0.startDate AND ? <= t0.endDate) 
[params=(Timestamp) 2016-06-01 00:00:00.0, (Timestamp) 2016-06-01 00:00:00.0]

Q2:
SELECT t0.id, t0.endDate, t0.hireStatus, t0.isManager, t0.name, t0.startDate, t0.status 
    FROM EMPLOYEE_PQC t0
    WHERE (? >= t0.startDate AND ? <= t0.endDate) 
[params=(Timestamp) 2017-06-01 00:00:00.0, (Timestamp) 2016-06-01 00:00:00.0]

As you can see, the cached parameter for the endDate has persisted from the first query's parameters.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)