MySQL 5.5 vs 5.6,5.7 above same database same query but different output, limit have duplicated ID I am using Running same source code and same database for WordPress, only the mysql version is different. It make my pagination post have duplication.
Here is what I found out. Any idea how to solve it in higher version?

This is for Page 2, and Page 3 query output. Left-Hand side is MySql 5.5, Right-Hand side is MySql 5.6.  up_posts.ID is unique.

SQL Statement:-

SELECT SQL_CALC_FOUND_ROWS up_posts.ID FROM up_posts  WHERE 1=1  AND up_posts.post_type = 'post' AND (up_posts.post_status = 'publish' OR up_posts.post_status = 'closed' OR up_posts.post_status = 'private' OR up_posts.post_status = 'hidden')  ORDER BY up_posts.post_date DESC LIMIT 10, 10

SELECT SQL_CALC_FOUND_ROWS up_posts.ID FROM up_posts  WHERE 1=1  AND up_posts.post_type = 'post' AND (up_posts.post_status = 'publish' OR up_posts.post_status = 'closed' OR up_posts.post_status = 'private' OR up_posts.post_status = 'hidden')  ORDER BY up_posts.post_date DESC LIMIT 20, 10


Page 2:- different is LIMIT 10,10


Page 3:- different is LIMIT 20,10


Seem like MySQL 5.5 will sort by the ID, but 5.6 above does not have this ability.
Added ORDER BY up_posts.post_date DESC, up_posts.ID DESC no more duplicate result. Seem like we need to manually add this to avoid an unnecessary problem.

FYI. It applied to MySQL 5.6 and 5.7. 

See 8.2.1.16 LIMIT Query Optimization

If you want to order by primary key, you have to say so explicitly.