• Chaithra Gopalareddy's avatar
    Bug#17909656 - WRONG RESULTS FOR A SIMPLE QUERY WITH GROUP BY · 5fa8e768
    Chaithra Gopalareddy authored
    Problem:
    If there is a predicate on a column referenced by MIN/MAX and
    that predicate is not present in all the disjunctions on
    keyparts earlier in the compound index, Loose Index Scan will
    not return correct result.
    
    Analysis:
    When loose index scan is chosen, range optimizer currently
    groups all the predicates that contain group parts separately
    and minmax parts separately. It therefore applies all the
    conditions on the group parts first to the fetched row.
    Then in the call to next_max, it processes the conditions
    which have min/max keypart.
    
    For ex in the following query:
    Select f1, max(f2) from t1 where (f1 = 10 and f2 = 13) or
    (f1 = 3) group by f1;
    Condition (f2 = 13) would be applied even for rows that
    satisfy (f1 = 3) thereby giving wrong results.
    
    Solution:
    Do not choose loose_index_scan for such cases. So a new rule
    WA2 is introduced to take care of the same.
    
    WA2: "If there are predicates on C, these predicates must
    be in conjuction to all predicates on all earlier keyparts
    in I."
    
    Todo the same, fix reuses the function get_constant_key_infix().
    Since this funciton will fail for all multi-range conditions, it
    is re-written to recognize that if the sub-conditions are
    equivalent across the disjuncts: it will now succeed.
    And to achieve this a new helper function is introduced called
    all_same().
    
    The fix also moves the test of NGA3 up to the former only
    caller, get_constant_key_infix().
    5fa8e768
group_min_max_innodb.test 8.25 KB