# Query uses subselect after updating to 6.4.2

**URL:** <https://discourse.hibernate.org/t/query-uses-subselect-after-updating-to-6-4-2/8890>\
**Category:** Hibernate ORM\
**Created:** [January 22, 2024, 4:48pm UTC](https://discourse.hibernate.org/t/query-uses-subselect-after-updating-to-6-4-2/8890 "2024-01-22T16:48:52Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![mira-silhavy](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mira-silhavy/32/2811_2.png) [@mira-silhavy](https://discourse.hibernate.org/u/mira-silhavy)\
**Post date:** [January 22, 2024, 4:48pm UTC](https://discourse.hibernate.org/t/query-uses-subselect-after-updating-to-6-4-2/8890/1 "2024-01-22T16:48:53Z")

</div>

Hi,  
we’ve been on 6.2.14 and we’re updating to 6.4.2. We have a HQL query that looks like below. Should the simple query like this be translated to subselect instead of simple inner join? Also dirty checking on second table doesn’t work because the querySpace is different for the joined table. It worked fine for hibernate 5 and 6.2.14, with subselect dirty checking is broken.  
In 6.4.2 autoFlush querySpaces are `SITE_AGREEMENT` and second is `(select * from SUPPLIER_PROFILE t where t.TYPE='COMMUNITY')`.  
In 6.2.14 autoFlush querySpaces are `SITE_AGREEMENT` and second `SUPPLIER_PROFILE`.

SUPPLIER\_PROFILE is entity with SINGLE\_TABLE inheritance, one of the types is COMMUNITY.

```auto
select siteAgreementImpl
from SiteAgreementImpl siteAgreementImpl
  inner join siteAgreementImpl.communitySupplierProfile as communitySupplierProfileImpl
where siteAgreementImpl.communitySite = 1 and communitySupplierProfileImpl.businessNumber = "ZZZ"

```

And in Hibernate 6.4.2 this query is translated to

```auto
select ... from SITE_AGREEMENT sai1_0 join (select * from SUPPLIER_PROFILE t where t.TYPE='COMMUNITY') csp1_0 on csp1_0.ID=sai1_0.COMMUNITY_SUPPLIER_PROFILE_ID where sai1_0.COMMUNITY_SITE_ID=1 and csp1_0.BUSINESS_NUMBER="ZZZ"

```

In 6.2.14, the same query translated to

```auto
select ... from SITE_AGREEMENT s1_0 join SUPPLIER_PROFILE c1_0 on c1_0.ID=s1_0.COMMUNITY_SUPPLIER_PROFILE_ID where s1_0.COMMUNITY_SITE_ID=1 and c1_0.BUSINESS_NUMBER="ZZZ"

```

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [January 22, 2024, 5:45pm UTC](https://discourse.hibernate.org/t/query-uses-subselect-after-updating-to-6-4-2/8890/2 "2024-01-22T17:45:28Z")

</div>

Please try to create a reproducer with our test case template ([hibernate-test-case-templates/orm/hibernate-orm-6/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java at main · hibernate/hibernate-test-case-templates · GitHub](https://github.com/hibernate/hibernate-test-case-templates/blob/master/orm/hibernate-orm-6/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java)) and if you are able to reproduce the issue, create a bug ticket in our issue tracker([https://hibernate.atlassian.net](https://hibernate.atlassian.net)) and attach that reproducer.

---

<div class="post-metadata">

**Author:** ![mira-silhavy](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mira-silhavy/32/2811_2.png) [@mira-silhavy](https://discourse.hibernate.org/u/mira-silhavy)\
**Post date:** [January 29, 2024, 3:46pm UTC](https://discourse.hibernate.org/t/query-uses-subselect-after-updating-to-6-4-2/8890/3 "2024-01-29T15:46:33Z")

</div>

Opened bug report [[HHH-17686] - Hibernate JIRA](https://hibernate.atlassian.net/browse/HHH-17686)