Make WordPress Core

Opened 21 months ago

Last modified 21 months ago

#57767 new defect (bug)

SQL query error with multiple order specifications in pre_get_posts.

Reported by: kuroro111's profile kuroro111 Owned by:
Milestone: Awaiting Review Priority: normal
Severity: critical Version: 6.1.1
Component: Query Keywords:
Focuses: Cc:

Description (last modified by Otto42)

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?

Change History (6)

#1 @Otto42
21 months ago

  • Description modified (diff)

#2 @SergeyBiryukov
21 months ago

  • Component changed from General to Query

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?

#3 @kuroro111
21 months ago

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

#4 @Otto42
21 months ago

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

#5 @kuroro111
21 months ago

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 )  LEFT JOIN wp_postmeta AS mt2 ON ( wp_posts.ID = mt2.post_id AND mt2.meta_key = 'プラン' )
					WHERE 1=1  AND ( 
  wp_postmeta.meta_key = 'プラン' 
  AND 
  ( 
    mt1.meta_key = 'プラン' 
    OR 
    mt2.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 wp_postmeta.meta_value DESC, wp_posts.post_date DESC
					LIMIT 0, 10
Version 0, edited 21 months ago by kuroro111 (next)

#6 @kuroro111
21 months ago

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

Note: See TracTickets for help on using tickets.