PanacheQuery: singleResultOptional() gives hibernate warning "HHH000104"

[jkalex-git]

Describe the bug

When a query involves JOIN FETCH on one to many relation and use singleResultOptional() to query the data, below warning is coming in the logs "HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!"

This is because the function applies limit of 2 on the query and hence trigger the warning.

Expected behavior

Remove the limit which will avoid the warning as for a user this might be confusing as no pagination or range is specified. It will throw 'NonUniqueResultException' anyway if more than one results are found.

Actual behavior

"HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!" warning in logs even though there is only one entity in the result set.

How to Reproduce?

No response

Output of uname -a or ver

No response

Output of java -version

jdk 21

Quarkus version or git rev

3.8.5

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

No response

Additional information

No response

[quarkus-bot[bot]]

/cc @FroMage (panache), @gsmet (hibernate-orm), @loicmathieu (panache), @yrodiere (hibernate-orm)

[yrodiere]

Remove the limit which will avoid the warning as for a user this might be confusing as no pagination or range is specified. It will throw 'NonUniqueResultException' anyway if more than one results are found.

This could result in unnecessarily fetching hundreds of results from the DB. Which is why the limit is there in the first place, I guess.

That being said, I don't think it's Panache's role to optimize such a query. It's either the user's or Hibernate ORM's.

So I tend to agree we should just replace this:

https://github.com/quarkusio/quarkus/blob/d3e578eb7f2f5e068ec03347990609b14ddb6fe2/extensions/panache/hibernate-orm-panache-common/runtime/src/main/java/io/quarkus/hibernate/orm/panache/common/runtime/CommonPanacheQueryImpl.java#L335-L342

With this:

        Query jpaQuery = createQuery();
        try (NonThrowingCloseable c = applyFilters()) {
            return jpaQuery.unwrap(org.hibernate.query.Query.class).uniqueResultOptional();

If there's optimization to be implemented, we should do that in Hibernate ORM itself, where we can make sure no warnings are thrown.

[FroMage]

Sounds reasonable. But also for the non-Optional version :) PR welcome?