[wp-trac] [WordPress Trac] #58806: Unexpected ordering result when ordering based on multiple clauses

[WordPress Trac]

#58806: Unexpected ordering result when ordering based on multiple clauses
--------------------------+-----------------------------
 Reporter:  pelentak      |      Owner:  (none)
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Query         |    Version:  6.2.2
 Severity:  normal        |   Keywords:
  Focuses:                |
--------------------------+-----------------------------
 I am not sure if this is a bug or this is a mis-understanding from my side
 on how the ordering works but
 Consider the following `WP_Query`:
 {{{#!php
 <?php
 $query = new WP_Query(
     [
         'fields' => 'ids',
         'post_type' => 'post',
         'post_status' => 'publish',
         'posts_per_page' => -1,
         'meta_query' => [
             'relation' => 'OR',
             'sticky_clause' => [
                 'key' => '_sticky',
                 'type' => 'UNSIGNED',
             ],
             [
                 'key' => '_sticky',
                 'compare' => 'NOT EXISTS',
             ],
         ],
         'orderby' => [
             'sticky_clause' => 'desc',
             'title' => 'asc',
         ],
     ]
 );
 }}}

 The expected behavior is that the query should show posts that have a
 `_sticky` meta first, then shows the rest of the posts sorted by their
 title alphabetically.
 However it doesn't happen.


 {{{
 array(7) {
   [0]=>
   string(20) "A guide to WordPress"
   [1]=>
   string(27) "Brotherhood of creativeness"
   [2]=>
   string(30) "Gather all information you can"
   [3]=>
   string(16) "Yet another post"
   [4]=>
   string(12) "Hello world!"
   [5]=>
   string(36) "Opera browser released a new version"
   [6]=>
   string(27) "Zebras are cool and relaxed"
 }
 }}}

 In the above output of post titles, the `"Hello World!"` has `_sticky`
 meta, but the sorting isn't correct (nor alphabetically and nor by
 `_sticky` meta first)

 This is the SQL query generated by this query:

 {{{#!php
 SELECT
   wp_posts.ID
 FROM
   wp_posts
   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 = '_sticky'
   )
 WHERE
   1 = 1
   AND (
     wp_postmeta.meta_key = '_sticky'
     OR mt1.post_id IS NULL
   )
   AND wp_posts.post_type = 'post'
   AND (
     (wp_posts.post_status = 'publish')
   )
 GROUP BY
   wp_posts.ID
 ORDER BY
   CAST(
     wp_postmeta.meta_value AS UNSIGNED
   ) DESC,
   wp_posts.post_title ASC
 }}}

 If I am not wrong the issue happens at the position `ORDER BY
 CAST(wp_postmeta.meta_value AS UNSIGNED) DESC`, it should use the alias
 instead of the table name here (should be `mt1.meta_value`)

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