I discovered this issue during migration to Spring Boot 3 and Hibernate 6.2 The problem is that Hibernate 6 generates invalid native SQL and the SQL is correct when using Hibernate 5
We have the following entity which has a reference to the {{parent}} class(abstract class)
{noformat} @Entity public class Main {
@Id private Long id;
@OneToMany(mappedBy = "main") private Collection<Parent> parents = new HashSet<>(); }{noformat}
{{Parent}} class
{noformat}@Entity @Inheritance(strategy = InheritanceType.JOINED) public abstract class Parent {
@Id private Long id;
@ManyToOne private Main main; }{noformat}
and implementation of the {{parent}} class
{noformat}@Entity public class Child extends Parent {
@Column private String data; }{noformat}
You will get incorrect native SQL when executing the following query
{{select s from Main s left join s.parents as p left join Child c on c = p where c.data is not null}}
The error
{noformat}org.hibernate.exception.SQLGrammarException: could not prepare statement [Column "C1_1.ID" not found; SQL statement: select m1_0.id from Main m1_0 left join (Parent p1_0 left join Child p1_1 on p1_0.id=p1_1.id) on m1_0.id=p1_0.main_id left join Child c1_0 on c1_1.id=p1_1.id where c1_0.data is not null [42122-220]] [select m1_0.id from Main m1_0 left join (Parent p1_0 left join Child p1_1 on p1_0.id=p1_1.id) on m1_0.id=p1_0.main_id left join Child c1_0 on c1_1.id=p1_1.id where c1_0.data is not null]
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:64) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:56) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:187) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:152){noformat}
This query works fine on Hibernate 5 without issues
The workaround to join entities using ids for example {{left join Child c on c.id = p.id}}
I’ve attached a reproducer. The reproducer in {{ORMStandaloneTestCase}} class |
|