• Neeraj Bisht's avatar
    Bug#12328597 - MULTIPLE COUNT(DISTINCT) IN SAME SELECT FALSE · 0edb58b1
    Neeraj Bisht authored
                   WITH COMPOSITE KEY COLUMNS
    
    Problem:-
    While running a SELECT query with several AGGR(DISTINCT) function 
    and these are referring to different field of same composite key, 
    Returned incorrect value.
    
    Analysis:-
    
    In a table, where we have composite key like (a,b,c)
    and when we give a query like
    
    select COUNT(DISTINCT b), SUM(DISTINCT a) from ....
    
    here, we first make a list of items in Aggr(distinct) function
    (which is a, b), where order of item doesn't matter. 
    and then we see, whether we have a composite key where the prefix 
    of index columns matches the items of the aggregation function.
    (in this case we have a,b,c).
    
    if yes, so we can use loose index scan and we need not perform 
    duplicate removal to distinct in our aggregate function.
    
    In our table, we traverse column marked with <-- and get the result as
    (a,b,c)      count(distinct b)           sum(distinct a)
                 treated as count b          treated as sum(a)
    (1,1,2)<--              1                      1		
    (1,2,2)<--              1++=2                  1+1=2
    (1,2,3)		
    (2,1,2)<--              2++=3                  1+1+2=4
    (2,2,2)<--              3++=4                  1+1+2+2=6
    (2,2,3)
    
    result will be 4,6, but it should be (2,3)
    
    As in this case, our assumption is incorrect. If we have
    query like 
    select count(distinct a,b), sum(distinct a,b)from ..
    then we can use loose index scan
    
    Solution:-
    In our query, when we have more then one aggr(distinct) function 
    then they should refer to same  fields like
    
    select count(distinct a,b), sum(distinct a,b) from .. 
    
    -->we can use loose scan index as both aggr(distinct) refer to same fields a,b.
    
    If they are referring to different field like
    
    select count(distinct a), sum(distinct b) from .. 
    
    -->will not use loose scan index as both aggr(distinct) refer to different fields.
    0edb58b1
sql_select.cc 577 KB