OpenJPA
  1. OpenJPA
  2. OPENJPA-1654

Details of an ArgumentException if SUBSTRING() IN (...) is used

    Details

    • Type: Improvement Improvement
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 1.2.2
    • Fix Version/s: None
    • Component/s: None
    • Environment:
      IBM WebSphere 7.0.0.9 on Windows XP uses OpenJPA 1.2.3 in com.ibm.ws.jpa.jar

      Description

      Everything works fine with the following two statements

      SQL: select * from table where substr(field,2,1)='6';
      JPA: SELECT o FROM Table o WHERE SUBSTRING(o.field,2,1)='6'

      but not with these

      SQL: select * from table where substr(field,2,1) in ('6','7');
      JPA: SELECT o FROM Table o WHERE SUBSTRING(o.field,2,1) IN ('6','7')

      SQL works as expected but JPA throws an exception

      org.apache.openjpa.persistence.ArgumentException "SUBSTRING ( o . field , 2 , 1 ) IN" gefunden, erwartet wurde jedoch ["(", ")", "*", "+", ",", "-", ".", "/", ":", "<", "<=", "<>", "=", ">", ">=", "?", "ABS", "ALL", "AND", "ANY", "AS", "ASC", "AVG", "BETWEEN", "BOTH", "BY", "CONCAT", "COUNT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "DELETE", "DESC", "DISTINCT", "EMPTY", "ESCAPE", "EXISTS", "FETCH", "FROM", "GROUP", "HAVING", "IN", "INNER", "IS", "JOIN", "LEADING", "LEFT", "LENGTH", "LIKE", "LOCATE", "LOWER", "MAX", "MEMBER", "MIN", "MOD", "NEW", "NOT", "NULL", "OBJECT", "OF", "OR", "ORDER", "OUTER", "SELECT", "SET", "SIZE", "SOME", "SQRT", "SUBSTRING", "SUM", "TRAILING", "TRIM", "UPDATE", "UPPER", "WHERE", <BOOLEAN_LITERAL>, <DECIMAL_LITERAL>, <IDENTIFIER>, <INTEGER_LITERAL>, <STRING_LITERAL>].

      ejb-3_0-fr-spec-persistence.pdf "4.6.8 In Expressions" tells us

      in_expression ::= state_field_path_expression [NOT] IN ( in_item

      {, in_item}

      * | subquery)

      "state_field_path_expression" is defined at "4.4.4 Path Expressions".
      As far as I understand, the result of SUBSTRING() is maybe not a "state_field_path_expression" and therefore the exception is not a bug.
      But the error message could be improved. Because it tells us that IN is allowed in this case - see the list of allowed terms at the ArgumentException above.

      I would like to send a Query like "SELECT o FROM Table o WHERE SUBSTRING(o.field,2,1) IN ('6','7')" but if this is not allowed I would like to read an error message which guides me to the right direction.

      Thanks, Hannes

        Activity

          People

          • Assignee:
            Unassigned
            Reporter:
            Hannes Reisinger
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:

              Development