MySQL select query result set changes based on column order
- by user197191
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.
