Search code examples
javajpajpqlopenjpa

What is the correct syntax for comparing an attribute to the result of an aggregate selection in JPQL?

My JPQL Query is this:

SELECT blocked 
  FROM BlockedMessage blocked 
  JOIN blocked.resourceDescriptions resource 
 WHERE resource.description IN :resourceDescriptions 
   AND blocked.blockedSince = (SELECT MIN(inner.blockedSince) //error in this line
                                 FROM BlockedMessage inner 
                                 JOIN inner.resourceDescriptions innerResource 
                                WHERE innerResource = resource)

But OpenJPA 2.2.2 gives the error message:

<openjpa-2.2.2-r422266:1468616 nonfatal user error>
org.apache.openjpa.persistence.ArgumentException: 
Encountered "blocked . blockedSince = ( SELECT MIN ( inner" 
at character 143, but expected: 
["(", "*", "+", "-", ".", "/", ":", "<", "<=", "<>", "=", ">", ">=", "?", "ABS", "ALL", "AND", "ANY", "AS", "ASC", "AVG", "BETWEEN", "BOTH", "BY", "CASE", "CLASS", "COALESCE", "CONCAT", "COUNT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "DELETE", "DESC", "DISTINCT", "ELSE", "EMPTY", "END", "ENTRY", "ESCAPE", "EXISTS", "FETCH", "FROM", "GROUP", "HAVING", "IN", "INDEX", "INNER", "IS", "JOIN", "KEY", "LEADING", "LEFT", "LENGTH", "LIKE", "LOCATE", "LOWER", "MAX", "MEMBER", "MIN", "MOD", "NEW", "NOT", "NULL", "NULLIF", "OBJECT", "OF", "OR", "ORDER", "OUTER", "SELECT", "SET", "SIZE", "SOME", "SQRT", "SUBSTRING", "SUM", "THEN", "TRAILING", "TRIM", "TYPE", "UPDATE", "UPPER", "VALUE", "WHEN", "WHERE", <BOOLEAN_LITERAL>, <DATE_LITERAL>, <DECIMAL_LITERAL>, <IDENTIFIER>, <INTEGER_LITERAL>, <STRING_LITERAL2>, <STRING_LITERAL>, <TIMESTAMP_LITERAL>, <TIME_LITERAL>].

I didn't manage to figure out what is wrong using the JPQL language reference.

Solution

  • The problem with that query is that inner is a keyword. However the parser of OpenJPA isn't able to report the incorrect use of that keyword in inner.resourceDescriptions.

    The correct query is:

    SELECT blocked 
  FROM BlockedMessage blocked 
  JOIN blocked.resourceDescriptions resource 
 WHERE resource.description IN :resourceDescriptions 
   AND blocked.blockedSince <= ALL(SELECT MIN(innerBlocked.blockedSince) 
                                     FROM BlockedMessage innerBlocked 
                                     JOIN innerBlocked.resourceDescriptions innerResource 
                                    WHERE innerResource.description = resource.description)