Difference in result ( MySQL 5.7 vs MySQL 8.0 ) when using group

ghz 9months ago ⋅ 66 views

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

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

SELECT 
  DISTINCT(
    users.partner_id
  ), 
  users_summaries.total_count 
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, 
  last_working_date desc 
LIMIT 
  10 OFFSET 0
  • 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?

Answers

The behavior you're observing with different results for different versions of MySQL when changing the offset in your query is likely due to changes in how MySQL handles query execution and optimization between versions.

In MySQL 5.7, the behavior you're observing where changing the offset always results in unique results might be due to the way MySQL executes the query plan and processes the LIMIT and OFFSET clauses. It's possible that MySQL 5.7 is optimizing the query execution differently, resulting in consistent and predictable behavior regardless of the offset value.

However, in MySQL 8, there may have been changes to the query optimizer or execution plan that affect how the LIMIT and OFFSET clauses are applied, leading to different behavior when changing the offset. The optimizer may be choosing a different query plan or optimization strategy that can result in overlapping or inconsistent results when changing the offset.

To address this issue and ensure consistent behavior across different versions of MySQL, you may need to revise your query to include additional ordering criteria to make the results deterministic, especially when using pagination with LIMIT and OFFSET. Adding additional ordering criteria ensures that the results are sorted consistently, even when changing the offset.

For example, you could include additional ordering criteria based on unique identifiers or timestamps to ensure consistent ordering and avoid overlapping results when changing the offset.

Additionally, reviewing the query execution plans and optimizing the query for performance can also help improve the consistency and predictability of the results across different MySQL versions. You can use tools like EXPLAIN to analyze the query execution plan and identify potential optimizations or performance bottlenecks.

#mysql