JPQL query with JOIN ... ON clause + sub query results in exception "Internal problem encountered while compiling" using EclipseLink

Question

I have a JPQL query with a JOIN ... ON ... clause, which is valid since JPA 2.1:

SELECT NEW net.bbstats.dto.CompetitionListDto(
  gl.name,
  se.startYear,
  -1,
  cm.level,
  cml.code,
  cml.name,
  tt.code,
  tt.ageGroup,
  tt.gender,
  cm.type,
  -1
)
FROM Group gr
  LEFT JOIN gr.groupLabel gl
  LEFT JOIN gr.groupMembers gm
  LEFT JOIN gm.roster ro
  JOIN gr.round rd
  JOIN rd.season se
  JOIN rd.competition cm
  JOIN cm.teamType tt
  JOIN cm.geoContext gc
  JOIN cm.competitionLabels cml ON cml.seasonStartYear = SELECT MAX(lbl.seasonStartYear)
                                                          FROM CompetitionLabel lbl
                                                            JOIN lbl.competition cmp
                                                            JOIN cmp.geoContext geo
                                                            JOIN cmp.teamType tet
                                                          WHERE geo.id = ge.id
                                                            AND tet.code = tt.code
                                                            AND cmp.type = cm.type
                                                            AND cmp.level = cm.level
                                                            AND lbl.seasonStartYear <= :seasonStartYear
WHERE se.startYear = :seasonStartYear
GROUP BY rd.id, gl.code, cml.code, cml.name
ORDER BY se.startYear, gc.type, gc.name, cm.type, tt.ageGroup DESC, tt.gender DESC, rd.id, gl.code

I get an exception:

Exception Description: Unable to deploy PersistenceUnit [BBStatsPU] in invalid state [DeployFailed].
Internal Exception: javax.persistence.PersistenceException: Exception [EclipseLink-28019] (Eclipse Persistence Services - 2.7.5.v20191016-ea124dd158): org.eclipse.persistence.exceptions.EntityManagerSetupException
Exception Description: Deployment of PersistenceUnit [BBStatsPU] failed. Close all factories for this PersistenceUnit.
Internal Exception: Exception [EclipseLink-0] (Eclipse Persistence Services - 2.7.5.v20191016-ea124dd158): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Internal problem encountered while compiling [
            SELECT NEW net.bbstats.dto.CompetitionListDto(
              gl.name,
              se.startYear,
              -1,
              cm.level,
              cml.code,
              cml.name,
              tt.code,
              tt.ageGroup,
              tt.gender,
              cm.type,
              -1
            )
            FROM Group gr
              LEFT JOIN gr.groupLabel gl
              LEFT JOIN gr.groupMembers gm
              LEFT JOIN gm.roster ro
              JOIN gr.round rd
              JOIN rd.season se
              JOIN rd.competition cm
              JOIN cm.teamType tt
              JOIN cm.geoContext gc
              JOIN cm.competitionLabels cml ON cml.seasonStartYear = SELECT MAX(lbl.seasonStartYear)
                                                                      FROM CompetitionLabel lbl
                                                                        JOIN lbl.competition cmp
                                                                        JOIN cmp.geoContext geo
                                                                        JOIN cmp.teamType tet
                                                                      WHERE geo.id = ge.id
                                                                        AND tet.code = tt.code
                                                                        AND cmp.type = cm.type
                                                                        AND cmp.level = cm.level
                                                                        AND lbl.seasonStartYear <= :seasonStartYear
            WHERE se.startYear = :seasonStartYear
            GROUP BY rd.id, gl.code, cml.code, cml.name
            ORDER BY se.startYear, gc.type, gc.name, cm.type, tt.ageGroup DESC, tt.gender DESC, rd.id, gl.code
        ].
