[wp-trac] [WordPress Trac] #57767: SQL query error with multiple order specifications in pre_get_posts.

[WordPress Trac]

#57767: SQL query error with multiple order specifications in pre_get_posts.
--------------------------+------------------------------
 Reporter:  kuroro111     |       Owner:  (none)
     Type:  defect (bug)  |      Status:  new
 Priority:  normal        |   Milestone:  Awaiting Review
Component:  Query         |     Version:  6.1.1
 Severity:  critical      |  Resolution:
 Keywords:                |     Focuses:
--------------------------+------------------------------
Changes (by SergeyBiryukov):

 * component:  General => Query


Comment:

 Hi there, welcome to WordPress Trac! Thanks for the report.

 For reference, the code from the topic linked above is:
 {{{
 add_action('pre_get_posts','cs_pre_get_posts');

         function cs_pre_get_posts($query)
         {
                 if (is_admin() || !$query->is_main_query()) {
                         return;
                 }

                 if (!lightning_is_woo_page() && !is_singular() &&
 !is_search() && !is_404()) {
                         $meta_query_args = array(
                                 'relation' => 'OR',
                                 'exists' => array(
                                         'key' => 'プラン',
                                         'compare' => 'EXISTS',
                                 ),
                                 'notexists' => array(
                                         'key' => 'プラン',
                                         'compare' => 'NOT EXISTS',
                                 ),
                         );
                         $query->set('meta_query', $meta_query_args);
                         $query->set( 'orderby', array('exists' => 'desc',
 'notexists' => 'desc', 'date' => 'desc') );
                 }
         }
 }}}

 I could not reproduce the issue with this code. I have not yet tested
 whether the sorting works as expected, but it does return some post IDs
 and does not throw an error.

 The resulting SQL query is:

 `SELECT SQL_CALC_FOUND_ROWS develop_posts.ID FROM develop_posts LEFT JOIN
 develop_postmeta ON ( develop_posts.ID = develop_postmeta.post_id ) LEFT
 JOIN develop_postmeta AS mt1 ON ( develop_posts.ID = mt1.post_id AND
 mt1.meta_key = 'プラン' ) WHERE 1=1 AND ( develop_postmeta.meta_key = 'プ
 ラン' OR mt1.post_id IS NULL ) AND ((develop_posts.post_type = 'post' AND
 (develop_posts.post_status = 'publish' OR develop_posts.post_status =
 'private'))) GROUP BY develop_posts.ID ORDER BY
 CAST(develop_postmeta.meta_value AS CHAR) DESC, CAST(mt1.meta_value AS
 CHAR) DESC, develop_posts.post_date DESC LIMIT 0, 10`

 1. Does the issue still happen with all plugins disabled and a default
 theme (Twenty Twenty-Three) activated?
 2. Could you share the SQL query you get?

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/57767#comment:2>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform