diff options
| -rw-r--r-- | mysql-test/r/distinct.result | 12 | ||||
| -rw-r--r-- | mysql-test/r/group_min_max.result | 22 | ||||
| -rw-r--r-- | mysql-test/t/distinct.test | 16 | ||||
| -rw-r--r-- | mysql-test/t/group_min_max.test | 16 | ||||
| -rw-r--r-- | sql/sql_select.cc | 11 |
5 files changed, 77 insertions, 0 deletions
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index a6ad95570f8..89b17d69f40 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -533,3 +533,15 @@ select count(distinct concat(x,y)) from t1; count(distinct concat(x,y)) 2 drop table t1; +CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a,b)); +INSERT INTO t1 VALUES (1, 101); +INSERT INTO t1 SELECT a + 1, a + 101 FROM t1; +INSERT INTO t1 SELECT a + 2, a + 102 FROM t1; +INSERT INTO t1 SELECT a + 4, a + 104 FROM t1; +INSERT INTO t1 SELECT a + 8, a + 108 FROM t1; +EXPLAIN SELECT DISTINCT a,a FROM t1 WHERE b < 12 ORDER BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL PRIMARY 8 NULL 16 Using where; Using index +SELECT DISTINCT a,a FROM t1 WHERE b < 12 ORDER BY a; +a a +DROP TABLE t1; diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index 6370f7699b3..f9b55cc6a7b 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -2116,3 +2116,25 @@ COUNT(DISTINCT a) 1 DROP TABLE t1; DROP PROCEDURE a; +CREATE TABLE t1 (a varchar(64) NOT NULL default '', PRIMARY KEY(a)); +INSERT INTO t1 (a) VALUES +(''), ('CENTRAL'), ('EASTERN'), ('GREATER LONDON'), +('NORTH CENTRAL'), ('NORTH EAST'), ('NORTH WEST'), ('SCOTLAND'), +('SOUTH EAST'), ('SOUTH WEST'), ('WESTERN'); +EXPLAIN SELECT DISTINCT a,a FROM t1 ORDER BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range NULL PRIMARY 66 NULL 12 Using index for group-by +SELECT DISTINCT a,a FROM t1 ORDER BY a; +a a + +CENTRAL CENTRAL +EASTERN EASTERN +GREATER LONDON GREATER LONDON +NORTH CENTRAL NORTH CENTRAL +NORTH EAST NORTH EAST +NORTH WEST NORTH WEST +SCOTLAND SCOTLAND +SOUTH EAST SOUTH EAST +SOUTH WEST SOUTH WEST +WESTERN WESTERN +DROP TABLE t1; diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test index 45bd0c7a51c..09f07c2852f 100644 --- a/mysql-test/t/distinct.test +++ b/mysql-test/t/distinct.test @@ -382,3 +382,19 @@ INSERT INTO t1 VALUES select count(distinct x,y) from t1; select count(distinct concat(x,y)) from t1; drop table t1; + +# +# Bug #18068: SELECT DISTINCT +# +CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a,b)); + +INSERT INTO t1 VALUES (1, 101); +INSERT INTO t1 SELECT a + 1, a + 101 FROM t1; +INSERT INTO t1 SELECT a + 2, a + 102 FROM t1; +INSERT INTO t1 SELECT a + 4, a + 104 FROM t1; +INSERT INTO t1 SELECT a + 8, a + 108 FROM t1; + +EXPLAIN SELECT DISTINCT a,a FROM t1 WHERE b < 12 ORDER BY a; +SELECT DISTINCT a,a FROM t1 WHERE b < 12 ORDER BY a; + +DROP TABLE t1; diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test index bff07366ec2..d9836ccc8df 100644 --- a/mysql-test/t/group_min_max.test +++ b/mysql-test/t/group_min_max.test @@ -782,3 +782,19 @@ SELECT COUNT(DISTINCT a) FROM t1 WHERE a=0; DROP TABLE t1; DROP PROCEDURE a; + +# +# Bug #18068: SELECT DISTINCT +# + +CREATE TABLE t1 (a varchar(64) NOT NULL default '', PRIMARY KEY(a)); + +INSERT INTO t1 (a) VALUES + (''), ('CENTRAL'), ('EASTERN'), ('GREATER LONDON'), + ('NORTH CENTRAL'), ('NORTH EAST'), ('NORTH WEST'), ('SCOTLAND'), + ('SOUTH EAST'), ('SOUTH WEST'), ('WESTERN'); + +EXPLAIN SELECT DISTINCT a,a FROM t1 ORDER BY a; +SELECT DISTINCT a,a FROM t1 ORDER BY a; + +DROP TABLE t1; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index d640e45c058..86a3814cda2 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -12406,6 +12406,17 @@ create_distinct_group(THD *thd, Item **ref_pointer_array, { if (!item->const_item() && !item->with_sum_func && !item->marker) { + /* + Don't put duplicate columns from the SELECT list into the + GROUP BY list. + */ + ORDER *ord_iter; + for (ord_iter= group; ord_iter; ord_iter= ord_iter->next) + if ((*ord_iter->item)->eq(item, 1)) + break; + if (ord_iter) + continue; + ORDER *ord=(ORDER*) thd->calloc(sizeof(ORDER)); if (!ord) return 0; |