Difference in result ( MySQL 5.7 vs MySQL 8.0 ) when using group_by and offset limit

Question

I'm wondering why this query shows different results in different versions of MySQL when I change offset. This is my query

SELECT 
  users.partner_id, 
  users_summaries.total_count,
  users.last_working_date
FROM 
  `users` 
  LEFT OUTER JOIN (
    SELECT 
      user_loc_summaries.partner_id, 
      user_loc_summaries.loc_id, 
      SUM(
        user_loc_summaries.total_count
      ) AS total_count 
    FROM 
      user_loc_summaries 
    WHERE 
      (
        user_loc_summaries.loc_id = '2222' 
      ) 
    GROUP BY
      user_loc_summaries.partner_id
  ) AS users_summaries ON (
    users_summaries.partner_id = users.partner_id
  ) 
WHERE 
  AND `users`.`loc_id` IN (2222, 666, 666)
GROUP BY 
  users.partner_id 
ORDER BY 
  (
    users.loc_id = '2222'
  ) DESC, 
  users.loc_id ASC, 
  users.last_working_date desc 
LIMIT 
  10 OFFSET 0

• last_working_date can be null (in my database last_working_date almost is null)
• On version 5.7 when I change the offset (for pagination) the result always uniq
• On version 8 when I change the offset the results is overlaps with previous pages

I know that using group_by without order_by may cause this problem. But I don't know why mysql version 5.7 return the correct results?

Answer 1

In MySQL, historically GROUP BY was used to provide sorting as well. If a query specified GROUP BY, the result was sorted as if ORDER BY was present in the query.

https://dev.mysql.com/blog-archive/removal-of-implicit-and-explicit-sorting-for-group-by/

In mysql 5.7 column gourp_by was sorting as well (default asc), and from mysql 8.0.13 it was removed so that why using limit, offset with group_by without order_by (group_by column) return correct results.