Design Specification: Enhanced JPQL
|Date||Author||Version Description & Notes|
JPQL currently offers a sub-set of SQL functionality. Some queries that are possible using SQL cannot be defined using JPQL.
It is desired to make EclipseLink's JPQL support be more complete of what is possible in SQL.
A key requirement from users (#1 voted enhancement) is to have ON clause support in JPQL.
Other missing features of SQL could also be added to JPQL, such as sub selects in the SELECT from FROM clauses, and enhanced function support.
Other JPQL enhancements:
- BUG#243698 - allow qualified class names in from clause
- BUG#293775 - allow alias on join fetch
- BUG#315087 - support for cast function
- BUG#333645 - group by fails in in
- BUG#276147 - support union and intersect
- BUG#305187 - support platform independent functions and custom operators
- BUG#321722 - support nested join fetches
- BUG#321724 - support connect by
- BUG#321725 - support common functions
- BUG#350597 - JPA 2.1 sub select in functions, having, select, from
- support IN with nested arrays
- support literal key word
- support table and column references
- support asOf
- BUG#246356 - support inner joins in from clause
Other JPQL bugs:
- BUG#346729 - incorrect SQL for distinct
- BUG#331124 - join to element collection does not work
- BUG#331969 - join ignored
- BUG#347562 - group by ignored 
- BUG#300625 - duplicate join in sub select
- BUG#267767 - package with order fails to parse
- BUG#301905 - stack overflow parsing query
- BUG#320541 - join of map fails
- BUG#326848 - plus not aliasable
- BUG#327848 - sub-string fails
- BUG#328378 - element collection fails with in
- BUG#245652 - count distinct fails
- BUG#259867, 243384, 314025 - bad sql for in  
- BUG#301741 - jpql sub selects incorrect
- BUG#307412 - member of fails
- BUG#354344 - concat fails in subquery
- BUG#303268 - expression order in jpql causes error
- BUG#303767 - join fetch ignored
- BUG#357843 - dot removes outer join
- (fixed - Hermes) - BUG#275449 - is not null should be used
- (fixed - Hermes) - from clause alias not used in select clause or where clause is omitted
- object parameters on left
- BUG#362063 - Nested subselects
ON clause : the SQL clause part of the FROM clause that defines how to tables are joined, this can be used for both joins and outer joins, but is required for outer joins.
outer join : A join where if a row in the source table has no joined rows in the target table it is still included in the join result with a null row for the target table.
ANTRL : Third party library currently used in EclipseLink for parsing JPQL.
SQL : SQL 2003 BNF
Hermes : New JPQL parser developed by Dali project for parsing JPQL at design time. Currently included in EclipseLink SVN, but not currently used.
- Support additional ON clause for a relationship join.
- Support an ON clause on the join for two independent objects.
- Support JPA 2.1 ON clause
- Support JPA 2.1 function key word
- Allow sub-selects in the select clause, having clause, and from clause. ("optionalish" in JPA 2.1)
- Outer join capabilities differ in different databases.
- SQL capabilities differ in different databases.
- Extend ObjectExpression to support an onClause, must normalize and copy expression accordingly.
- Allow ExpressionBuilders and other Expressions to be added to nonFetchJoinExpressions in ObjectLevelReadQuery.
- Add support for having SubSelectExpressions in SQLSelectStatement's fields and print/normalize accordingly.
- Added new FromSubSelectExpression to allow SubSelectExpressions to be contained an aliased in a SQLSelectStatement's from clause.
- Added new FromAliasExpression to allow get() to be used from a FromSubSelectExpression and treated as a QueryKeyExpression aliasing one of the items in the sub-select.
- Added support to FunctionExpression and RelationExpression to support object comparisons with IN. For composite ids as parameters arrayed INs are used, this requires database specific support.
- Added support for count distinct with composite ids for MySQL and supporting databases.
- Support ON clause for join and allow joins of non related entities.
- Support sub-selects in the SELECT, HAVING, and FROM clause.
- Support IN with object expressions.
- Support ORDER BY with object expressions.
- Support = with aliases.
- Support alias on JOIN FETCH.
- Support FUNCTION key word.
- Support OPERATOR key word to allow usage of any ExpressionOperator.
- Support CONNECT BY PRIOR in FROM clause.
- Support AS OF in FROM clause.
- Support BATCH FETCH in FROM clause.
ON clause needs Expression tests in core testing and JPQL tests in JPA testing. Testing both 1-1, 1-m, relationship and parallel.
- Added ON clause tests to Expression outerJoin core tests (both inner and outer joins tested).
- Added FROM and SELECT sub-select tests to Expression subSelects core tests.
- Select e from Employee e left join e.address a on (a.city = 'Ottawa')
- Select e from Employee e left join Project p on (p.teamLeader = e)
- Select e.id, (Select a.city from Address a where e.address = a) from Employee e
- Select e.id, a2.city from Employee e, (Select a.city from Address a) a2
- IN / NOT IN
- Select e from Employee e where e.address in (Select a from Address a where city = 'Ottawa')
- Select e from Employee e join e.phones p where p in :phones
- Select e from Employee e join e.phones p where p in (:phone1, :phone2)
- Select e from Employee e where e.address = (Select a from Address a where street = '27 Hastings')
- ORDER BY (order by objects, Id or all fields for embeddables)
- Select e from Employee e order by e.address
- Select e from Employee e order by e.period
- JOIN FETCH alias
- Select e from Employee e join fetch e.address a where a.city = :city order by a.street
- Select e from Employee e join fetch e.phones p order by p.areaCode
- Select e from Employee e join fetch e.manager m join fetch m.address
- Select e.salary, (Select Max(e2.salary from Employee e2 where e2.address.city = e.address.city) from Employee e
- Select e.id, c.city from Employee e, (Select a.city from Address a) c
- join(Expression target, Expression onClause)
- leftJoin(Expression target, Expression onClause)
- getAlias(Expression subSelect)
// ON ExpressionBuilder employee = new ExpressionBuilder(); Expression address = employee.getAllowingNull("address"); employee.leftJoin(address, address.get("city").equal("Ottawa")); ReadAllQuery query = new ReadAllQuery(Employee.class, employee); query.addNonFetchJoin(address); // ON ExpressionBuilder employee = new ExpressionBuilder(Employee.class); ExpressionBuilder project = new ExpressionBuilder(Project.class); employee.join(project, project.get("teamLeader").equal(employee)); ReadAllQuery query = new ReadAllQuery(Employee.class, employee); query.addNonFetchJoin(project); // SELECT SUB-SELECT ExpressionBuilder employee = new ExpressionBuilder(Employee.class); ExpressionBuilder address = new ExpressionBuilder(Address.class); ReportQuery subQuery = new ReportQuery(Address.class, address); subQuery.addAttribute("city"); subQuery.setSelectionCriteria(employee.get("address").equal(a)); ReportQuery query = new ReportQuery(Employee.class, employee); query.addAttribute("id"); query.addItem("city", employee.subQuery(subQuery)); // FROM SUB-SELECT ExpressionBuilder employee = new ExpressionBuilder(Employee.class); ExpressionBuilder address = new ExpressionBuilder(Address.class); ReportQuery subQuery = new ReportQuery(Address.class, address); subQuery.addAttribute("city"); subQuery.setSelectionCriteria(employee.get("address").equal(a)); Expression a2 = employee.getAlias(employee.subQuery(subQuery)); ReportQuery query = new ReportQuery(Employee.class, employee); query.addNonFetchJoin(a2); query.addAttribute("id"); query.addItem("city", a2.get("city"));
Need to document our BNF, example queries and support beyond JPA 2.0/2.1 in query section.
|Issue #||Owner||Description / Notes|
|1||Which JPQL parser should be used ANTLR or Hermes?|
|Issue||Description / Notes||Decision|
- Other JPQL enhancements.
- Criteria support.