• Monty's avatar
    MDEV-26585 Wrong query results when `using index for group-by` · 38058c04
    Monty authored
    The problem was that "group_min_max optimization" does not work if
    some aggregate functions, like COUNT(*), is used.
    The function get_best_group_min_max() is using the join->sum_funcs
    array to check which aggregate functions are used.
    The bug was that aggregates in HAVING where not yet added to
    join->sum_funcs at the time get_best_group_min_max() was called.
    
    Fixed by populate join->sum_funcs already in prepare, which means that
    all sum functions will be in join->sum_funcs in get_best_group_min_max().
    A benefit of this approach is that we can remove several calls to
    make_sum_func_list() from the code and simplify the function.
    
    I removed some wrong setting of 'sort_and_group'.
    This variable is set when alloc_group_fields() is called, as part
    of allocating the cache needed by end_send_group() and does not need
    to be set by other functions.
    
    One problematic thing was that Spider is using *join->sum_funcs to detect
    at which stage the optimizer is and do internal calculations of aggregate
    functions. Updating join->sum_funcs early caused Spider to fail when trying
    to find min/max values in opt_sum_query().
    Fixed by temporarily resetting sum_funcs during opt_sum_query().
    
    Reviewer: Sergei Petrunia
    38058c04
group_min_max.test 73.1 KB