Page count fails when using a sort specification in a Panache query with a SELECT DISTINCT statement in Quarkus 3.17

[mzuber]

Describe the bug

Given a JPA entity like ExampleEntity

@Entity(name = "Example")
@Table(name = "examples")
public class ExampleEntity {

  @Id
  @Column(name = "id")
  public UUID id;

  @Column(name = "date_created")
  public OffsetDateTime dateCreated;
}

then trying to determine the number of available pages with the pageCount() method fails when the paginated PanacheQuery was created with a sort specification and uses a SELECT DISTINCT statement:

String query = "SELECT DISTINCT e FROM Example e"; // imagine some more joins and filters are happening here so that a distinct is required
Sort sortSpecification = Sort.by("e.dateCreated");
PanacheQuery<ExampleEntity> examplePage = find(query, sortSpecification).page(0, 10);

List<ExampleEntity> examples = examplePage.list();
int numberOfPages = examplePage.pageCount();

Invoking the pageCount() method results in an SQLGrammarException when using a PostgreSQL database:

org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [select count(*) from (select distinct ee1_0.id from examples order by ee1_0.date_created) derived1_0(c0_id)] [ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

The above code worked up until Quarkus 3.16.4, we are only seeing this type of error with the 3.17 release line.

Looking at the generated SQL I understand why this isn't working, but knowing this worked for all major version 3 release lines so far I'm wondering whether we should assume this is a bug and the pageCount() method should drop the ORDER BY expression or whether this should have never worked in the first place and we should change our code here to call the pageCount() only on Panache queries where no sort specification was defined.

Expected behavior

The pageCount() ignores the sort specification and correctly returns the number of available pages.

Actual behavior

An org.hibernate.exception.SQLGrammarException stating ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list.

How to Reproduce?

See example above.

Output of uname -a or ver

Darwin Kernel Version 24.1.0

Output of java -version

openjdk version "21.0.5" 2024-10-15 LTS

Quarkus version or git rev

3.17

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

Gradle 8.11.1

Additional information

No response

[quarkus-bot]

/cc @FroMage (panache), @loicmathieu (panache)

[FroMage]

Since 3.17 includes d395046 this means it's using getResultCount() from ORM upstream and so it's probably an ORM bug.

I've added a pure-ORM reproducer at https://github.com/FroMage/quarkus-44960

@yrodiere what's our strategy usually? Ask users to report upstream, or we handle this?

[mbladel]

@FroMage this looks like https://hibernate.atlassian.net/browse/HHH-18850, which is already fixed on main. I'll check if it has been backported and if not make sure it will be included in the next 6.6 maintenance version.

@mzuber for now, excluding the sort specification from your distinct queries should make the count work as expected and it won't affect the result.

[mbladel]

@FroMage I've tried your reproducer and can confirm the issue I linked fixed this issue. The fix will be backported to the next 6.6 release.

I believe we can close this, unless you want to wait for the release to verify if this actually fixes @mzuber's problem.

[yrodiere]

I believe we can close this, unless you want to wait for the release to verify if this actually fixes @mzuber's problem.

Usually we keep it open and close it when we merge the upgrade PR in Quarkus.