Possible Bug with Named Orderby Meta Query SQL

[maiorano84]

I don't know how easy this would be to replicate, but I can at least walk through the steps in identifying the bug, and how I was able to address it for my own use-case.

On the web application we're building, we've added a simple meta field called "spotlight" to basically drive a single post to the front of the list, and apply some extra styling.

To do this, I had built the following query:

<?php
$news = new WP_Query([
    'post_type' => 'post',
    'posts_per_page' => 3,
    'orderby' => 'has_spotlight post_date',
    'meta_query' => [
        'relation' => 'OR',
        'has_spotlight' => [
            'key' => 'spotlight',
            'value' => '1'
        ],
        'standard' => [
            'key' => 'spotlight',
            'compare' => 'NOT EXISTS'
        ]
    ],
    'tax_query' => [
        [
            'taxonomy' => 'post_tag',
            'field'    => 'name',
            'terms'    => 'News'
        ]
    ]
]);

In other areas of the web application, similar queries appeared to function as expected - the "spotlight" post would appear first in the list, with remaining posts ordered by date.

However, for the above query, something interesting happened: A post with no spotlight metadata was appearing at the top of the list, and the actual spotlight post was appearing below it.

Using PHPStorm and Xdebug, I set a breakpoint for the query and examined the object. Under the request property, the following SQL was generated:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
LEFT JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id AND mt1.meta_key = 'spotlight' )
WHERE 1=1
    AND ( 
        wp_term_relationships.term_taxonomy_id IN (724)
    )
    AND ( 
         ( wp_postmeta.meta_key = 'spotlight' AND wp_postmeta.meta_value = '1' ) 
         OR 
         mt1.post_id IS NULL
    )
    AND wp_posts.post_type = 'post'
    AND (
        wp_posts.post_status = 'publish'
        OR
        wp_posts.post_status = 'acf-disabled'
    )
GROUP BY wp_posts.ID
ORDER BY wp_posts.menu_order, CAST(wp_postmeta.meta_value AS CHAR) DESC, wp_posts.post_date DESC
LIMIT 0, 3

When running this SQL directly in SequelPro, the following IDs were returned, confirming the order that I was seeing:

1
227 <-- Spotlight Post ID
225

After looking through the SQL statement, I decided to check exactly what Wordpress was seeing. After modifying the query to include everything from the wp_postmeta query, something interesting happened:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID, wp_postmeta.* FROM wp_posts
...

I receive the following results:

ID	meta_id	post_id	meta_key	meta_value
1	51	1	_edit_lock	1496942377:1
227	20794	227	spotlight	1
225	3305	225	_yst_is_cornerstone	

It appears that the table that's being used to define the ordering - wp_postmeta - is not being filtered the same way as the other table alias mt1.

Upon changing the order clause to use the filtered meta table:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID, mt1.*
...
CAST(mt1.meta_value AS CHAR) DESC

I received the correct results:

ID	meta_id	post_id	meta_key	meta_value
227	20794	227	spotlight	1
225	NULL	NULL	NULL		NULL
1	NULL	NULL	NULL		NULL

This is strange to me, as this line should theoretically prevent other unrelated meta information from being included in the results:

WHERE
...
( wp_postmeta.meta_key = 'spotlight' AND wp_postmeta.meta_value = '1' ) 
OR 
mt1.post_id IS NULL
...

It should be noted that simply changing the ORDER BY clause in my case - while it "fixes" the issue - is not technically correct.

Instead, the primary meta query clause should also contain the same filtering that the second does. The complete query looks like so:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'spotlight' )
LEFT JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id AND mt1.meta_key = 'spotlight' )
WHERE 1=1
    AND ( 
        wp_term_relationships.term_taxonomy_id IN (724)
    )
    AND ( 
        ( wp_postmeta.meta_key = 'spotlight' AND wp_postmeta.meta_value = '1' ) 
        OR 
        mt1.post_id IS NULL
    )
    AND wp_posts.post_type = 'post'
    AND (
        wp_posts.post_status = 'publish'
        OR
        wp_posts.post_status = 'acf-disabled'
    )
GROUP BY wp_posts.ID
ORDER BY wp_posts.menu_order, CAST(wp_postmeta.meta_value AS CHAR) DESC, wp_posts.post_date DESC
LIMIT 0, 3

Notice that the first metadata left join contains the same filtering by meta_key as the second.

I'm not sure what far-reaching consequences this might have, or if this issue has come up in the past. I imagine it's a bit of an edge-case.

Let me know if this is something that you're able to reproduce on your end, or if you need more information in regards to this.