• Neeraj Bisht's avatar
    Bug#11751794 MYSQL GIVES THE WRONG RESULT WITH SOME SPECIAL USAGE · eb0390a5
    Neeraj Bisht authored
    Consider the following query:
    
    SELECT f_1,..,f_m, AGGREGATE_FN(C)
    FROM t1
    WHERE ...
    GROUP BY ...
    
    Loose index scan ("Using index for group-by") can be used for
    this query if there is an index 'i' covering all fields in the
    select list, and the GROUP BY clause makes up a prefix f1,...,fn
    of 'i'. Furthermore, according to rule NGA2 of
    get_best_group_min_max(), the WHERE clause must contain a
    conjunction of equality predicates for all fields fn+1,...,fm.
    
    The problem in this bug was that a query with WHERE clause that
    broke NGA2(NGA: Non Group Attribuite) was not detected and therefore 
    used loose index scan.
    This lead to wrong result. The query had an index
    covering (c1,c2) and had:
      "WHERE (c1 = 1 AND c2 = 'a') OR (c1 = 2 AND c2 = 'b')
       GROUP BY c1"
    or 
      "WHERE (c1 = 1 ) OR (c1 = 2 AND c2 = 'b')
       GROUP BY c1"
    
    
    This WHERE clause cannot be transformed to a conjunction of
    equality predicates.
    
    The solution is to introduce another rule, NGA3, that complements
    NGA2. NGA3 says that if a gap field (field between those
    listed in GROUP BY and C in the index) has a predicate, then
    there can only be one range in the query. This requirement is
    more strict than it has to be in theory. BUG 15947433 will deal
    with that.
    eb0390a5
opt_range.cc 373 KB