[wp-trac] [WordPress Trac] #40984: Possible Bug with Named Orderby Meta Query SQL

[WordPress Trac]

#40984: Possible Bug with Named Orderby Meta Query SQL
--------------------------+-----------------------------
 Reporter:  maiorano84    |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  General       |    Version:  4.8
 Severity:  normal        |   Keywords:
  Focuses:                |
--------------------------+-----------------------------
 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
 <?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.

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