[wp-trac] [WordPress Trac] #52907: Add "ID" to the "order by" list where "post_date" is only used

[WordPress Trac]

#52907: Add "ID" to the "order by" list where "post_date" is only used
----------------------------+-----------------------------
 Reporter:  greenya         |      Owner:  (none)
     Type:  defect (bug)    |     Status:  new
 Priority:  normal          |  Milestone:  Awaiting Review
Component:  Administration  |    Version:  5.7
 Severity:  normal          |   Keywords:
  Focuses:  administration  |
----------------------------+-----------------------------
 The problem with admin lists displaying posts with pagination; The request
 looks like this:

 {{{
 SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
 FROM wp_posts
 WHERE wp_posts.post_type = 'post' AND (... post_status filtering...)
 ORDER BY wp_posts.post_date DESC
 LIMIT 120, 20
 }}}

 This is fine except one line, the "order by" line should be appended with
 ", wp_posts.ID" (so we additionally order by ID, an unique key).

 == Why?

 **The problem is that the order of posts with the same post_date is
 undefined.** This makes an issue when we have lets say 2 posts that has
 same post_date and they comes to the edge of the page, so lets say page 5
 ends with one post, and the page 6 starts with another post -- in this
 scenario, you can see same post in the end of 5th page and that same post
 in the start of 6th page -- **not only repeating but also completely
 hiding from the listing another post** (the one it has equal post_date
 with).

 == When?

 The chance to have same post_date is very low (1 second precision) when we
 create posts manually. But when we create posts with some import process
 or sync or other scripting thing that creates many posts in short time
 period -- the issue is there.

 == P.S.:

 Maybe automate it into WP_Query by adding "ID" to ordering fields list if
 it is supported by the table is not a bad idea. Because this is basically
 an issue for every places where we need to iterate over all items with
 pagination (we want **all** and **only once**).

 Thank you for making WP!

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