组by，Order在MySQL中工作，而不是MariaDB

Question

一段时间前，我们尝试了一些非常复杂(对我们来说)的数据库查询，这些查询将一些动态meta_values与WordPress中的一般Post信息相结合，从而得到了一个很好的、完整的key=>value对列表。多年来，它一直运行得很好，但是现在我们已经转向了MariaDB，它已经不能正常订购了，我不知道为什么。请参阅下面的一个大型长示例或https://screencast.com/t/mFILYpWf屏幕截图，以获得更多格式。

有什么想法可以让我如何让它首先由menu_order，last_name，然后first_name？

一如既往地感谢你！！

SELECT *
FROM
(
    SELECT wp_posts.ID, wp_posts.menu_order, wp_posts.post_title,
        wp_posts.post_name,
        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_job_title'
                 THEN wp_postmeta.meta_value END ) AS 'job_title',
        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_prefix'
                 THEN wp_postmeta.meta_value END ) AS 'prefix',
        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_first_name'
                 THEN wp_postmeta.meta_value END ) AS 'first_name',
        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_middle_name'
                 THEN wp_postmeta.meta_value END ) AS 'middle_name',
        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_last_name'
                 THEN wp_postmeta.meta_value END ) AS 'last_name',
        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_suffix'
                 THEN wp_postmeta.meta_value END ) AS 'suffix',
        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_languages'
                 THEN wp_postmeta.meta_value END ) AS 'languages',
        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_email'
                 THEN wp_postmeta.meta_value END ) AS 'email',
        MAX(CASE WHEN wp_postmeta.meta_key = 'lawyer_phone'
                 THEN wp_postmeta.meta_value END ) AS 'phone',
        MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_offices' THEN wp_postmeta.meta_value END ) AS 'offices',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_linkedin' THEN wp_postmeta.meta_value END ) AS 'linkedin',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_biography' THEN wp_postmeta.meta_value END ) AS 'biography',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_field_credentials' THEN wp_postmeta.meta_value END ) AS 'bio_field_credentials',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_field_affiliations' THEN wp_postmeta.meta_value END ) AS 'bio_field_affiliations',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_field_accolades' THEN wp_postmeta.meta_value END ) AS 'bio_field_accolades',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_field_notable_decisions' THEN wp_postmeta.meta_value END ) AS 'bio_field_notable_decisions',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_field_education_admissions' THEN wp_postmeta.meta_value END ) AS 'bio_field_education_admissions',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_bio_image' THEN wp_postmeta.meta_value END ) AS 'bio_image',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_directory_image' THEN wp_postmeta.meta_value END ) AS 'directory_image',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_pdf_image' THEN wp_postmeta.meta_value END ) AS 'pdf_image',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_practices' THEN wp_postmeta.meta_value END ) AS 'practices',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_related_posts' THEN wp_postmeta.meta_value END ) AS 'related_posts',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_other_news' THEN wp_postmeta.meta_value END ) AS 'other_news',MAX( CASE WHEN wp_postmeta.meta_key = 'lawyer_related_staff' THEN wp_postmeta.meta_value END ) AS 'related_staff', concat( 'http://wiedner.localhost/lawyer/', wp_posts.post_name, '/' ) AS 'permalink' FROM wp_posts LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE wp_posts.post_status = 'publish' AND wp_posts.post_type = 'lawyer'
    GROUP BY wp_posts.ID
    ORDER BY menu_order asc, last_name asc, first_name asc
) AS t
WHERE 1=1

Answer 1

通过去掉外部SELECT来简化它！

原因是“派生”表(您的子查询)没有顺序。(这在Standard中已经存在很久了，但直到最近才有MariaDB和后来的MySQL利用它。)也就是说，执行是合理的，以摆脱您精心编写的ORDER BY。

但是，通过停止内部查询，它有义务遵守ORDER BY。

Answer 2

MariaDB可能有不同的模式集。试试这个，看看这里

 SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',));

另外，这可能是一个副本，请参阅此相关问题。