Subqueries in JPA is not working with OpenJPA

[Green Beret]

Does JPA support subqueries in the select part of the main query.

For example :

SELECT record1.txnId, record1.accessTime, (SELECT record2.accessTime FROM Metering record2 WHERE record2.txnId=record1.txnId AND record2.action='END') FROM Metering record1 WHERE record1.action='START'

The above query is properly working with Hibernate implementation. But when i use OpenJPA implementation instead of Hibernate implementation it is giving the following error:

org.springframework.dao.InvalidDataAccessApiUsageException: Encountered "(" at character 44, but expected: ["ABS", "AVG", "CONCAT", "COUNT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "LENGTH", "LOCATE", "LOWER", "MAX", "MIN", "MOD", "NEW", "OBJECT", "SIZE", "SQRT", "SUBSTRING", "SUM", "TRIM", "UPPER", <IDENTIFIER>].; nested exception is <openjpa-1.2.1-r752877:753278 nonfatal user error> org.apache.openjpa.persistence.ArgumentException: Encountered "(" at character 44, but expected: ["ABS", "AVG", "CONCAT", "COUNT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "LENGTH", "LOCATE", "LOWER", "MAX", "MIN", "MOD", "NEW", "OBJECT", "SIZE", "SQRT", "SUBSTRING", "SUM", "TRIM", "UPPER", <IDENTIFIER>].

[James Sutherland]

This is not supported by JPA. Perhaps use a native query.

Green Beret wrote:

Does JPA support subqueries in the select part of the main query.

For example :

SELECT record1.txnId, record1.accessTime, (SELECT record2.accessTime FROM Metering record2 WHERE record2.txnId=record1.txnId AND record2.action='END') FROM Metering record1 WHERE record1.action='START'

The above query is properly working with Hibernate implementation. But when i use OpenJPA implementation instead of Hibernate implementation it is giving the following error:

org.springframework.dao.InvalidDataAccessApiUsageException: Encountered "(" at character 44, but expected: ["ABS", "AVG", "CONCAT", "COUNT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "LENGTH", "LOCATE", "LOWER", "MAX", "MIN", "MOD", "NEW", "OBJECT", "SIZE", "SQRT", "SUBSTRING", "SUM", "TRIM", "UPPER", <IDENTIFIER>].; nested exception is <openjpa-1.2.1-r752877:753278 nonfatal user error> org.apache.openjpa.persistence.ArgumentException: Encountered "(" at character 44, but expected: ["ABS", "AVG", "CONCAT", "COUNT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "LENGTH", "LOCATE", "LOWER", "MAX", "MIN", "MOD", "NEW", "OBJECT", "SIZE", "SQRT", "SUBSTRING", "SUM", "TRIM", "UPPER", <IDENTIFIER>].