Jones ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *created* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiM2RjZWYwOGVm... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16537?atlOrigin=eyJpIjoiM2RjZW... ) HHH-16537 ( https://hibernate.atlassian.net/browse/HHH-16537?atlOrigin=eyJpIjoiM2RjZW... ) Wrong SQL generated when root join is not used in sub-query ( https://hibernate.atlassian.net/browse/HHH-16537?atlOrigin=eyJpIjoiM2RjZW... ) Issue Type: Bug Affects Versions: 6.2.2 Assignee: Unassigned Components: query-hql Created: 02/May/2023 02:57 AM Priority: Major Reporter: Jones ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) Given * Join with alias in root query * sub-query using same join path but not using the defined alias When * executing HQL Then * Wrong SQL is generated @Entity @Table(name = "REFERENCED_ENTITY" ) public class ReferencedEntity { @Id @GeneratedValue(strategy = GenerationType.AUTO) @Column(name = "ID" ) Integer id; @Column(name = "foo" ) Integer foo; } @Entity @Table(name = "ENTITY_A" ) public class EntityA { @Id @GeneratedValue(strategy = GenerationType.AUTO) @Column(name = "ID" ) Integer id; @JoinColumn(name = "ENTITY_B" ) @ManyToOne EntityB entityB; } @Entity @Table(name = "ENTITY_B" ) public class EntityB { @Id @GeneratedValue(strategy = GenerationType.AUTO) @Column(name = "ID" ) Integer id; @Column(name = "REF_NUMBER" ) Integer reference; } @Test public void hhhXXXXTest() throws Exception { try (Session s = openSession()) { Transaction tx = s.beginTransaction(); ReferencedEntity ref1 = new ReferencedEntity(); ref1.foo = 1; ReferencedEntity ref2 = new ReferencedEntity(); ref2.foo = 2; s.persist(ref1); s.persist(ref2); EntityA entityA1 = new EntityA(); EntityB entityB1 = new EntityB(); entityA1.entityB = entityB1; entityB1.reference = ref1.id; s.persist(entityB1); s.persist(entityA1); EntityA entityA2 = new EntityA(); EntityB entityB2 = new EntityB(); entityA2.entityB = entityB2; entityB2.reference = ref2.id; s.persist(entityB2); s.persist(entityA2); tx.commit(); } try (Session s = openSession()) { Query<EntityA> query = s.createQuery( "select a from EntityA a join a.entityB ab " // + " where 0 < (select count(*) from ReferencedEntity r where r.foo = 1 and r.id = a.entityB.reference)" , EntityA.class); List<EntityA> actual = query.getResultList(); assertThat(actual).hasSize(1); } } The generated SQL is wrong since e3_0 has no reference to root query and the test fails: select e1_0.ID, e1_0.ENTITY_B from ENTITY_A e1_0 join ENTITY_B e2_0 on e2_0.ID=e1_0.ENTITY_B where 0<( select count (*) from REFERENCED_ENTITY r1_0, ENTITY_B e3_0 where r1_0.foo=1 and r1_0.ID=e3_0.REF_NUMBER ) Changing the HQL to use the join alias from root query works fine: try (Session s = openSession()) { Query<EntityA> query = s.createQuery( "select a from EntityA a join a.entityB ab " // + " where 0 < (select count(*) from ReferencedEntity r where r.foo = 1 and r.id = ab.reference)" , EntityA.class); List<EntityA> actual = query.getResultList(); assertThat(actual).hasSize(1); } I decided to report this case since the original HQL works fine on latest hibernate 5.6 series Test case to be attached ( https://hibernate.atlassian.net/browse/HHH-16537#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16537#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#100224- sha1:07bd2b7 )