# Regarding Subquery in JPQL,please help me on this

**URL:** <https://discourse.hibernate.org/t/regarding-subquery-in-jpql-please-help-me-on-this/8164>\
**Category:** Hibernate ORM\
**Created:** [August 23, 2023, 11:27am UTC](https://discourse.hibernate.org/t/regarding-subquery-in-jpql-please-help-me-on-this/8164 "2023-08-23T11:27:21Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Raju\_Sk](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/raju_sk/32/2631_2.png) [@Raju\_Sk](https://discourse.hibernate.org/u/Raju_Sk)\
**Post date:** [August 23, 2023, 11:27am UTC](https://discourse.hibernate.org/t/regarding-subquery-in-jpql-please-help-me-on-this/8164/1 "2023-08-23T11:27:21Z")

</div>

SELECT m.materialId,m.materialName,m.materialShortName, subq.locationId " +  
" FROM CommodityEntity m " +  
" JOIN (" +  
" SELECT lc.materialId AS materialId, ls.locationId AS locationId,COUNT(lc.materialId) AS FREQ " +  
" FROM LogisticsCargoScheDuleEntity lcs " +  
" JOIN lcs.logisticsCargoId lc " +  
“JOIN lc.logisticsVoyageId lv “+  
" JOIN lcs.logisticsScheduleId ls” +  
" WHERE (lv.userUpdatedBy = :userId OR lv.userNominationBy = :userId OR lv.userFixedBy = :userId)” +  
" GROUP BY lc.materialId,ls.locationId) " +  
" AS subq ON subq.materialId = m.materialId " +  
" ORDER BY subq.freq DESC

Caused by: org.springframework.data.repository.query.QueryCreationException: Could not create query for public abstract java.util.List com.bunge.digital.of.voyagemanagement.logisticvoyage.domain.CommodityRepository.getFavouriteMaterial(int); Reason: Validation failed for query for method public abstract java.util.List com.bunge.digital.of.voyagemanagement.logisticvoyage.domain.CommodityRepository.getFavouriteMaterial(int)  
Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List com.bunge.digital.of.voyagemanagement.logisticvoyage.domain.CommodityRepository.getFavouriteMaterial(int)  
Caused by: org.hibernate.query.sqm.InterpretationException: Error interpreting query [SELECT m.materialId,m.materialName,m.materialShortName, subq.locationId FROM CommodityEntity m JOIN ( SELECT lc.materialId AS materialId, ls.locationId AS locationId,COUNT(lc.materialId) AS FREQ FROM LogisticsCargoScheDuleEntity lcs JOIN lcs.logisticsCargoId lc JOIN lc.logisticsVoyageId lv JOIN lcs.logisticsScheduleId ls WHERE (lv.userUpdatedBy = :userId OR lv.userNominationBy = :userId OR lv.userFixedBy = :userId) GROUP BY lc.materialId,ls.locationId) AS subq ON subq.materialId = m.materialId ORDER BY subq.freq DESC]; this may indicate a semantic (user query) problem or a bug in the parser [SELECT m.materialId,m.materialName,m.materialShortName, subq.locationId FROM CommodityEntity m JOIN ( SELECT lc.materialId AS materialId, ls.locationId AS locationId,COUNT(lc.materialId) AS FREQ FROM LogisticsCargoScheDuleEntity lcs JOIN lcs.logisticsCargoId lc JOIN lc.logisticsVoyageId lv JOIN lcs.logisticsScheduleId ls WHERE (lv.userUpdatedBy = :userId OR lv.userNominationBy = :userId OR lv.userFixedBy = :userId) GROUP BY lc.materialId,ls.locationId) AS subq ON subq.materialId = m.materialId ORDER BY subq.freq DESC]  
Caused by: java.lang.IllegalArgumentException: Can’t compare test expression of type [CommodityEntity] with element of type [BasicSqmPathSource(materialId : Integer)]

---

<div class="post-metadata">

**Author:** ![mbladel](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mbladel/32/2128_2.png) [@mbladel](https://discourse.hibernate.org/u/mbladel)\
**Post date:** [August 23, 2023, 11:37am UTC](https://discourse.hibernate.org/t/regarding-subquery-in-jpql-please-help-me-on-this/8164/2 "2023-08-23T11:37:18Z")

</div>

I can’t be sure without looking at your mappings, but it looks like you’re trying to compare an Entity valued path (i.e. an association) with a basic valued path (an Integer, probably the entity ID). This used to work in Hibernate 5 but was changed in Hibernate 6, see the [migration guide](https://docs.jboss.org/hibernate/orm/6.0/migration-guide/migration-guide.html#_association_comparisons).

To fix this you should explicitly compare the entity path’s id attribute.