Message Title

In queries, we dereference implicit joins for primary keys where possible. The same can however be done for natural keys (mapped to the referencedColumnName of a JoinColumn) as well.

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

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @NaturalId
    @Column(name = "isbn", unique = true)
    private String isbn;

}

@Entity(name = "BookRef")
@Table(name = "bookref")
public static class BookRef {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @ManyToOne(optional = true)
    @JoinColumn(nullable = true, columnDefinition = "id_ref")
    private Book normalBook;

    @ManyToOne
    @JoinColumn(name = "isbn_ref", referencedColumnName = "isbn")
    private Book naturalBook;

}

@Entity(name = "BookRefRef")
@Table(name = "bookrefref")
public static class BookRefRef {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @ManyToOne
    @JoinColumn(nullable = true, columnDefinition = "id_ref_ref")
    private BookRef normalBookRef;

    @ManyToOne
    @JoinColumn(name = "isbn_ref_Ref", referencedColumnName = "isbn_ref")
    private BookRef naturalBookRef;
}

SELECT r.naturalBook.isbn FROM BookRef r

Which could be derived from the SQL FROM clause, but actually joins BOOK to get the same column value.

SELECT b.normalBook FROM BookRefRef a JOIN BookRef b ON b.naturalBook.isbn = a.naturalBookRef.naturalBook.isbn

Due to the implicit join in the on clause, this query currently fails with an exception. With the optimisation, this query would produce two SQL joins.

SELECT r2.normalBookRef.normalBook.id, r3.naturalBookRef.naturalBook.isbn FROM BookRefRef r2 JOIN BookRefRef r3 ON r2.normalBookRef.normalBook.isbn = r3.naturalBookRef.naturalBook.isbn

Without the optimisation, this query produces 5 joins. With the optimisation, this query produces 3 joins.