Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from serverfault.com Nov 4 '13 at 21:06

This question came from our site for professional system and network administrators.

    
I see you use quotes around numbers, like in (COUNT(*) >= '1') Why? And does the query still return incorrect results if you remove the quotes from numbers? –  ypercube Nov 4 '13 at 21:13
    
But I guess that the reason for the different behaviour is the non-standard use of GROUP BY. You have columns in the SELECT list that are not in the GROUP BY list. 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. –  ypercube Nov 4 '13 at 21:37
    
Have the developers of this module documented somewhere the database design? (so we know the primary keys of the tables and other constraints - without having to download/install Drupal) –  ypercube Nov 4 '13 at 21:57
1  
This query is nonsensical: one of the GROUP BY columns is score which 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? –  Bill Karwin Nov 5 '13 at 0:56
2  
SELECT DISTINCT ... GROUP BY seems like a strange combination, but ultimately 'score' in the GROUP BY is absolutely ambiguous and whichever thing you're intending to reference -- either the SUM() expression or search_index.score should be used explicitly in the GROUP 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 the GROUP BY. –  Michael - sqlbot Nov 5 '13 at 4:03

1 Answer 1

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.

share|improve this answer
    
there is also this problem with MySQL that the GROUP BY is also using an implict ORDER BY.. so it looks like you need to force MySQL into sorting correctly in your ORDER BY statement –  Raymond Nijland Dec 7 '13 at 16:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.