[wp-trac] [WordPress Trac] #48280: orderby meta_value breaks with newer versions of MySQL

[WordPress Trac]

#48280: orderby meta_value breaks with newer versions of MySQL
---------------------------+-----------------------------
 Reporter:  KodieGrantham  |      Owner:  (none)
     Type:  defect (bug)   |     Status:  new
 Priority:  normal         |  Milestone:  Awaiting Review
Component:  Query          |    Version:  5.2.3
 Severity:  normal         |   Keywords:
  Focuses:                 |
---------------------------+-----------------------------
 When running a query like this:
 {{{#!php
 <?php
 $posts = new WP_Query(array(
   'post_type'           => 'course_record',
   'posts_per_page'      => -1,
   'orderby'             => 'meta_value',
   'meta_key'            => 'start_date'
 ));
 ?>
 }}}

 which results in the following SQL query:
 {{{#!sql
 SELECT   wp_posts.* FROM wp_posts  INNER JOIN wp_postmeta ON ( wp_posts.ID
 = wp_postmeta.post_id ) WHERE 1=1  AND ( wp_postmeta.meta_key =
 'start_date' ) AND wp_posts.post_type = 'course_record' AND
 (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY
 wp_postmeta.meta_value DESC
 }}}

 On MySQL v5.7.25, it works fine and returns posts as expected.
 On MySQL v8.0.16, it returns zero posts without any errors.

 The weird thing is, if I try to run the SQL statement from above directly
 into the MySQL console '''on either MySQL version''', I get the following
 error:
 {{{#!text
 ERROR 1055 (42000): Expression #1 of ORDER BY clause is not in GROUP BY
 clause and contains nonaggregated column
 'wp_artofed.wp_postmeta.meta_value' which is not functionally dependent on
 columns in GROUP BY clause; this is incompatible with
 sql_mode=only_full_group_by
 }}}

 I also get the same error if I try the SQL statement directly in PHP
 outside of WordPress '''again, on either MySQL version''':
 {{{#!php
 <?php
 $conn = new mysqli($db_host, $db_user, $db_pass, $db_name);
 if ($conn->connect_error) die("Connection failed: $conn->connect_error");
 if (!$results = $conn->query("SELECT   wp_posts.* FROM wp_posts  INNER
 JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1  AND (
 wp_postmeta.meta_key = 'start_date' ) AND wp_posts.post_type =
 'course_record' AND (wp_posts.post_status = 'publish') GROUP BY
 wp_posts.ID ORDER BY wp_postmeta.meta_value DESC")) {
   die("Query Error: $conn->error");
 }
 ?>
 }}}

 However it works if I use `$wpdb->get_results` '''on MySQL v5.7.25''' but
 not v8.0.16:
 {{{#!php
 <?php
 global $wpdb;
 $results = $wpdb->get_results("SELECT   wp_posts.* FROM wp_posts  INNER
 JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1  AND (
 wp_postmeta.meta_key = 'start_date' ) AND wp_posts.post_type =
 'course_record' AND (wp_posts.post_status = 'publish') GROUP BY
 wp_posts.ID ORDER BY wp_postmeta.meta_value DESC");
 ?>
 }}}

 I was able to get everything working however by adding
 `wp_postmeta.meta_value` to the `GROUP BY` clause:
 {{{#!php
 <?php
 add_filter('posts_groupby', 'fix_wp_only_full_group_by', 10, 2);
 function fix_wp_only_full_group_by($groupby, $query) {
   if (
     !empty($groupby) &&
     array_key_exists('orderby', $query->query_vars) &&
     $query->query_vars['orderby'] === 'meta_value'
   ) {
     global $wpdb;
     $groupby .= ", {$wpdb->postmeta}.meta_value";
   }
   return $groupby;
 }
 ?>
 }}}

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