Hibernate生成SQL时CASE WHEN未正确使用关联别名致SQL Server语法错误

实体类定义 #

EntityA #

@Entity
public class EntityA {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    
    @JoinColumn(updatable=false, name="entityB_id")
    @OneToOne
    private EntityB entityB;

    @OneToMany(mappedBy="entityParent")
    private Set<EntityAggregated> entitiesAggregated = new HashSet<>();
}

EntityB #

@Entity
public class EntityB {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    @OneToOne(mappedBy="entityB")
    private EntityA entityParent;
}

EntityAggregated #

@Entity
public class EntityAggregated {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    @ManyToOne
    private EntityA entityParent;

    private BigDecimal amount;
    
}

问题场景 #

执行包含LEFT JOIN和GROUP BY的HQL查询时，Hibernate生成的SQL在SQL Server中触发语法错误。

预期生成的SQL #

SELECT entityA.id,
      (CASE WHEN entityBAlias IS NOT NULL THEN entityBAlias.id ELSE -1 END),
      SUM(entityAggregated.amount)
FROM EntityA entityA
   LEFT JOIN entityA.entityB entityBAlias
   LEFT JOIN entityA.entitiesAggregated entityAggregated 
GROUP BY entityA.id, entityBAlias.id

实际生成的SQL #

SELECT e1_0.id,
       CASE
         WHEN e1_0.entityb_id IS NOT NULL THEN e2_0.id
         ELSE -1
       END,
       Sum(e3_0.amount)
FROM   entitya e1_0
       LEFT JOIN entityb e2_0
              ON e2_0.id = e1_0.entityb_id
       LEFT JOIN entityaggregated e3_0
              ON e1_0.id = e3_0.entityparent_id
GROUP  BY e1_0.id,
          e2_0.id 

报错信息 #

org.hibernate.exception.SQLGrammarException: JDBC exception executing
SQL [select e1_0.id,case when e1_0.entityB_id is not null then e2_0.id
else -1 end,sum(e3_0.amount) from EntityA e1_0 left join EntityB e2_0
on e2_0.id=e1_0.entityB_id left join EntityAggregated e3_0 on
e1_0.id=e3_0.entityParent_id group by e1_0.id,e2_0.id] [Column
'EntityA.entityB_id' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.]
[n/a]

问题分析与结论 #

问题根源在于Hibernate生成的SQL中，CASE WHEN条件错误使用了主表字段e1_0.entityb_id，而非关联表别名e2_0.id。Hibernate 6.0以下版本可正确生成预期的SQL写法。

若将HQL中的CASE WHEN entityBAlias改为CASE WHEN entityBAlias.id，生成的SQL可正常执行。

这属于Hibernate的bug：当HQL中CASE WHEN使用关联别名entityBAlias时，生成的SQL应针对该关联别名进行非空判断，而非引用主表的外键字段。

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