Incorrect DISTINCT count query creation in SimpleJpaRepository [DATAREST-1288]

Martin Sommer opened DATAREST-1288 and commented

 

Consider the following entities and the existing records:

Entities:
User
----
email (PK)

Account
-------
id (PK)
user
countryName


 
Existing records:
Users:
[
  {email: a@b.c},
  {email: x@y.z}
]

Accounts:
[
  {id: 1, user: a@b.c, countryName: UK},
  {id: 2, user: x@y.z, countryName: UK},
  {id: 3, user: a@b.c, countryName: USA},
  {id: 4, user: x@y.z, countryName: USA}
]  

 

Consider the following query generated via UserRepository.findAll(Specification, Pageable):

 

SELECT distinct u
FROM User u
INNER JOIN Account a ON a.user = u
GROUP BY u, a.countryName
ORDER BY a.countryName            // this ORDER BY is not really relevant for the issue, but the reason why the GROUP BY looks like it is
LIMIT 10 OFFSET 0

 

which leads to the following (correct) result:

 

a@b.c
x@y.z

 

But the generated count-query (due to given pageable) looks like this:

 

SELECT distinct count(distinct u.email)
FROM User u
INNER JOIN Account a ON a.user = u
GROUP BY u, a.countryName

 

which results in a count=1 (!!!)

This is a result of SimpleJpaRepository.getCountQuery(Specification, Class) :

 

...
if (query.isDistinct()) {
 query.select(builder.countDistinct(root));
} else {
 query.select(builder.count(root));
}
...

 

So if the original query is DISTINCT, then a DISTINCT count-select is used but the query itself is still DISTINCT, which leads to an incorrect result. The query itself must not be DISTINCT

---

No further details from DATAREST-1288