• Chaithra Gopalareddy's avatar
    Bug#12713907:STRANGE OPTIMIZE & WRONG RESULT UNDER · 81058259
    Chaithra Gopalareddy authored
                       ORDER BY COUNT(*) LIMIT.
    
    PROBLEM:
    With respect to problem in the bug description, we
    exhibit different behaviors for the two tables
    presented, because innodb statistics (rec_per_key
    in this case) are updated for the first table
    and not so for the second one. As a result the
    query plan gets changed in test_if_skip_sort_order
    to use 'index' scan. Hence the difference in the
    explain output. (NOTE: We can reproduce the problem
    with first table by reducing the number of tuples
    and changing the table structure)
    
    The varied output w.r.t the query on the second table
    is because of the result in the query plan change.
    When a query plan is changed to use 'index' scan,
    after the call to test_if_skip_sort_order, we set
    keyread to TRUE immedietly. If for some reason
    we drop this index scan for a filesort later on,
    we fetch only the keys not the entire tuple.
    As a result we would see junk values in the result set.
    
    Following is the code flow:
    
    Call test_if_skip_sort_order
    -Choose an index to give sorted output
    -If this is a covering index, set_keyread to TRUE
    -Set the scan to INDEX scan
    
    Call test_if_skip_sort_order second time
    -Index is not chosen (note that we do not pass the
    actual limit value second time. Hence we do not choose
    index scan second time which in itself is a bug fixed
    in 5.6 with WL#5558)
    -goto filesort
    
    Call filesort
    -Create quick range on a different index
    -Since keyread is set to TRUE, we fetch only the columns of
    the index
    -results in the required columns are not fetched
    
    FIX:
    Remove the call to set_keyread(TRUE) from
    test_if_skip_sort_order. The access function which is
    'join_read_first' or 'join_read_last' calls set_keyread anyways.
    81058259
func_group_innodb.result 5 KB