OpenJPA
  1. OpenJPA
  2. OPENJPA-1494

Criteria query IN predicate generates incorrect SQL

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.0.0-beta
    • Fix Version/s: 2.0.0-beta2
    • Component/s: query
    • Labels:
      None

      Description

      Junit regression uncovered following problems:
      1.a testcase error in TestTypesafeCriteria.testValues5() : this test is currently annotated @AllowFailure.
      the expected sql string is incorrect.
      String sql = "SELECT t0.name, t2.id, t2.label FROM CR_ITEM t0 "
      + "INNER JOIN CR_ITEM_photos t1 ON t0.id = t1.ITEM_ID "
      + "INNER JOIN CR_PHT t2 ON t1.VALUE_ID = t2.id WHERE "
      + "(0 = (SELECT COUNT FROM CR_ITEM_photos t3 WHERE "
      + "(t3.VALUE_ID = ? OR t3.VALUE_ID = ? OR t3.VALUE_ID = ? OR t3.VALUE_ID = ? OR t3.VALUE_ID = ?) "
      + "AND (t0.id = t3.ITEM_ID) AND t0.id = t3.ITEM_ID) "
      + "AND 0 < (SELECT COUNT FROM CR_ITEM_photos WHERE CR_ITEM_photos.ITEM_ID = t0.id))";

      in the last SELECT COUNT, the FROM table does not have table alias assigned.
      2. NOT IN expression is transformed into a QueryExpression which resulting SQL subselects with SELECT COUNT – there could be a problem in how query expression tree is built in Criteria Query for IN-Expression.

      3: ((CriteriaQueryImpl)q).toCQL() produces the following JPQL string which has syntax error.
      JPQL=SELECT i.name, i.photos FROM Item i INNER JOIN i.photos ? WHERE i.photos IN ([org.apache.openjpa.persistence.criteria.Photo@22de22de, org.apache.openjpa.persistence.criteria.Photo@23122312, org.apache.openjpa.persistence.criteria.Photo@23462346, org.apache.openjpa.persistence.criteria.Photo@226e226e, org.apache.openjpa.persistence.criteria.Photo@22aa22aa])

      if toCQL() produced the following JPQL, then semantically they would be equavilence:
      SELECT i.name, p FROM Item i INNER JOIN i.photos p where p NOT IN ?1

      but JPQL BNF does not allow Object-value 'p' in [NOT] IN conditional expression.

      4. There is no JPQL equivalence query for the criteria query in testValues5().
      a closer JPQL string could be:
      SELECT i.name, p FROM Item i INNER JOIN i.photos p where p.id NOT IN ?1
      where ?1 is a collection-valued-parameter that contains a list of Photo IDs.
      The above JPQL generates following SQL:
      8500 test TRACE [main] openjpa.Query - Executing query: [SELECT i.name, p FROM Item i INNER JOIN i.photos p WHERE p.id not IN ?1] with parameters:

      {1=[0, 0, 0, 0, 0]}

      8750 test TRACE [main] openjpa.jdbc.SQL - <t 108529272, conn 1628201228> executing prepstmnt 1532713819 SELECT t0.name, t2.id, t2.label FROM CR_ITEM t0 INNER JOIN CR_ITEM_photos t1 ON t0.id = t1.ITEM_ID INNER JOIN CR_PHT t2 ON t1.VALUE_ID = t2.id WHERE (NOT (t1.VALUE_ID IN (?, ?, ?, ?, ?))) [params=(int) 0, (int) 0, (int) 0, (int) 0, (int) 0]

      as shown in the above, a NOT IN JPQL generated a NOT IN SQL.

        Activity

        No work has yet been logged on this issue.

          People

          • Assignee:
            Fay Wang
            Reporter:
            Catalina Wei
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development