Wrong calculation of total number of elements when using DISTINCT clause [DATAJPA-342]

[spring-projects-issues]

Alex opened DATAJPA-342 and commented

If I define a custom query of this type:

@Query("SELECT DISTINCT m.genre FROM Media m WHERE m.user = ?1 ORDER BY m.genre ASC")
Page<String> getJukeGenres(User user, Pageable pageable);

The resulting Page element has wrong Total Elements.
It seems like it gives the number of total records instead of the distinct ones.

I had to create another adhoc query just to have the correct count:

@Query("SELECT count(*) as num FROM Media m WHERE m.user = ?1 AND m.genre IS NOT NULL")
long countGenres(User user);

But this is tedious workaround that I would like to avoid

---

Affects: 1.3 GA

[spring-projects-issues]

Oliver Drotbohm commented

We probably don't discover the distinct correctly here. As a workaround, you can define the countQuery attribute of the @Query annotation of the paging method to define the query explicitly

[OctavioCega]

Any progress or solution on this?

It seems that when using a native query with "distinct" it is getting removed in the count.

Is this a bug or a feature?

using springboot version 2.3.7.BUILD-SNAPSHOT