Joining CTEs in Hibernate 6.2

Question

Hibernate 6.2 introduced CTE support but it's unclear how to use multiple CTEs in the single HQL query. So let's say I have City entity and the following CTE query:

        TypedQuery<Integer> typedQuery = em.createQuery(
        """
           with max_cities as (
             SELECT id id from City c ORDER BY population DESC
             LIMIT 20 
          ), min_cities as (
             SELECT id id from City c ORDER BY population ASC
             LIMIT 20
          )
           SELECT m1.id from max_cities m1 join min_cities m2
              on m1.id = m2.id
        """, 
        Integer.class);

It contains two CTEs (min_cities and max_cities). And Hibernate doesn't complain about that. But the error message is pretty clear here:

Caused by: java.lang.IllegalArgumentException: Could not resolve entity reference: min_cities
    at org.hibernate.metamodel.model.domain.internal.JpaMetamodelImpl.resolveHqlEntityReference(JpaMetamodelImpl.java:166)

So Hibernate allows to use first CTE in FROM clause but expects entity (and not CTE) in the JOIN clause. However MySQL documentation states that joining two CTEs is pretty legal:

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

So is joining CTEs supported in Hibernate ?

Answer 1

That looks like a bug in ExpectingEntityJoinDelegate#consumeIdentifier:

@Override
public void consumeIdentifier(String identifier, boolean isTerminal, boolean allowReuse) {
    if ( path.length() != 0 ) {
        path.append( '.' );
    }
    path.append( identifier );
    if ( isTerminal ) {
        final String fullPath = path.toString();
        final EntityDomainType<?> joinedEntityType = creationState.getCreationContext()
                .getJpaMetamodel()
                .resolveHqlEntityReference( fullPath );
        // here impl assumes that resolveHqlEntityReference may return nulls,
        // however it never does that. Most probably getHqlEntityReference
        // should be used instead
        if ( joinedEntityType == null ) {
            final SqmCteStatement<?> cteStatement = creationState.findCteStatement( fullPath );
            if ( cteStatement != null ) {
                join = new SqmCteJoin<>( cteStatement, alias, joinType, sqmRoot );
                creationState.getCurrentProcessingState().getPathRegistry().register( join );
                return;
            }
            throw new SemanticException( "Could not resolve join path - " + fullPath );
        }

BTW, cross join does work as expected, i.e.:

"""
   with max_cities as (
     SELECT id id from City c ORDER BY population DESC
     LIMIT 20
  ), min_cities as (
     SELECT id id from City c ORDER BY population ASC
     LIMIT 20
  )
   SELECT m1.id from max_cities m1, min_cities m2
      where m1.id = m2.id
"""