Regarding Subquery in JPQL,please help me on this

Hibernate ORM
1

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)]

2

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.

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