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

        Michael Dick made changes -
        Status Resolved [ 5 ] Closed [ 6 ]
        Donald Woods made changes -
        Fix Version/s 2.0.1 [ 12314532 ]
        Patch Info [Patch Available]
        Catalina Wei made changes -
        Status Reopened [ 4 ] Resolved [ 5 ]
        Fix Version/s 2.0.1 [ 12314532 ]
        Resolution Fixed [ 1 ]
        Martin Dirichs made changes -
        Attachment OpenJPA-trunk_OJ1719.testcase.patch [ 12450432 ]
        Catalina Wei made changes -
        Resolution Fixed [ 1 ]
        Status Resolved [ 5 ] Reopened [ 4 ]
        Catalina Wei made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Resolution Fixed [ 1 ]
        Catalina Wei made changes -
        Assignee Catalina Wei [ fancy ]
        Michael Dick made changes -
        Field Original Value New Value
        Attachment sql-cache-subqordering.diff.txt [ 12448882 ]
        Michael Dick created issue -

          People

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

            Dates

            • Created:
              Updated:
              Resolved:

              Development