[wp-hackers] Possible Wordpress or MySQL bug on different Ubuntu versions?

[Roberto Sanchez]

Please excuse the long email, I wanted to include all relevant details.

I've run across a really weird bug with WP_Query that seems to depend on
the version of Ubuntu I'm using.

I'm running Wordpress on a VPS that runs Ubuntu. The weird bug has to do
with sorting. For reference, here are the arguments I'm passing to WP_Query:

$query_args = array(
    'post_type' => 'coupon',
    'posts_per_page' => 10,
    'paged' => $current_page,
    'orderby' => 'meta_value_num',
    'order' => 'DESC',
    'meta_key' => 'score',
    'post_status' => 'publish',
    'tax_query' => array(
        array(
            'taxonomy' => 'merchant',
            'field' => 'slug',
            'terms' => $slug
        )
    ),
    'meta_query' => array(
        array(
            'key' => 'startdate',
            'value' => date( 'c' ),
            'compare' => '<=',
            'type' => 'DATETIME'
        ),
    )
);

I'm getting posts of post_type 'coupon', associated with a 'merchant'
taxonomy, filtered to show only posts with a 'startdate' post meta that is
before the current time, and ordering them by post meta 'score'.

The query could return many posts with the same 'score', and here is where
the problem starts. The query returns posts correctly ordered by 'score',
but if I have for example 20 posts in a row with the same 'score', there's
no guarantee it would return all those posts in the same order. Those 20
posts with the same 'score', while correctly coming after posts with a
higher 'score' but before posts with a 'lower' score, will come in complete
random order among posts with the same 'score'. This is a problem because I
am getting 10 posts per page, so with those 20 posts being randomized, I
could load a duplicate post on a later page.

The weird part about this bug is that it seems to depend on Ubuntu
versions. My development site was running on 11.04, while my live site was
running on 12.04. I was only having the sorting problem on the live site,
and after ensuring every other setting was identical between the live site
and the development site, I upgraded my development site to 12.04 from
11.04, and now I have the sorting problem on both sites. So it seems that
this is not a problem on 11.04, but it is a problem on 12.04.

I tried taking the MySQL query that wpdb runs, and running it directly from
phpmyadmin, and the posts were returned in random order there, so it makes
me think it's less a wordpress problem, more a MySQL problem. PHP, Apache,
and MySQL were all upgraded when I upgraded my development VPS from Ubuntu
11.04 to 12.04.

There is a workaround, however. I first thought to try ordering the posts
by post ID as well, to see if that would ensure the posts get returned in
the same order every time. I changed the WP_Query argument 'orderby' to
this:

'orderby' => 'meta_value_num ID'

This should order by 'score' first, then by post ID. However, trying this,
it returned the posts in reverse 'score' order, and changing 'order' from
'DESC' to 'ASC' had no effect.

I checked what query wpdb is using after adding ID to 'orderby', and the
orderby clause looks like this:

ORDER BY wp_postmeta.meta_value+0 ,wp_posts.ID DESC

So I tried using a 'posts_orderby' filter to append wp_posts.ID DESC to the
orderby clause, to have it looks like this:

ORDERBY wp_postmeta.meta_value+0 DESC, wp_posts.ID DESC

With this, it finally returns the post in correct 'score' order, and
returns all posts in the same order each time the query is run.

I'm looking for any insight as to why I should need to do this workaround
when I'm running my Wordpress website on Ubuntu 12.04, and why I don't need
it on 11.04.  Does anyone know any possible changes to MySQL that could
have caused this? Maybe any changes to Wordpress from 3.5 to 3.5.1 could
have caused this? I found a workaround, but it feels like a hack and I
would rather know why I need it, to see if there's a more elegant solution
possible.

Thanks.