[wp-trac] [WordPress Trac] #40376: WordPress Duplication Post when come to Pagination if using MySQL 5.6, 5.7 and above

[WordPress Trac]

#40376: WordPress Duplication Post when come to Pagination if using MySQL 5.6, 5.7
and above
--------------------------+-----------------------------
 Reporter:  shiroamada    |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  General       |    Version:  4.7.3
 Severity:  normal        |   Keywords:
  Focuses:                |
--------------------------+-----------------------------
 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
 [[Image(http://i.imgur.com/6bd8h6Q.png)]]

 Page 3
 [[Image(http://i.imgur.com/ORkIbHw.png)]]

 {{{
 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
 }}}

--
Ticket URL: <https://core.trac.wordpress.org/ticket/40376>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform