SQL query error with multiple order specifications in pre_get_posts.

[kuroro111]

​https://ja.wordpress.org/support/topic/pre_get_posts%e3%81%a7%e8%a4%87%e6%95%b0%e3%81%ae%e4%b8%a6%e3%81%b3%e9%a0%86%e6%8c%87%e5%ae%9a%e3%81%a7%e3%82%a8%e3%83%a9%e3%83%bc/#post-11682395

We have started a thread in the above forum but have not been able to resolve the issue.

You can refer to the URL for more details, but when I specify multiple sorting conditions based on the value of a custom field, the results are not displayed properly, and when I throw the query portion of the request in $query directly to SQL in the hook point of pre_get_posts, the following error is returned The following error is returned.

Expression #1 of ORDER BY clause is not in GROUP BY clause and contains non-aggregated column 'local.wp_postmeta.meta_value' which is not functionally dependent on columns in the GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Is this part of a bug?

[SergeyBiryukov]

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?

[kuroro111]

Supplementary information.

wp-includes/class-wp-query.php
There is a pre_get_posts hook in get_posts() in the above file.
Just before the return of this function, you can retrieve the SQL query from the rewritten $this->request.

I am working with Local by flyfeel and when I type the SQL command directly from the admin screen of the database tool Adminer 4.8.1, I get the following error.
I have tried this in the environment immediately after the initial installation and the following error is displayed.
However, what is different from the initial environment is that the reordering is successful.
Perhaps a different query was being run in the first environment due to a plugin or customisation, which we will continue to investigate, but it does not appear to be a bug.

I don't have a good understanding of what the query error is, but if it looks OK I would like to close this ticket.

Query error (1055): Expression #1 of ORDER BY clause is not in GROUP BY clause and contains non-aggregated column 'local.wp_postmeta.meta_value' which is not functionally dependent on columns in the GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

[Otto42]

Can you please post the SQL query as you get it?

[kuroro111]

SELECT SQL_CALC_FOUND_ROWS  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 = 'プラン' )
					WHERE 1=1  AND ( 
  wp_postmeta.meta_key = 'プラン' 
  OR 
  mt1.post_id IS NULL
) AND ((wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' 
OR wp_posts.post_status = 'private')))
					GROUP BY wp_posts.ID
					ORDER BY CAST(wp_postmeta.meta_value AS CHAR) DESC, CAST(mt1.meta_value AS CHAR) DESC, wp_posts.post_date DESC
					LIMIT 0, 10
				

[kuroro111]

Sorry, the above query, but I posted it once by mistake and have corrected it.