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 | Owned by: | |
---|---|---|---|
Milestone: | Awaiting Review | Priority: | normal |
Severity: | critical | Version: | 6.1.1 |
Component: | Query | Keywords: | |
Focuses: | Cc: |
Description (last modified by )
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)
#3
@
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
#5
@
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
Hi there, welcome to WordPress Trac! Thanks for the report.
For reference, the code from the topic linked above is:
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