Support subqueries in SELECT lists

[lukasj]

Whilst developing applications using JPA, we have repeatedly found ourselves in a situation where the restriction preventing subqueries from being used in SELECT lists poses a serious limitation, requiring us to work around the problem in one of several ways:

1. Rearrange the query to avoid using a subquery in the SELECT list. Not a problem, but not always possible (e.g. when a count of related entities is required).

2. Issue the subselect as separate queries. Not feasible in many situations as it requires an additional database query for every row returned by the original query. This can have a huge performance impact.

3. Denormalize our database schema so that the required data can be selected from a single table. This is acceptable in some circumstances, and indeed can boost performance for some operations, but in every instance we've come across the query containing the sub-select can be executed efficiently by the database, and it seems somewhat perverse to denormalize a database schema to work around limitations in the ORM API.

4. Resort to native queries. Not pretty. Breaks type safety and can easily lead to database portability issues. Makes problems such as limiting results for pagination and allowing variable sort orders far more complex to implement.

I really do hope that the next release of the JPA specification resolves this issue, as I believe that this restriction poses a serious limitation.

[lukasj]

@glassfishrobot Commented
Reported by dPiergies

[lukasj]

@glassfishrobot Commented
This issue was imported from java.net JIRA JPA_SPEC-107

[lukasj]

• Issue Imported From: https://github.com/javaee/jpa-spec/issues/107
• Original Issue Raised By:@glassfishrobot
• Original Issue Assigned To: @ldemichiel