summaryrefslogtreecommitdiff
path: root/mysql-test/t/distinct.test
diff options
context:
space:
mode:
authorunknown <gkodinov@mysql.com>2006-05-09 18:13:01 +0300
committerunknown <gkodinov@mysql.com>2006-05-09 18:13:01 +0300
commitbaae7a97d9e0c82941559c439e5799eec9f96d0c (patch)
treeeb8d0afd0429db7dd9a70b30d96d0bf919e892ff /mysql-test/t/distinct.test
parent33417297c5e11ff82aae7bc2d8b5a75968725905 (diff)
downloadmariadb-git-baae7a97d9e0c82941559c439e5799eec9f96d0c.tar.gz
BUG#18068: SELECT DISTINCT (with duplicates and covering index)
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
Diffstat (limited to 'mysql-test/t/distinct.test')
-rw-r--r--mysql-test/t/distinct.test16
1 files changed, 16 insertions, 0 deletions
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;