• Sergei Petrunia's avatar
    MDEV-17155: Incorrect ORDER BY optimization: full index scan is used instead of range · e63b84b9
    Sergei Petrunia authored
    The bug was this scenario:
    1. Join optimizer picks a range plan on index IDX1
       (This index doesn't match the ORDER BY clause, so sorting will be needed)
    2. Index Condition Pushdown pushes a part of WHERE down. The pushed
       condition is removed from SQL_SELECT::cond
    3. test_if_skip_sort_order() figures that it's better to use IDX2
       (as it will match ORDER BY ... LIMIT and so will execute faster)
    
    3.1 It sees that there was a possible range access on IDX2. It tries to
       construct it by calling SQL_SELECT::test_quick_select(), but alas,
       SQL_SELECT::cond doesn't have all parts of WHERE anymore.
       So it uses full index scan which is slow.
    
    (The execution works fine because there's code further in test_if_skip_sort_order()
    which "Unpushes" the index condition and restores the original WHERE clause.
    It was just the test_quick_select call that suffered).
    e63b84b9
sql_select.cc 854 KB