orderby meta_value breaks with newer versions of MySQL

[KodieGrantham]

When running a query like this:

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

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:

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
$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
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
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;
}
?>

[KodieGrantham]

Of course my fix won't work for the get_posts function unless you set suppress_filters to false (since it's enabled by default with that function).

[rfair404]

In case it is helpful to anyone finding this ticket, I had a similar problem. On a fresh install of WP 5.3.2 running on Digital Ocean, utilizing their managed database service. I was getting the same error. After some research I found that it was related to the ONLY_FULL_GROUP_BY setting, as suggested by this article: ​https://www.percona.com/blog/2019/05/13/solve-query-failures-regarding-only_full_group_by-sql-mode/ and ​https://www.digitalocean.com/community/questions/sql_mode-only_full_group_by

I changed this setting in my database configuration (added the STRICT_TRANS_TABLE) - which resolved the issue.