Panache - load page with join fetch

[g-apparence]

Describe the bug

Hi,
loading a page using panache works great.
Problem is when you try to fetch subcollection within a page loading like this :

@NamedQueries({
        @NamedQuery(name = "Videos.VideosByProjectAndStatus",
            query = "from videos v " +
                    "left join fetch v.audience a " +
                    "left join fetch v.survey sv " +
                    "left join fetch v.events ev " +
                    "left join fetch v.videoResource vr " +
                    "where v.project.uid = ?1"),
})

...

public static PageItems<VideoEntity> pageByProjectIdAndStatus(UUID id, List<VideoStatus> statuses,  int pageIndex, int pageSize) {
        var query = VideoEntity.find(
            "project.uid = ?1 and status in ?2",
            Sort.by("creationDate").descending(),
            id, statuses
        ).page(Page.of(pageIndex, pageSize));

        var pageCount = query.pageCount(); // <<--- this is what creates the error
        List<VideoEntity> items = query.list();
        return ImmutablePageItems.of(pageSize, pageCount - 1, pageIndex, items);
    }

Calling a request without the joins for the count query works.
Maybe we can make this a bit more friendly as join with pagination is a common case.

Expected behavior

You should be able to load a page with a fetch join.
Replacing lazy by EAGER works. (but I don't want to be eager on every request...)

Actual behavior

The error

Caused by: 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=null,role=io.apparence.entities.VideoEntity.audience,tableName=video_audience,tableAlias=videoaudie1_,origin=videos videoentit0_,columns={videoentit0_.uid,className=io.apparence.entities.VideoAudienceEntity}}] [SELECT COUNT(*) from io.apparence.entities.VideoEntity v join fetch v.audience where v.project.uid = ?1]

How to Reproduce?

No response

Output of uname -a or ver

No response

Output of java -version

17

GraalVM version (if different from Java)

No response

Quarkus version or git rev

2.9.2.Final

Build tool (ie. output of mvnw --version or gradlew --version)

No response

Additional information

No response

[quarkus-bot]

/cc @FroMage, @loicmathieu

[victor-h-costa]

I've ran into this same issue today calling PanacheQuery.count(), although PanacheQuery.list() works just fine. In my case I can workaround it by removing " FETCH " from my query but then I will have a N + 1 query problem.

[FroMage]

This looks like an ORM question @Sanne or @yrodiere

[yrodiere]

@FroMage As far as I understand, this is clearly a Panache issue, caused by the way Panache implements pageCount/count:

    public static String getCountQuery(String query) {
        // try to generate a good count query from the existing query
        Matcher selectMatcher = SELECT_PATTERN.matcher(query);
        String countQuery;
        if (selectMatcher.matches()) {
            // this one cannot be null
            String firstSelection = selectMatcher.group(1).trim();
            if (firstSelection.toLowerCase().startsWith("distinct ")) {
                // this one can be null
                String secondSelection = selectMatcher.group(2);
                // we can only count distinct single columns
                if (secondSelection != null && !secondSelection.trim().isEmpty()) {
                    throw new PanacheQueryException("Count query not supported for select query: " + query);
                }
                countQuery = "SELECT COUNT(" + firstSelection + ") " + selectMatcher.group(3);
            } else {
                // it's not distinct, forget the column list
                countQuery = "SELECT COUNT(*) " + selectMatcher.group(3);
            }
        } else if (FROM_PATTERN.matcher(query).matches()) {
            countQuery = "SELECT COUNT(*) " + query;
        } else {
            throw new PanacheQueryException("Count query not supported for select query: " + query);
        }

        // remove the order by clause
        String lcQuery = countQuery.toLowerCase();
        int orderByIndex = lcQuery.lastIndexOf(" order by ");
        if (orderByIndex != -1) {
            countQuery = countQuery.substring(0, orderByIndex);
        }
        return countQuery;
    }

That method removes the "SELECT" part of the query, but doesn't remove the "FETCH" clauses from the query. A "FETCH" clause without a corresponding "SELECT" clause doesn't make sense, hence the failure.

That's precisely what the exception is trying to explain:

Caused by: 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=null,role=io.apparence.entities.VideoEntity.audience,tableName=video_audience,tableAlias=videoaudie1_,origin=videos videoentit0_,columns={videoentit0_.uid,className=io.apparence.entities.VideoAudienceEntity}}] [SELECT COUNT(*) from io.apparence.entities.VideoEntity v join fetch v.audience where v.project.uid = ?1]

[blazmrak]

What is the state of this? Pagination also works just for simple queries, as soon as you join one to many relation it returns too few rows. Is there a desire to have proper pagination implemented, by doing a SELECT DISTINCT id ... offset x limit y and only then SELECT ... WHERE ... AND id IN (...distinctIds)? The count implementation would then follow the same SELECT DISTINCT id ... to get the actual count.

[FroMage]

Do you have any other example of join queries where it returns invalid count results? This would be useful for testing.

I guess we'd need to start with better query parsing :(

[blazmrak]

From looking at the queries doing something like

SELECT u FROM User u LEFT JOIN FETCH u.recipes r

would return only first X rows even if I want first X users right?

[beiertu-mms]

Any updates on this? We're currently using Quarkus v3.7.1 and this issue is still present.

[FroMage]

I'm working on a fix