Hibernate 6迁移后HQL左连接WHERE子句SQL转换错误问题

将Hibernate从5.4.30.Final版本升级到6.2.0.Final后，部分HQL查询的SQL转换出现错误，导致查询结果不符合预期。以下是最小复现场景：

实体类定义（省略getter/setter） #

@Entity
public class Author {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    private String name;
}

@Entity
public class PublishingHouse {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    private String name;
}

Book实体包含指向Author和PublishingHouse的多对一关联：

@Entity
public class Book {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    private String title;
    
    @ManyToOne
    private Author author;
    
    @ManyToOne
    private PublishingHouse publishingHouse;
}

测试代码与异常结果 #

在Main方法中插入测试数据后，执行HQL查询所有作者为Stephen King或出版社为Simon & Schuster的书籍：

public static void main(String[] args) {
    Transaction transaction = null;
    PublishingHouse phSimonSchuster = null;
    Author stephenKing = null;
    
    try (Session session = HibernateUtil.getSessionFactory().openSession()) {
        transaction = session.beginTransaction();
        
        // 插入出版社数据
        phSimonSchuster = new PublishingHouse();
        phSimonSchuster.setName("Simon & Schuster");
        session.persist(phSimonSchuster);
        
        var phHarperCollins = new PublishingHouse();
        phHarperCollins.setName("HarperCollins");
        session.persist(phHarperCollins);
        
        // 插入作者数据
        stephenKing = new Author();
        stephenKing.setName("Stephen King");
        session.persist(stephenKing);
        
        var hpLovecraft = new Author();
        hpLovecraft.setName("Howard Phillips Lovecraft");
        session.persist(hpLovecraft);
        
        // 插入书籍数据
        var bShining = new Book();
        bShining.setTitle("Shining");
        bShining.setAuthor(stephenKing);
        bShining.setPublishingHouse(phSimonSchuster);
        session.persist(bShining);
        
        var bColorOutOfSpace = new Book();
        bColorOutOfSpace.setTitle("The Colour Out of Space");
        bColorOutOfSpace.setAuthor(hpLovecraft);
        bColorOutOfSpace.setPublishingHouse(phHarperCollins);
        session.persist(bColorOutOfSpace);
    
        transaction.commit();
    }
    
    try (Session session = HibernateUtil.getSessionFactory().openSession()) {
        var hql = "SELECT book "
                + "FROM Book book "
                + "   LEFT JOIN book.author author "
                + "        WITH author.id = :stephenKingId "
                + "   LEFT JOIN book.publishingHouse publishingHouse "
                + "        WITH publishingHouse.id = :simonSchusterId "
                + "WHERE author IS NOT NULL OR publishingHouse IS NOT NULL ";
        
        List<Book> books = session.createQuery(hql, Book.class)
                .setParameter("stephenKingId", stephenKing.getId())
                .setParameter("simonSchusterId", phSimonSchuster.getId())
                .list();
        books.forEach(b -> {
            System.out.println("Book found: " + b.getTitle());
        });
    }
}

实际输出结果 #

Book found: Shining
Book found: The Colour Out of Space

根据插入的数据逻辑，《The Colour Out of Space》的作者并非Stephen King，出版社也不是Simon & Schuster，本不应被查询出来。

错误SQL分析 #

Hibernate将上述HQL转换为了错误的SQL：

SELECT b1_0.id,
       b1_0.author_id,
       b1_0.publishinghouse_id,
       b1_0.title
FROM   book b1_0
       LEFT JOIN author a1_0
              ON a1_0.id = b1_0.author_id
             AND b1_0.author_id = ?
       LEFT JOIN publishinghouse p1_0
              ON p1_0.id = b1_0.publishinghouse_id
             AND b1_0.publishinghouse_id = ?
WHERE  b1_0.author_id IS NOT NULL
   OR b1_0.publishinghouse_id IS NOT NULL 

错误点：

• WHERE条件判断的是Book实体的author_id和publishinghouse_id是否非空，而非左连接后的关联实体是否存在
• WITH子句的条件被错误转换为判断Book的外键值，而非关联实体的ID

正确SQL示例 #

符合预期的SQL应该是：

SELECT b1_0.id,
       b1_0.author_id,
       b1_0.publishinghouse_id,
       b1_0.title
FROM   book b1_0
       LEFT JOIN author a1_0
              ON a1_0.id = b1_0.author_id
             AND a1_0.id = ?
       LEFT JOIN publishinghouse p1_0
              ON p1_0.id = b1_0.publishinghouse_id
             AND p1_0.id = ?
WHERE a1_0.id IS NOT NULL OR p1_0.id IS NOT NULL 

该SQL可以正确筛选出作者为Stephen King或出版社为Simon & Schuster的书籍。此问题在Hibernate 6.0以下版本不会出现。

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