WordPress Duplication Post when come to Pagination if using MySQL 5.6, 5.7 and above

[shiroamada]

Here is the Database behaviour
​http://dba.stackexchange.com/questions/169163/mysql-5-5-vs-5-6-above-same-database-same-query-but-different-output

When it comes to pagination, the bug is happening because we have a lot of same post_date result. In normal blog this will not happen.

URL: ​http://wordpress.dev/page/2, ​http://wordpress.dev/page/3

Here is the debug tools screenshot
Page 2

Page 3

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

The reason it is because MySQL behaviour Non-deterministic, the ORDER BY produces non-deterministic results.

Can the team add the ORDER BY wp_posts.post_date DESC, wp_posts.ID DESC to make deterministic results.

The Fix SQL:

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

[shiroamada]

I reported to MySQL for this issue - ​https://bugs.mysql.com/bug.php?id=85801

Replied by Developer:

This is not a bug, see discussion for ​https://bugs.mysql.com/bug.php?id=72076 and documentation at ​https://dev.mysql.com/doc/refman/5.6/en/limit-optimization.html

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

So I think WordPress need to be added in this requirement.

[talldanwp]

Hey @shiroamada. Thanks for reporting this, and apologies it took a while for a response.

This issue was discussed during a triage session in slack (​https://wordpress.slack.com/archives/C02RQBWTW/p1590040313447300, link requires a Making WordPress slack account).

It was hard to test the issue because of the limited time, but there was an agreement that this change makes logical sense.

[ocean90]

Closing as a duplicate of #44349 which has a few more details.