diff options
author | unknown <timour/tkatchaounov@dl145s.mysql.com> | 2006-08-28 16:08:48 +0200 |
---|---|---|
committer | unknown <timour/tkatchaounov@dl145s.mysql.com> | 2006-08-28 16:08:48 +0200 |
commit | 01ec88dcd8d49aede05ba1f22270708d132f3ffe (patch) | |
tree | 80f505f1d173a7fec5ebe6b0255891c9e5f5edc6 | |
parent | c17b36d9842703985a9f242515c811c660d6d074 (diff) | |
parent | e3181c59e7962657bf6a273db68e0649d5372bba (diff) | |
download | mariadb-git-01ec88dcd8d49aede05ba1f22270708d132f3ffe.tar.gz |
Merge tkatchaounov@bk-internal.mysql.com:/home/bk/mysql-5.0
into dl145s.mysql.com:/data/tkatchaounov/autopush/5.0-bug-21456
sql/sql_select.cc:
Auto merged
-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 | 68 |
3 files changed, 56 insertions, 34 deletions
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index a3d1e8bf3bb..86ab2141e2d 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -563,6 +563,17 @@ id IFNULL(dsc, '-') 2 line number two 3 line number three drop table t1; +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; CREATE TABLE t1 ( ID int(11) NOT NULL auto_increment, x varchar(20) default NULL, diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test index 61250a7105e..e517380ba9b 100644 --- a/mysql-test/t/distinct.test +++ b/mysql-test/t/distinct.test @@ -385,6 +385,17 @@ insert into t1 values (1, "line number one"), (2, "line number two"), (3, "line select distinct id, IFNULL(dsc, '-') from t1; drop table t1; +# +# 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 47eb19364ee..0ef4cb6da9d 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -812,6 +812,40 @@ 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 && + (!join_tab[const_tables].select || + !join_tab[const_tables].select->quick || + join_tab[const_tables].select->quick->get_type() != + QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX)) + { + 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) @@ -881,40 +915,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 && - (!join_tab[const_tables].select || - !join_tab[const_tables].select->quick || - join_tab[const_tables].select->quick->get_type() != - QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX)) - { - 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 |