Internal Exception: java.lang.NullPointerException
    at org.eclipse.persistence.internal.jpa.EntityManagerSetupImpl.deploy(EntityManagerSetupImpl.java:634)
    at org.eclipse.persistence.internal.jpa.EntityManagerFactoryDelegate.getAbstractSession(EntityManagerFactoryDelegate.java:222)
    at org.eclipse.persistence.internal.jpa.EntityManagerFactoryDelegate.createEntityManagerImpl(EntityManagerFactoryDelegate.java:330)
    at org.eclipse.persistence.internal.jpa.EntityManagerFactoryImpl.createEntityManagerImpl(EntityManagerFactoryImpl.java:350)
    at org.eclipse.persistence.internal.jpa.EntityManagerFactoryImpl.createEntityManager(EntityManagerFactoryImpl.java:313)
    at org.jboss.as.jpa.container.TransactionScopedEntityManager.createEntityManager(TransactionScopedEntityManager.java:187)
    at org.jboss.as.jpa.container.TransactionScopedEntityManager.getOrCreateTransactionScopedEntityManager(TransactionScopedEntityManager.java:157)
    at org.jboss.as.jpa.container.TransactionScopedEntityManager.getEntityManager(TransactionScopedEntityManager.java:87)
    at org.jboss.as.jpa.container.AbstractEntityManager.createNamedQuery(AbstractEntityManager.java:98)
    at net.bbstats.framework.service.Repository.findByNamedQuery(Repository.java:173)
    at net.bbstats.framework.service.BaseEntityService.findByNamedQuery(BaseEntityService.java:467)
    at net.bbstats.framework.service.BaseEntityService.findByNamedQuery(BaseEntityService.java:453)
    at net.bbstats.framework.service.BaseEntityService.findByNamedQuery(BaseEntityService.java:429)
    at net.bbstats.framework.service.BaseEntityService.findAllWithFetchGraph(BaseEntityService.java:385)
    ... 192 more

Looking at the JPQL BNF of the JPA 2.1 spec, see https://download.oracle.com/otn-pub/jcp/persistence-2_1-fr-eval-spec/JavaPersistence.pdf?#G4.1537490, the JOIN ... ON ... doesn't appear to really allow a sub query on the right-hand side of the join_condition if further evaluated:

QUESTION:

Is my assumption correct?

If so, how would I then solve my problem at hand using standard JPA + EclipseLink without a native query (which would be the obvious alternative)?

To explain: the sub query takes a season start year as argument, say 2010, and will then determine the latest name/label of the competition, which might have been inserted in 2005. For example, a competition like the German Basketball Bundesliga might change sponsors every X years, so the name might be "Beko Basketball Bundesliga" in 2010 and will be "easycredit BBL" starting with 2014 etc.

Short version: "Get the MAX label entity of all before the passed :seasonStartYear"

BTW: the above query works using Hibernate. EclipseLink version is 2.7.5, see stacktrace.

Answer 1

EclipseLink simply does not support this. Since you are using an inner join you could actually also use the subquery in the WHERE clause like this:

SELECT NEW net.bbstats.dto.CompetitionListDto(
  gl.name,
  se.startYear,
  -1,
  cm.level,
  cml.code,
  cml.name,
  tt.code,
  tt.ageGroup,
  tt.gender,
  cm.type,
  -1
)
FROM Group gr
  LEFT JOIN gr.groupLabel gl
  LEFT JOIN gr.groupMembers gm
  LEFT JOIN gm.roster ro
  JOIN gr.round rd
  JOIN rd.season se
  JOIN rd.competition cm
  JOIN cm.teamType tt
  JOIN cm.geoContext gc
  JOIN cm.competitionLabels cml
WHERE se.startYear = :seasonStartYear
AND cml.seasonStartYear = (SELECT MAX(lbl.seasonStartYear)
                                                          FROM CompetitionLabel lbl
                                                            JOIN lbl.competition cmp
                                                            JOIN cmp.geoContext geo
                                                            JOIN cmp.teamType tet
                                                          WHERE geo.id = ge.id
                                                            AND tet.code = tt.code
                                                            AND cmp.type = cm.type
                                                            AND cmp.level = cm.level
                                                            AND lbl.seasonStartYear <= :seasonStartYear)
GROUP BY rd.id, gl.code, cml.code, cml.name
ORDER BY se.startYear, gc.type, gc.name, cm.type, tt.ageGroup DESC, tt.gender DESC, rd.id, gl.code