diff options
| -rw-r--r-- | mysql-test/r/distinct.result | 11 | ||||
| -rw-r--r-- | mysql-test/t/distinct.test | 11 | ||||
| -rw-r--r-- | sql/sql_select.cc | 60 |
3 files changed, 52 insertions, 30 deletions
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index c6c614a5646..6cdf4063291 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -555,3 +555,14 @@ EXPLAIN SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 DROP TABLE t1,t2; +CREATE TABLE t1 (a int primary key, b int); +INSERT INTO t1 (a,b) values (1,1), (2,3), (3,2); +explain SELECT DISTINCT a, b FROM t1 ORDER BY b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort +SELECT DISTINCT a, b FROM t1 ORDER BY b; +a b +1 1 +3 2 +2 3 +DROP TABLE t1; diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test index 8ca6f350b8d..2a87427a2b6 100644 --- a/mysql-test/t/distinct.test +++ b/mysql-test/t/distinct.test @@ -378,4 +378,15 @@ EXPLAIN SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d; DROP TABLE t1,t2; +# +# Bug 21456: SELECT DISTINCT(x) produces incorrect results when using order by +# +CREATE TABLE t1 (a int primary key, b int); + +INSERT INTO t1 (a,b) values (1,1), (2,3), (3,2); + +explain SELECT DISTINCT a, b FROM t1 ORDER BY b; +SELECT DISTINCT a, b FROM t1 ORDER BY b; +DROP TABLE t1; + # End of 4.1 tests diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 605ef49bb07..a6bba15d70a 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -648,6 +648,36 @@ JOIN::optimize() if (!order && org_order) skip_sort_order= 1; } + /* + Check if we can optimize away GROUP BY/DISTINCT. + We can do that if there are no aggregate functions and the + fields in DISTINCT clause (if present) and/or columns in GROUP BY + (if present) contain direct references to all key parts of + an unique index (in whatever order). + Note that the unique keys for DISTINCT and GROUP BY should not + be the same (as long as they are unique). + + The FROM clause must contain a single non-constant table. + */ + if (tables - const_tables == 1 && (group_list || select_distinct) && + !tmp_table_param.sum_func_count) + { + if (group_list && + list_contains_unique_index(join_tab[const_tables].table, + find_field_in_order_list, + (void *) group_list)) + { + group_list= 0; + group= 0; + } + if (select_distinct && + list_contains_unique_index(join_tab[const_tables].table, + find_field_in_item_list, + (void *) &fields_list)) + { + select_distinct= 0; + } + } if (group_list || tmp_table_param.sum_func_count) { if (! hidden_group_fields && rollup.state == ROLLUP::STATE_NONE) @@ -717,36 +747,6 @@ JOIN::optimize() if (old_group_list && !group_list) select_distinct= 0; } - /* - Check if we can optimize away GROUP BY/DISTINCT. - We can do that if there are no aggregate functions and the - fields in DISTINCT clause (if present) and/or columns in GROUP BY - (if present) contain direct references to all key parts of - an unique index (in whatever order). - Note that the unique keys for DISTINCT and GROUP BY should not - be the same (as long as they are unique). - - The FROM clause must contain a single non-constant table. - */ - if (tables - const_tables == 1 && (group_list || select_distinct) && - !tmp_table_param.sum_func_count) - { - if (group_list && - list_contains_unique_index(join_tab[const_tables].table, - find_field_in_order_list, - (void *) group_list)) - { - group_list= 0; - group= 0; - } - if (select_distinct && - list_contains_unique_index(join_tab[const_tables].table, - find_field_in_item_list, - (void *) &fields_list)) - { - select_distinct= 0; - } - } if (!group_list && group) { order=0; // The output has only one row |