OpenJPA
  1. OpenJPA
  2. OPENJPA-232

concat function in EJB QL doesn't nest

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 0.9.7
    • Fix Version/s: None
    • Component/s: jpa
    • Labels:
      None
    • Environment:
      Using H2 as the target SQL dialect.

      Description

      I'm attempting to do a case insensitive search for a substring within the concatenation of two columns. The EJB QL I came up with looks like this:

      where lower(concat(concat(e.firstname, ' '), e.surname))) like concat(lower(:name), '%')

      however trying to execute that results in the following parse exception.

      Caused by: <0.0.0 nonfatal user error> org.apache.openjpa.persistence.ArgumentException: Encountered "lower ( concat ( concat ( e . firstname , \' \' ) , e . surname ) ) )" at character 32, but expected: ["(", ")", "+", ",", "-", ".", ":", "<", "<=", "<>", "=", ">", ">=", "?", "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>].
      at org.apache.openjpa.kernel.jpql.JPQL.generateParseException(JPQL.java:9322)
      at org.apache.openjpa.kernel.jpql.JPQL.jj_consume_token(JPQL.java:9199)
      at org.apache.openjpa.kernel.jpql.JPQL.conditional_primary(JPQL.java:1895)
      at org.apache.openjpa.kernel.jpql.JPQL.conditional_factor(JPQL.java:1873)
      at org.apache.openjpa.kernel.jpql.JPQL.conditional_term(JPQL.java:1739)
      at org.apache.openjpa.kernel.jpql.JPQL.conditional_expression(JPQL.java:1701)
      at org.apache.openjpa.kernel.jpql.JPQL.where_clause(JPQL.java:1548)
      at org.apache.openjpa.kernel.jpql.JPQL.select_statement(JPQL.java:90)
      at org.apache.openjpa.kernel.jpql.JPQL.parseQuery(JPQL.java:62)
      at org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder$ParsedJPQL.parse(JPQLExpressionBuilder.java:1652)
      ... 50 more

        Issue Links

          Activity

          Hide
          Dan Everton added a comment -

          Sorry, the query is actually

          where lower(concat(e.firstname, ' ', e.surname)) like concat(lower(:name), '%')

          Not sure how the stray ) got in there.

          Show
          Dan Everton added a comment - Sorry, the query is actually where lower(concat(e.firstname, ' ', e.surname)) like concat(lower(:name), '%') Not sure how the stray ) got in there.
          Hide
          Dan Everton added a comment -

          Actually I meant:

          where lower(concat(concat(e.firstname, ' '), e.surname)) like concat(lower(:name), '%')

          Show
          Dan Everton added a comment - Actually I meant: where lower(concat(concat(e.firstname, ' '), e.surname)) like concat(lower(:name), '%')
          Hide
          Milosz Tylenda added a comment -

          Hi Julien, I am afraid this issue might not be a duplicate of OPENJPA-920, rather a related one only. Although the 2nd parameter to LIKE might give an error similar to OPENJPA-920, the stack trace indicates that this is rather the 1st parameter which blows OpenJPA up (the nested CONCATs).

          Show
          Milosz Tylenda added a comment - Hi Julien, I am afraid this issue might not be a duplicate of OPENJPA-920 , rather a related one only. Although the 2nd parameter to LIKE might give an error similar to OPENJPA-920 , the stack trace indicates that this is rather the 1st parameter which blows OpenJPA up (the nested CONCATs).

            People

            • Assignee:
              Unassigned
              Reporter:
              Dan Everton
            • Votes:
              1 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:

                Development