Damian Majer ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=712020%... ) *updated* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiMjE5OWU5Yzgw... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16928?atlOrigin=eyJpIjoiMjE5OW... ) HHH-16928 ( https://hibernate.atlassian.net/browse/HHH-16928?atlOrigin=eyJpIjoiMjE5OW... ) Subquery in 'where' clause causes additional 'join' creation in SQM ( https://hibernate.atlassian.net/browse/HHH-16928?atlOrigin=eyJpIjoiMjE5OW... ) Change By: 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: {code:java}@Entity public class EntityA { @Id private Integer id; @OneToMany private List<EntityB> bCollection; }{code} {code:java}@Entity public class EntityB { @Id private Integer id; @ManyToOne private EntityA a; @ElementCollection private final Set<EntityC> cCollection = new HashSet<>(); }{code} {code:java}@Embeddable public class EntityC { private LocalDateTime date; private Long value; }{code} Query creation: {code:java}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) """);{code} {code:java}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) """);{code} In the first query we receive following result: {noformat}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 ){noformat} There’s the additional inner join to {{c2_0}}, which is never used. In the second query this is the result: {noformat}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 ){noformat} There are not additional joins. First query with Hibernate 5.6.15: {noformat}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{noformat} It doesn’t have any additional inner join. Test case - [https://github.com/damianmajer/hibernate-test-case-templates|https://gith...] I have created same test case in orm-5 and orm-6. In orm-5 it works and it fails in orm-6. ( 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 )