Page count fails when using a sort specification in a Panache query with a SELECT DISTINCT statement in Quarkus 3.17 #44960
Description
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