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.
(COUNT(*) >= '1')Why? And does the query still return incorrect results if you remove the quotes from numbers?GROUP BY. You have columns in theSELECTlist that are not in theGROUP BYlist. This usually results in indeterminate behaviour. While the query may have showed expected results in 5.5, that was accidental and changes in 5.6 optimizer code may have caused different indexes to be used and different results to appear.scorewhich is in a SUM() function in the select-list. Also every join is a LEFT JOIN, despite some having conditions in the WHERE clause. Did you design this as a custom query, or is it part of the stock Views module?SELECT DISTINCT...GROUP BYseems like a strange combination, but ultimately 'score' in theGROUP BYis absolutely ambiguous and whichever thing you're intending to reference -- either theSUM()expression orsearch_index.scoreshould be used explicitly in theGROUP BY(though I can't see how either of them makes sense) so the third option would be to see what happens if it's eliminated from theGROUP BY.