get_zone_query() function not working on 5.7

[dev-erem]

It doesn't display posts for a zone, on the backend below is an error in the query generated by get_zone_query() method:

WordPress database error Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'wordpress.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 for query SELECT wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1 AND ( \n wp_postmeta.meta_key = '_zoninator_order_4942'\n) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'future' OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' OR wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value+0 ASC made by WP_Query::get_posts, referer: http://localhost/wordpress/wp-admin/admin.php?page=zoninator

Further, executed the select query directly on mysql, got the following response:

Error Code: 1055. Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'wordpress.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

Checked mysql documentation and got the following from there:

https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

Please check the issue.

Thanks.

[sboisvert]

@brettshumaker Can you take a look?

[brettshumaker]

@sboisvert I don't have anything set up on my local machine to run tests on MySQL 5.7, but reading up on the error and how 5.7 wants the queries to be structured, I think it's an issue with this line: https://github.com/Automattic/zoninator/blob/master/zoninator.php#L1084

The original GROUP BY ORDER BY clauses look like this: GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value+0 ASC

I think 5.7 is complaining that wp_posts.ID is not used in the ORDER BY clause. So changing the line mentioned above to $args['orderby'] = 'meta_value_num ID'; doesn't seem to affect the returned posts and changes those clauses to look like this: GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value+0 ASC, wp_posts.ID ASC

Tomorrow evening I'll work on setting up a way to check this on MySQL 5.7 and see if it fixes the issue. 👍

[sboisvert]

I think 5.7 is complaining that wp_posts.ID is not used in the ORDER BY clause. So changing the line mentioned above to $args['orderby'] = 'meta_value_num ID'; doesn't seem to affect the returned posts and changes those clauses to look like this: GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value+0 ASC, wp_posts.ID ASC

I think I'm confused :) Can you elaborate?

[brettshumaker]

@sboisvert I was just trying to think through why MySQL 5.7 would be throwing that error. But I was able to get a docker container set up with MySQL 5.7.22 and I couldn't reproduce this error.

I was able to add posts to a zone in the admin, save, return to the edit zone screen without errors. I was also able to run that query with $wpdb and received the correct set of posts.

@amjad-eremnews Can you confirm if you're still seeing this issue?

[mikeyarce]

Just wanted to add some thoughts to this one!

MySQL 5.7.5 and up detects functional dependance so if ONLY_FULL_GROUP_BY is set on the database, and you run the get_zone_query() method, you will get an error like this:

While this doesn't seem to break any functionality in my tests, we could fix this so errors don't show up.

What we would need to do is hook into posts_groupby and group our results by the Postmeta meta_value as well as the Posts ID which already is being done.

Example:

function zones_groupby( $groupby, $wp_query ) {
        global $wpdb;
        if ( isset( $wp_query->query['orderby'] ) && 'meta_value_num' === $wp_query->query['orderby'] ) {
            $groupby = "{$wpdb->posts}.ID, {$wpdb->postmeta}.meta_value";
    }
        return $groupby;
    }

This works for the frontend, but on the admin side we would also need to set suppress_filters to false here: https://github.com/Automattic/zoninator/blob/94052202947be61b80e617b08db4e0fbb1535c38/zoninator.php#L1113