• unknown's avatar
    BUG#18068: SELECT DISTINCT (with duplicates and covering index) · baae7a97
    unknown authored
    When converting DISTINCT to GROUP BY where the columns are from the covering
    index and they are quoted twice in the SELECT list the optimizer is creating
    improper processing sequence. This is because of the fact that the columns
    of the covering index are not recognized as such and treated as non-index
    columns.
    
    Generally speaking duplicate columns can safely be removed from the GROUP
    BY/DISTINCT list because this will not add or remove new rows in the
    resulting set. Duplicates can be removed even if they are not consecutive
    (as is the case for ORDER BY, where the duplicate columns can be removed
    only if they are consecutive).
    
    So we can safely transform "SELECT DISTINCT a,a FROM ... ORDER BY a" to
    "SELECT a,a FROM ... GROUP BY a ORDER BY a" instead of 
    "SELECT a,a FROM .. GROUP BY a,a ORDER BY a". We can even transform 
    "SELECT DISTINCT a,b,a FROM ... ORDER BY a,b" to
    "SELECT a,b,a FROM ... GROUP BY a,b ORDER BY a,b".
    
    The fix to this bug consists of checking for duplicate columns in the SELECT
    list when constructing the GROUP BY list in transforming DISTINCT to GROUP
    BY and skipping the ones that are already in.
    
    
    mysql-test/r/distinct.result:
      test case for the bug without loose index scan
    mysql-test/r/group_min_max.result:
      test case for the bug
    mysql-test/t/distinct.test:
      test case for the bug without loose index scan
    mysql-test/t/group_min_max.test:
      test case for the bug
    sql/sql_select.cc:
      duplicates check and removal
    baae7a97
distinct.test 14.3 KB