org.hibernate.QueryException when applying @EntityGraph on (Querydsl) findAll(Predicate, Pageable) method [DATAJPA-790]

[spring-projects-issues]

Ivan Fedorenkov opened DATAJPA-790 and commented

The exception is thrown when applying @EntityGraph annotation on the Page<T> findAll(Predicate, Pageable) method in a repository interface.

Example Stacktrace (source: stackoverflow):

org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list [FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=appointment,role=com.physioclinic.entity.Appointment.createdBy,tableName=user,tableAlias=user5_,origin=appointment appointmen0_,columns={appointmen0_.createdBy_id ,className=com.physioclinic.entity.User}}] [select count(appointment)
from com.physioclinic.entity.Appointment appointment where lower(concat(concat(appointment.patient.person.name,?1),appointment.patient.person.surname)) like ?2 escape '!']; nested exception is java.lang.IllegalArgumentException: org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list [FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=appointment,role=com.physioclinic.entity.Appointment.createdBy,tableName=user,tableAlias=user5_,origin=appointment appointmen0_,columns={appointmen0_.createdBy_id ,className=com.physioclinic.entity.User}}]

Applying the annotation on the Iterable<T> findAll(Predicate) is not resulting with the exception (works just fine)

---

Affects: 1.9 GA (Gosling)

Issue Links:

• DATAJPA-684 QueryDslPredicateExecutor + EntityGraph + findAll
  ("is duplicated by")
• DATAJPA-1087 Regression: query hints not applied to count queries for QueryDSL

Referenced from: pull request #182, and commits 297efea, f2453de, 1a04f16, db345e6, 1477c18, 0e9be4e, dc230ef, 3f5467d, 35d5cc2, 1b86b0e, fea2f76, dd0919b

Backported to: 1.10.5 (Hopper SR5), 1.9.7 (Gosling SR7)

2 votes, 5 watchers

[spring-projects-issues]

Ivan Fedorenkov commented

The root cause is in applying hints on the count query.

@Override
public Page<T> findAll(Predicate predicate, Pageable pageable) {

	JPQLQuery countQuery = createQuery(predicate);
	JPQLQuery query = querydsl.applyPagination(pageable, createQuery(predicate));
......
 }

protected JPQLQuery createQuery(Predicate... predicate) {

	JPAQuery query = querydsl.createQuery(path).where(predicate);
	CrudMethodMetadata metadata = getRepositoryMethodMetadata();

	if (metadata == null) {
		return query;
	}

	LockModeType type = metadata.getLockModeType();
	query = type == null ? query : query.setLockMode(type);

	for (Entry<String, Object> hint : getQueryHints().entrySet()) {
		query.setHint(hint.getKey(), hint.getValue());
	}

	return query;
}

If you remove hints from the count query the hibernate will not throw the exception and (looks like) everything will be working just fine.

[spring-projects-issues]

Thomas Darimont commented

I just tried to replicate that with plain SD JPA - but it seems to work as expected. Perhaps I'm missing something.

I tried this with hibernate 4.3.9.Final via

mvn clean install -Phibernate-43

and explicitly with 4.3.10.Final via

mvn clean install  -Dhibernate=4.3.10.Final

both tests pass.

Would you mind creating a small test case that reporduces the problem?

My try:

public interface RepositoryMethodsWithEntityGraphConfigRepository extends CrudRepository<User, Integer>, QueryDslPredicateExecutor<User> {

...
    /**
     * Should find all users with pagination.
     */
    @EntityGraph(type = EntityGraphType.LOAD, value = "User.overview")
    Page<User> findAll(Pageable pageable);
...
}

In EntityGraphRepositoryMethodsIntegrationTests:

    /**
     * @see DATAJPA-790
     */
    @Test
    public void shouldRespectConfiguredJpaEntityGraphWithPagination() {

        Assume.assumeTrue(currentEntityManagerIsAJpa21EntityManager(em));

        Page<User> page = repository.findAll(new PageRequest(0, 100));

        List<User> result = page.getContent();

        assertThat(result.size(), is(2));
        assertThat(Persistence.getPersistenceUtil().isLoaded(result.get(0).getRoles()), is(true));
        assertThat(result.get(0), is(tom));
    }
    
    /**
     * @see DATAJPA-790
     */
    @Test
    public void shouldRespectConfiguredJpaEntityGraphWithPaginationAndQueryDslPredicates() {

        Assume.assumeTrue(currentEntityManagerIsAJpa21EntityManager(em));

        Page<User> page = repository.findAll(QUser.user.firstname.isNotNull(), new PageRequest(0, 100));

        List<User> result = page.getContent();

        assertThat(result.size(), is(2));
        assertThat(Persistence.getPersistenceUtil().isLoaded(result.get(0).getRoles()), is(true));
        assertThat(result.get(0), is(tom));
    }

[spring-projects-issues]

Thomas Darimont commented

I can now reproduce this with Spring Boot 1.3.0 BUILD-SNAPSHOT: https://gist.github.com/thomasdarimont/7ae6b3b4293fde7cd65c

[spring-projects-issues]

Oliver Drotbohm commented

Does Boot 1.3 change the Hibernate dependency version?

[spring-projects-issues]

Thomas Darimont commented

The current 1.3.0.BUILD-SNAPSHOT of Spring Boot uses hibernate-entitymanager-4.3.11.Final.
Even if I change the hibernate-entity manager version to hibernate-entitymanager-4.3.10.Final - the problem still occurs so it isn't the new hibernate version.
If I run the spring-data-jpa build with my additional tests (see above) with that version I still don't see an error - so I must miss something.

A similar problem with Spring Data JPA & EntityGraph is described here: http://codingexplained.com/coding/java/spring-framework/fetch-query-not-working-spring-data-jpa-pageable

Not adding the query hints for the count query does indeed fix the problem - but I don't know if we shouldn't any hints at all to count queries or if we should just filter "some" of them (like the entity load / fetch graph hint). In either way we'd need to know whether we're generating a count query or not in createQuery

[spring-projects-issues]

Rich Thibault commented

Just chiming in on this one. I get this error as well, with Spring 4.2.5, Spring Data 1.9.4, and Hibernate 4.3.11. The count query blows up when I apply a fetch graph. Ivan's workaround did the trick for me: http://stackoverflow.com/questions/27494988/overriding-spring-data-jpa-default-method-annotating-with-entitygraph-causes-que

org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list [FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=enrollment,role=Enrollment.contact,tableName=contact,tableAlias=contact1_,origin=enrollment enrollment0_,columns={enrollment0_.contactId ,className=Contact}}] [select count(enrollment)
from Enrollment enrollment
where enrollment.status <> ?1 and enrollment.program = ?2];

[spring-projects-issues]

Jocelyn N'TAKPE commented

Same problem. Could we generate count query in a specific method ?