• Gleb Shchepa's avatar
    Bug #38745: MySQL 5.1 optimizer uses filesort for ORDER BY · 3ca98f76
    Gleb Shchepa authored
                when it should use index
    
    Sometimes the LEFT/RIGHT JOIN with an empty table caused an
    unnecessary filesort.
    
    Sample query, where t1.i1 is indexed and t3 is empty:
    
      SELECT t1.*, t2.* FROM t1 JOIN t2 ON t1.i1 = t2.i2
                           LEFT JOIN t3 ON t2.i2 = t3.i3
        ORDER BY t1.i1 LIMIT 5;
    
    The server erroneously used an item of empty outer-joined
    table as a common constant of a Item_equal (multi-equivalence
    expression).
    By the fix for the bug 16590 the constant status of such
    an item has been propagated to st_table::const_key_parts
    map bits related to other Item_equal argument-related
    key parts (those are obviously not constant in our case).
    As far as test_if_skip_sort_order function skips constant
    prefixes of testing keys, this caused an ignorance of
    available indices, since some prefixes were marked as
    constant by mistake.
    3ca98f76
order_by.result 43.8 KB