Damian Majer ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=712020%... ) *created* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiNDlkZTQ3Mjk2... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16928?atlOrigin=eyJpIjoiNDlkZT... ) HHH-16928 ( https://hibernate.atlassian.net/browse/HHH-16928?atlOrigin=eyJpIjoiNDlkZT... ) Subquery in 'where' clause causes additional 'join' creation in SQM ( https://hibernate.atlassian.net/browse/HHH-16928?atlOrigin=eyJpIjoiNDlkZT... ) Issue Type: Bug Affects Versions: 6.2.5 Assignee: Unassigned Components: hibernate-core Created: 12/Jul/2023 05:20 AM Environment: Hibernate: 6.2.5 Spring Boot: 3.1.1 JVM: 17 Postgresql: 42.6.0 Priority: Major Reporter: Damian Majer ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=712020%... ) When you create a subquery on entity which is joined then additional inner join is created in SQM query. Entities: @Entity public class EntityA { @Id private Integer id; @OneToMany private List<EntityB> bCollection; } @Entity public class EntityB { @Id private Integer id; @ManyToOne private EntityA a; @ElementCollection private final Set<EntityC> cCollection = new HashSet<>(); } @Embeddable public class EntityC { private LocalDateTime date; private Long value; } Query creation: entityManager.createQuery(""" select distinct a.id, b.id, c.value from EntityA a left join EntityB b on b.a.id = a.id left join b.cCollection as c where (c.date = (select max(c2.date) from b.cCollection as c2) or c.date is null ) """); entityManager.createQuery(""" select distinct b.id, c.value from EntityB b left join b.cCollection as c where (c.date = (select max(c2.date) from b.cCollection as c2) or c.date is null ) """); In the first query we receive following result: select distinct e1_0.id, e2_0.id, c1_0.value from entitya e1_0 left join entityb e2_0 on e2_0.a_id=e1_0.id left join entityb_c_collection c1_0 on e2_0.id=c1_0.entityb_id join entityb_c_collection c2_0 on e2_0.id=c2_0.entityb_id where ( c1_0.date=( select max(c3_0.date) from entityb_c_collection c3_0 where e2_0.id=c3_0.entityb_id ) or c1_0.date is null ) There’s the additional inner join to c2_0 , which is never used. In the second query this is the result: select distinct e1_0.id, c1_0.value from entityb e1_0 left join entityb_c_collection c1_0 on e1_0.id=c1_0.entityb_id where ( c1_0.date=( select max(c2_0.date) from entityb_c_collection c2_0 where e1_0.id=c2_0.entityb_id ) or c1_0.date is null ) There are not additional joins. First query with Hibernate 5.6.15: select distinct entitya0_.id as col_0_0_, entityb1_.id as col_1_0_, ccollectio2_.value as col_2_0_ from entitya entitya0_ left outer join entityb entityb1_ on ( entityb1_.a_id=entitya0_.id ) left outer join entityb_c_collection ccollectio2_ on entityb1_.id=ccollectio2_.entityb_id where ccollectio2_.date=( select max(ccollectio3_.date) from entityb_c_collection ccollectio3_ where entityb1_.id=ccollectio3_.entityb_id ) or ccollectio2_.date is null It doesn’t have any additional inner join. ( https://hibernate.atlassian.net/browse/HHH-16928#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16928#add-comment?atlOrigin=ey... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=Em... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100231- sha1:2d13be9 )