Comment descendant queries do not have determinate order

[boonebgorges]

[34546] introduced hierarchical comment queries. For efficiency, each level of the hierarchy is fetched in a single SQL query, of the form SELECT ... WHERE comment_parent IN ( p1, p2, p3...). However, an ORDER BY clause was not included in this query, with the result that in certain environments (such as sites with large numbers of comments) MySQL could return results in unexpected order. This was discovered by @tellyworth on certain wordpress.com sites.

The proper fix is to add an ORDER BY clause to the descendant query. The order can always be ASC, because non-top-level comments have always been displayed in ascending order.

[boonebgorges]

34838.diff​ adds the ORDER BY clause. @pento Can I get an RC review please?

[pento]

Should it be ordered by comment_ID or comment_date, for if the comment date has been edited?

[boonebgorges]

Replying to pento:

Should it be ordered by comment_ID or comment_date, for if the comment date has been edited?

Oh right, I forgot that this might be edited. Two things:

• I like to have comment_ID as at least the secondary sort, because comment timestamps can be shared, in which case we don't have a determinate order.
• comment_date does not have an index, so we should use comment_date_gmt.

See 34838.2.diff​

[pento]

:danceparty:

Let's do this.

[boonebgorges]

In 35757:

Ensure that order is specified when querying for comment descendants.

Props tellyworth.
Fixes #34838.