Uploaded image for project: 'OpenJPA'
  1. OpenJPA
  2. OPENJPA-2698

Query cache incorrectly handles parameters for BETWEEN expressions

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 2.2.2
    • Fix Version/s: 2.4.3, 3.0.0
    • Component/s: jdbc, jpa, sql
    • Labels:
      None
    • Patch Info:
      Patch Available

      Description

      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.

        Attachments

          Activity

            People

            • Assignee:
              ilgrosso Francesco Chicchiriccò
              Reporter:
              dazeydev Will Dazey
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: