Hibernate 6 generating a subquery for joins causing performance regression

Hibernate ORM
1

Hi,

We are updating from Spring Boot 2 to 3 (Hibernate 5 to Hibernate 6) and have a problem where Hibernate 6 is generating a subquery for joins on fields with multiple @JoinColumn annotations which is causing a performance regression. Our queries have gone from taking milliseconds to seconds.

We are using Oracle Database.

Example test case:

Person:

@Entity
@Table(name = "people")
public class Person {

  @Id
  @JdbcTypeCode(Types.VARCHAR)
  @Column(name = "id")
  private UUID id;

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

  @Column(name = "last_name")
  private String lastName;
}

Book:

@Entity
@Table(name = "books")
public class Book {

  @Id
  @JdbcTypeCode(Types.VARCHAR)
  @Column(name = "id")
  private UUID id;

  @ManyToOne
  @JoinColumn(name = "owner_first_name", referencedColumnName = "first_name", insertable = false, updatable = false)
  @JoinColumn(name = "owner_last_name", referencedColumnName = "last_name", insertable = false, updatable = false)
  private Person owner;
}

JPQL query:

SELECT 1
FROM Person p
JOIN Book b ON b.owner = p

Hibernate 6 generated query:

    select
        1 
    from
        people p1_0 
    join
        books b1_0 
            on (b1_0.owner_first_name, b1_0.owner_last_name) in (select
                p1_0.first_name, p1_0.last_name 
        from
            dual)

As you can see, the join is using an IN clause with a subquery selecting from dual. Hibernate 5 did not do this.

Interestingly, if I comment out one of the @JoinColumn annotations, it does not generate the IN clause and the join is generated as expected:

    select
        1 
    from
        people p1_0 
    join
        books b1_0 
            on b1_0.owner_first_name=p1_0.first_name

Is this expected behaviour with Hibernate 6 and is there any way we can avoid it if so, or does it look like a bug?

2

Oracle does not support tuple comparison before version 23 i.e. (b1_0.owner_first_name, b1_0.owner_last_name)=(p1_0.first_name, p1_0.last_name) which is why it is being emulated with the subquery. We could also emulate it differently like in ORM 5, but thought that Oracle was smart enough to figure this out.

Please create a bug ticket in our issue tracker and post the link here as a comment.

3

I took the liberty and created [HHH-18352] - Hibernate JIRA for this.