Given
- Join with alias in root query
- sub-query using same join path but not using the defined alias
When
Then
@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 |