MySQL select query result set changes based on column order

MySQL

I have a drupal 7 site using the Views module to back-end site content search results. The same query with the same dataset returns different results from MySQL 5.5.28 to MySQL 5.6.14. The results from 5.5.28 are the correct, expected results. The results from 5.6.14 are not. If, however, I simply move a column in the select statement, the query returns the correct results.

Here is the code-generated query in question (modified for readability). I apologize for the length; I couldn't find a way to reproduce it without the whole query:

SELECT 
    DISTINCT node_node_revision.nid AS node_node_revision_nid, 
    node_revision.title AS node_revision_title, 
    node_field_revision_field_position_institution_ref.nid AS node_field_revision_field_position_institution_ref_nid, 
    node_revision.vid AS vid, 
    node_revision.nid AS node_revision_nid, 
    node_node_revision.title AS node_node_revision_title, 
    SUM(search_index.score * search_total.count) AS score, 
    'node' AS field_data_field_system_inst_name_node_entity_type, 
    'node' AS field_revision_field_position_college_division_node_entity_t, 
    'node' AS field_revision_field_position_department_node_entity_type, 
    'node' AS field_revision_field_search_lvl_degree_lvls_node_entity_type, 
    'node' AS field_revision_field_position_app_deadline_node_entity_type, 
    'node' AS field_revision_field_position_start_date_node_entity_type, 
    'node' AS field_revision_body_node_entity_type
FROM 
    node_revision node_revision
    LEFT JOIN node node_node_revision 
        ON node_revision.nid = node_node_revision.nid
    LEFT JOIN field_revision_field_position_institution_ref field_revision_field_position_institution_ref 
        ON node_revision.vid = field_revision_field_position_institution_ref.revision_id 
        AND (field_revision_field_position_institution_ref.entity_type = 'node' AND field_revision_field_position_institution_ref.deleted = '0')
    LEFT JOIN node node_field_revision_field_position_institution_ref 
        ON field_revision_field_position_institution_ref.field_position_institution_ref_target_id = node_field_revision_field_position_institution_ref.nid
    LEFT JOIN field_revision_field_position_cip_code field_revision_field_position_cip_code 
        ON node_revision.vid = field_revision_field_position_cip_code.revision_id 
        AND (field_revision_field_position_cip_code.entity_type = 'node' AND field_revision_field_position_cip_code.deleted = '0')
    LEFT JOIN node node_field_revision_field_position_cip_code 
        ON field_revision_field_position_cip_code.field_position_cip_code_target_id = node_field_revision_field_position_cip_code.nid
    LEFT JOIN node node_node_revision_1 
        ON node_revision.nid = node_node_revision_1.nid
    LEFT JOIN field_revision_field_position_vacancy_status field_revision_field_position_vacancy_status 
        ON node_revision.vid = field_revision_field_position_vacancy_status.revision_id 
        AND (field_revision_field_position_vacancy_status.entity_type = 'node' AND field_revision_field_position_vacancy_status.deleted = '0')
    LEFT JOIN search_index search_index 
        ON node_revision.nid = search_index.sid
    LEFT JOIN search_total search_total 
        ON search_index.word = search_total.word
WHERE (
        ( 
            (node_node_revision.status = '1') 
            AND (node_node_revision.type IN  ('position'))
            AND (field_revision_field_position_vacancy_status.field_position_vacancy_status_target_id IN  ('38')) 
            AND( 
                    (search_index.type = 'node') AND( (search_index.word = 'accountant') )
                )
            AND (
                    (node_revision.vid=node_node_revision.vid AND node_node_revision.status=1)
                ) 
        )
    )
GROUP BY 
    search_index.sid, 
    vid, 
    score, 
    field_data_field_system_inst_name_node_entity_type, 
    field_revision_field_position_college_division_node_entity_t, 
    field_revision_field_position_department_node_entity_type, 
    field_revision_field_search_lvl_degree_lvls_node_entity_type, 
    field_revision_field_position_app_deadline_node_entity_type, 
    field_revision_field_position_start_date_node_entity_type, 
    field_revision_body_node_entity_type
HAVING (
        ( (COUNT(*) >= '1') )
    )
ORDER BY 
    node_node_revision_title ASC
LIMIT 20 OFFSET 0;

Again, this query returns different sets of results from MySQL 5.5.28 (correct) to 5.6.14 (incorrect). If I move the column named "score" (the SUM() column) to the end of the column list, the query returns the correct set of results in both versions of MySQL.

My question is: Is this expected behavior (and why), or is this a bug? I'm on the verge of reverting my entire environment back to 5.5 because of this.

Best Answer

This appears to be related to this bug:

http://bugs.mysql.com/bug.php?id=70657

There is some background on this behavior here: http://mechanics.flite.com/blog/2013/02/12/why-i-use-only-full-group-by/

The gist is that group by in MySQL doesn't operate (by default) the same way it does in other RDBMS's.