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
{code:java}@Entity
@Table(name = "REFERENCED_ENTITY")
public class ReferencedEntity {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "ID")
Integer id;
@Column(name = "foo")
Integer foo;
}{code}
{code:java}@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;
}{code}
{code:java}@Entity
@Table(name = "ENTITY_B")
public class EntityB {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "ID")
Integer id;
@Column(name = "REF_NUMBER")
Integer reference;
}{code}
{code:java} @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);
}
}{code}
The generated SQL is wrong since {{e3_0}} has no reference to root query and the test fails:
{code:sql} 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
){code}
Changing the HQL to use the join alias from root query works fine:
{code:java} 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);
}{code}
I decided to report this case since the original HQL works fine on latest hibernate 5.6 series
Test case to be attached and available at [https://github.com/ratoaq2/HHH-16537|https://github.com/ratoaq2/HHH-16537|smart-link] |
|
