Make WordPress Core

Opened 11 months ago

#58806 new defect (bug)

Unexpected ordering result when ordering based on multiple clauses

Reported by: pelentak's profile pelentak Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version: 6.2.2
Component: Query Keywords:
Focuses: Cc:

Description

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
$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:

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)

Change History (0)

Note: See TracTickets for help on using tickets.