Paginate is not counting total results correctly when used with distinct

[ptrpiero]

Package version

@adonisjs/core: ^5.6.2 @adonisjs/lucid: ^17.2.0

Node.js and npm version

Node.js: v16.14.2 npm: 8.5.0

Issue

When using both distinct and paginate inside a query, the total results is not correct. This seems related to the counting query that is performed before the actual query.

Sample

I have been able to reproduce this behaviour in a schema with two tables, users and user_group, where users has a one to many relation with table user_group

Route.get('/distinct-users', async ({ request, response }) => {
  const { perPage, page } = request.qs()
  const query = Database.query()
    .from('users')
    .join('user_group', 'users.id', 'user_group.user_id')
    .distinct('users.id')
  const result = await query.paginate(parseInt(page), parseInt(perPage))
  response.json(result)
})

Theese are the queries made when calling the above endpoint with page=1&perPage=100:

"mysql" (5.36 ms) SELECT COUNT(*) AS `total` FROM `users` INNER JOIN `user_group` ON `users`.`id` = `user_group`.`user_id` []
"mysql" (5.07 ms) SELECT DISTINCT `users`.`id` FROM `users` INNER JOIN `user_group` ON `users`.`id` = `user_group`.`user_id` LIMIT ? [ 100 ]

It looks like paginate is always doing SELECT COUNT(*), whatever field or distinct field is inside the actual query

[vincekinyops]

having same problem here.