Hibernate 6.2中是否支持CTE之间的关联查询？

问题场景 #

Hibernate 6.2引入了CTE支持，但在单个HQL查询中使用多个CTE并关联时出现错误。现有City实体，编写的HQL查询如下：

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);

执行时抛出错误：

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

对比MySQL官方文档，原生SQL中关联两个CTE是合法的，示例如下：

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;

疑问：Hibernate是否支持CTE之间的关联查询？

解答 #

Hibernate 6.2支持多CTE关联查询，问题出在你的HQL写法上——HQL解析器在JOIN子句中会默认将直接引用的标识符识别为实体类，而非已定义的CTE。以下是几种可行的修正方案：

方案1：将关联的CTE包装为子查询 #

调整HQL写法，把JOIN中的CTE用子查询形式包裹，强制Hibernate将其识别为查询结果集：

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

方案2：将CTE作为FROM子句的子查询源 #

通过FROM子句同时引入两个CTE的子查询形式，用WHERE条件关联：

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

方案3：使用原生SQL查询 #

如果不需要HQL的ORM特性，直接使用createNativeQuery执行原生SQL的CTE语法，完全兼容MySQL的写法：

Query nativeQuery = em.createNativeQuery(
        """
           with max_cities as (
             SELECT id from City c ORDER BY population DESC
             LIMIT 20 
          ), min_cities as (
             SELECT 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);

内容的提问来源于stack exchange，提问作者Sergiy