OpenJPA
  1. OpenJPA
  2. OPENJPA-1719

Prepared SQL cache ordering problem with subqueries.

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.0.0
    • Fix Version/s: 2.0.1, 2.1.0
    • Component/s: None
    • Labels:
      None
    • Patch Info:
      Patch Available

      Description

      I've found what appears to be an ordering issue with subqueries and the prepared SQL cache. The attached patch shows where I think the problem lies and adds a test method that shows the problem.

      To summarize: When the prepared SQL cache is enabled we reorder the parameter values provided by the user. If a query contains named parameters and a subquery which also contains named parameters the placement of the subquery becomes important.

      The following query will work :
      SELECT p FROM Person p WHERE p.id IN (SELECT p1.id FROM Person p1 WHERE p1.lastUpdated >= :date ) AND p.name = :name

      But this one fails with a SQLDataException.
      SELECT p FROM Person p WHERE p.name = :name AND p.id IN (SELECT p1.id FROM Person p1 WHERE p1.lastUpdated >= :date )

      Assuming that the query is executed something like this :
      Query query = em.createQuery(query);
      query.setParameter("name", "mike");
      query.setParameter("date", new java.sql.Date(1005397));

      1. sql-cache-subqordering.diff.txt
        4 kB
        Michael Dick
      2. OpenJPA-trunk_OJ1719.testcase.patch
        2 kB
        Martin Dirichs

        Activity

          People

          • Assignee:
            Catalina Wei
            Reporter:
            Michael Dick
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development