[wp-trac] [WordPress Trac] #30478: Comment query results returned in an inconsistent order on some versions of MySQL

[WordPress Trac]

#30478: Comment query results returned in an inconsistent order on some versions of
MySQL
------------------------------------------+-----------------------------
 Reporter:  boonebgorges                  |       Owner:
     Type:  defect (bug)                  |      Status:  new
 Priority:  normal                        |   Milestone:  Future Release
Component:  Comments                      |     Version:
 Severity:  normal                        |  Resolution:
 Keywords:  needs-patch needs-unit-tests  |     Focuses:
------------------------------------------+-----------------------------
Changes (by boonebgorges):

 * milestone:  Awaiting Review => Future Release


Comment:

 I've investigated the issue a bit more, and quelle surprise! it's more
 complicated than I thought.

 Comment queries almost always have an ORDER BY clause (the ability to omit
 this clause will come in 4.1; see #29902). When no 'orderby' argument is
 provided, the clause defaults to `ORDER BY comment_date_gmt DESC`. Let's
 say you have multiple comments with the same `date_modified_gmt`.
 Different versions of MySQL appear to act as follows:

 * 5.0-5.1: Fall back on the primary key, using the order specified in the
 existing `ORDER BY` clause. So, implicitly, `ORDER BY comment_date_gmt
 DESC, comment_ID DESC`.
 * 5.5+: Fall back on the primary key, using MySQL's default sort order.
 So, implicitly, `ORDER BY comment_date_gmt DESC, comment_ID ASC`.

 Fixing this will require a non-trivial refactoring of the way that the
 `ORDER BY` clause is built. In [12518], support for an array of 'orderby'
 values was introduced. However, 'order' ASC/DESC has always just been
 tacked on at the end. So it's not currently easy for us to tack a final
 `comment_ID DESC` onto this clause. What's needed is support for
 independent orderby/order pairs. And if we're going to build it
 internally, we may as well expose it in 'orderby' parameter. In other
 words, #17065, but for comments.

 Let's try to do this for 4.2.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/30478#comment:4>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform