"The owner of the fetched association was not present in the select list" error on SimpleJpaRepository#getCountQuery

[marcioggs]

Hi.

I'm having the error in the stack trace below using sprint-data-jpa 3.4.2 when calling JpaSpecificationExecutor#findAll(@Nullable Specification spec, Pageable pageable).

The specification being passed as an argument contains a fetch join (such as root.fetch(Entity_.attribute, JoinType.LEFT)).
The exception is thrown by SimpleJpaRepository#getCountQuery(@Nullable Specification<S> spec, Class<S> domainClass) when the entity manager tries to create the query.

Should the getCountQuery method remove the fetch from fetch joins before requesting the entity manager to create the query?

org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.query.SemanticException: Query specified join fetching, but the owner of the fetched association was not present in the select list [SqmListJoin(eu.europa.ec.grow.enorm.entity.EsoProjectDetail(405).deliverableMappings(406))]
	at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:371) ~[spring-orm-6.2.2.jar:6.2.2]
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:246) ~[spring-orm-6.2.2.jar:6.2.2]
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:560) ~[spring-orm-6.2.2.jar:6.2.2]
	at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61) ~[spring-tx-6.2.2.jar:6.2.2]
	at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:343) ~[spring-tx-6.2.2.jar:6.2.2]
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:160) ~[spring-tx-6.2.2.jar:6.2.2]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.2.2.jar:6.2.2]
	at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:165) ~[spring-data-jpa-3.4.2.jar:3.4.2]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.2.2.jar:6.2.2]
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:223) ~[spring-aop-6.2.2.jar:6.2.2]
	at jdk.proxy3/jdk.proxy3.$Proxy267.findAll(Unknown Source) ~[na:an]

It seems to be related to issue 2348.

Sample:
https://github.com/marcioggs/spring-data-jpa-issue-3765-sample

Thank you.

[mp911de]

Please provide a minimal yet complete sample that reproduces the problem.
You can share it with us by pushing it to a separate repository on GitHub or by zipping it up and attaching it to this issue.

JPA's Criteria API doesn't allow removing fetch joins, the CriteriaBuilder is only additive in its nature. With Spring Data JPA 3.5 we're introducing a method to provide a count specification, see #3727.

[marcioggs]

Here's the sample:
https://github.com/marcioggs/spring-data-jpa-issue-3765-sample

Instructions to reproduce are in the README file.

[christophstrobl]

@marcioggs thanks, but that's returning 404.

[marcioggs]

Ops, sorry.
I made it public now and you can try again.

[christophstrobl]

Thank you for the reproducer. The issue is the same as when using plain JPA directly as outlined below (simplified for readability).

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Long> criteriaQuery = cb.createQuery(Long.class);
Root<TaskList> root = criteriaQuery.from(TaskList.class);

Join<TaskList, TaskItem> join = root.fetch("taskItems");
criteriaQuery.where(cb.equal(join.get("description"), "Report issue"));

criteriaQuery.select(cb.count(root));

TypedQuery<Long> query = em.createQuery(criteriaQuery);

data-jpa:3.5 will introduce a method overload for findAll that accepts a custom count Specification (see: #3727).
We're also looking into utilizing hibernate SelectionQuery#getResultCount() (see #3456) which is internally rewriting the source query and apparently works fine in this case.

// same as before but:
// omit .select(cb.count(root)) allows entity manager to create query

Join<TaskList, TaskItem> join = root.fetch("taskItems");
criteriaQuery.where(cb.equal(join.get("description"), "Report issue"));
TypedQuery<Long> query = em.createQuery(criteriaQuery);

((SelectionQuery<?>) query).getResultCount();

If you consider reporting the issue back to the hibernate team make sure to provide a sample that solely consists of hibernate and JPA to avoid being filtered out by your choice of technology.

[marcioggs]

Thank you @christophstrobl .
Please feel free to close this issue if you find it appropriate.