java Hibernate left join fetch到使用@ManyToMany关联映射的softdeleted实体生成无效查询？

Question

我不知道这是Hibernate的问题还是我做错了什么

我试图使这个例子尽可能简单

我的实体：

书。爪哇：

@Entity
@Table(name="books")
@SQLDelete(sql="UPDATE books set is_deleted = true where id = ?")
@Where(clause="is_deleted = false")
public class Book implements Serializable {

    @Id
    @Column(name = "id", updatable = false, nullable = false)
    private long id;

    @Column (name="is_deleted")
    private boolean deleted;

    @Column(name="title")
    private String title;
    
    @ManyToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JoinTable(
            name="books_authors",
            joinColumns = {
                    @JoinColumn(name="id_books")
            },
            inverseJoinColumns = {
                    @JoinColumn(name="id_authors")
            }
    )
    private Set<Author> authors = new HashSet<>();

    //getters/setters
}

作者。爪哇：

@Entity
@Table(name="autors")
@SQLDelete(sql="UPDATE autors set is_deleted = true where id = ?")
@Where(clause="is_deleted = false")
public class Author implements Serializable {

    @Id
    @Column(name = "id", updatable = false, nullable = false)
    private long id;

    @Column(name="name")
    private String name;

    @Column (name="is_deleted")
    private boolean deleted;

    @ManyToMany(mappedBy = "authors", fetch = FetchType.LAZY)
    private Set<Book> books = new HashSet<>();

    //getters/setters
}

基础表：

create table autors (
    id int8 not null,
    is_deleted boolean,
    name varchar(255),
    primary key (id)
); 
   
create table books (
    id int8 not null,
    is_deleted boolean,
    title varchar(255),
    primary key (id)
); 
   
create table books_authors (
    id_books int8 not null,
    id_authors int8 not null,
    primary key (id_books, id_authors)
);
alter table if exists books_authors add constraint FK9xru8ocoxufr5eva6atfyfjxh foreign key (id_authors) references autors;
alter table if exists books_authors add constraint FKx5d5025t9ct5kcbwwxm6tipo foreign key (id_books) references books;

数据：

select * from books;

^{tb1}$

select * from authors;

^{tb2}$

select * from books b
join books_authors ba on b.id = ba.id_books 
join autors a on a.id = ba.id_authors;

^{tb3}$

JPQL：

public Optional<Book> getBookWithSoftDeletedAuthor(long bookId) {
    List<Book> myBooks = em.createQuery(
            "select b from " +
            "Book b " +
            "left join fetch b.authors a " +
            "where b.id = :bookId", Book.class)
            .setParameter("bookId", bookId)
            .getResultList();
    return myBooks.size() > 0 ? Optional.of(myBooks.get(0)) : Optional.empty();
}

坚持。xml:

<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence
             http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd"
             version="2.1">
    <persistence-unit name="WildflyUnit">
        <description> Persistence unit for Wildfly </description>
        <jta-data-source>java:jboss/datasources/WildflyDataSource</jta-data-source>
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQL95Dialect"/> 
            <property name="hibernate.hbm2ddl.auto" value="update" />
            <property name="hibernate.show_sql" value="true" />
            <property name="hibernate.format_sql" value="true" />
            <property name="hibernate.generate_statistics" value="true" />
        </properties>
    </persistence-unit>
</persistence>

来自Wildfly单机版的数据源。xml:

<datasource jndi-name="java:jboss/datasources/WildflyDataSource" pool-name="WildflyDataSource" enabled="true" use-java-context="true" statistics-enabled="true">
    <connection-url>jdbc:postgresql://localhost:5432/TestDB</connection-url>
    <driver>postgresql</driver>
    <security>
        <user-name>wildfly_user</user-name>
        <password>********</password>
    </security>
</datasource>

Hibernate生成如下sql语句：

^{10}pr$

imho Hibernate应生成如下查询：

 select
    book0_.id as id1_1_0_,
    author2_.id as id1_0_1_,
    book0_.is_deleted as is_delet2_1_0_,
    book0_.title as title3_1_0_,
    author2_.is_deleted as is_delet2_0_1_,
    author2_.name as name3_0_1_,
    book0_.id as id_books1_2_0__,
    author2_.id as id_autho2_2_0__ 
from
    books book0_ 
left outer join
    books_authors authors1_ 
        on book0_.id=authors1_.id_books 
left outer join
    authors author2_ 
        on authors1_.id_authors=author2_.id 
        and (
            author2_.is_deleted = false
        ) 
where
    (
        book0_.is_deleted = false
    ) 
    and book0_.id=2

id_author2_2_0__列应来自author2_（作者表），而不是来自author1_（书籍作者表）。因为作者的值为1。id_authors列不为null，hibernate尝试从authors表中获取软删除的实体

我使用Hibernate 5.4.27。决赛