count query fails to be auto generated when using @Query annotation with fetch join

[sinbom]

findAllFetch method causes the following error. however, findAllNotFetch method does not cause errors. because the automatically generated countQuery does not include the fetch keyword. likewise, fetch join paging using @EntityGraph does not cause an error.

public interface BoardRepository extends JpaRepository<Board, Long> {

    @Query(value = "SELECT DISTINCT b FROM Board b LEFT JOIN FETCH b.comments ORDER BY b.id")
    Page<Board> findAllFetch(Pageable pageable);
    
    @Query(value = "SELECT DISTINCT b FROM Board b LEFT JOIN b.comments ORDER BY b.id")
    Page<Board> findAllNotFetch(Pageable pageable);

    @EntityGraph(attributePaths = "comments")
    Page<Board> findAll(Pageable pageable);

}

Caused by: 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=null,role=com.example.demo.entity.Board.comments,tableName=comment,tableAlias=comments1_,origin=board board0_,columns={board0_.id,className=com.example.demo.entity.Comment}}]
	at org.hibernate.hql.internal.ast.tree.SelectClause.initializeExplicitSelectClause(SelectClause.java:215) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
	at org.hibernate.hql.internal.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:1028) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
	at org.hibernate.hql.internal.ast.HqlSqlWalker.processQuery(HqlSqlWalker.java:796) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
	at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:694) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
	at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:330) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
	at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:278) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:276) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:192) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
	... 125 common frames omitted

whether it is fetch join or not does not affect count query results. what is important is the association of the fetching entity. of course, I know that the count query generated in the findAllNotFetch method has inaccurate paging. but, paging would have been accurate if it was not a collection fetch.

i wonder, the QueryUtils class removes unnecessary queries that do not affect results such as order by, but why the fetch does not. Is this intentional?

public static String createCountQueryFor(String originalQuery, @Nullable String countProjection) {

		Assert.hasText(originalQuery, "OriginalQuery must not be null or empty!");

		Matcher matcher = COUNT_MATCH.matcher(originalQuery);
		String countQuery;

		if (countProjection == null) {

			String variable = matcher.matches() ? matcher.group(VARIABLE_NAME_GROUP_INDEX) : null;
			boolean useVariable = StringUtils.hasText(variable) //
					&& !variable.startsWith(" new") //
					&& !variable.startsWith("count(") //
					&& !variable.contains(","); //

			String complexCountValue = matcher.matches() && StringUtils.hasText(matcher.group(COMPLEX_COUNT_FIRST_INDEX))
					? COMPLEX_COUNT_VALUE
					: COMPLEX_COUNT_LAST_VALUE;

			String replacement = useVariable ? SIMPLE_COUNT_VALUE : complexCountValue;
			countQuery = matcher.replaceFirst(String.format(COUNT_REPLACEMENT_TEMPLATE, replacement));
		} else {
			countQuery = matcher.replaceFirst(String.format(COUNT_REPLACEMENT_TEMPLATE, countProjection));
		}

		return countQuery.replaceFirst(ORDER_BY_PART, "");
	}

[manousos]

A temporary workaround is to provide the specific count query. @sinbom for your example could be the following

    @Query(value = "SELECT DISTINCT b FROM Board b LEFT JOIN FETCH b.comments ORDER BY b.id"
countQuery="SELECT count(b.id) FROM Board b LEFT JOIN  b.comments")
    Page<Board> findAllFetch(Pageable pageable);

[gregturn]

We are working on a parsing solution to better handle such situations than QueryUtils accommodates. For the meantime, we are trying to avoid any more changes to QueryParser.

[gregturn]

With the new parser in place we can opt for SELECT DISTINCT b FROM Board b LEFT JOIN FETCH b.comments ORDER BY b.id to be rendered as either:

SELECT count(DISTINCT b) FROM Board b LEFT JOIN FETCH b.comments

or

SELECT count(DISTINCT b) FROM Board b LEFT JOIN b.comments

Are you saying that Hibernate wants count queries to DROP the FETCH aspect of JOINs for count operations?

[sinbom]

SELECT count(DISTINCT b) FROM Board b LEFT JOIN b.comments

Yes, Hibernate wants count queries to DROP the FETCH aspect of JOINs for count operations

[marcioggs]

Any recommended workaround to solve it on JpaSpecificationExecutor.html#findAll(Specification<T> spec, Pageable pageable) at Spring Data JPA 2?

[marcioggs]

Any recommended workaround to solve it on JpaSpecificationExecutor.html#findAll(Specification spec, Pageable pageable) at Spring Data JPA 2?

For anyone in the same situation, one fragile alternative without using AOP is to override the class org.hibernate.query.criteria.internal.CriteriaQueryImpl and remove the fetch keyword from the queries containing fetch join and count. This can be done by manipulating the variable jpaqlString at the following line: https://github.com/hibernate/hibernate-orm/blob/5.6.11/hibernate-core/src/main/java/org/hibernate/query/criteria/internal/CriteriaQueryImpl.java#L301

Be aware that you'd have to update it whenever Hibernate is upgraded on your project to avoid incompatibility issues.