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=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100224- sha1:07bd2b7 )