bk commit into 5.0 tree (gkodinov:1.2115) BUG#18068

[kgeorge]

Below is the list of changes that have just been committed into a local
5.0 repository of kgeorge. When kgeorge does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.2115 06/05/09 18:13:01 gkodinov@stripped +5 -0
  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.

  sql/sql_select.cc
    1.409 06/05/09 18:12:55 gkodinov@stripped +11 -0
    duplicates check and removal

  mysql-test/t/group_min_max.test
    1.21 06/05/09 18:12:55 gkodinov@stripped +16 -0
    test case for the bug

  mysql-test/t/distinct.test
    1.23 06/05/09 18:12:55 gkodinov@stripped +16 -0
    test case for the bug without loose index scan

  mysql-test/r/group_min_max.result
    1.23 06/05/09 18:12:55 gkodinov@stripped +22 -0
    test case for the bug

  mysql-test/r/distinct.result
    1.44 06/05/09 18:12:55 gkodinov@stripped +12 -0
    test case for the bug without loose index scan

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	gkodinov
# Host:	rakia.gmz
# Root:	/home/kgeorge/mysql/5.0/B18068

--- 1.408/sql/sql_select.cc	2006-04-24 16:49:32 +03:00
+++ 1.409/sql/sql_select.cc	2006-05-09 18:12:55 +03:00
@@ -12698,6 +12698,17 @@
   {
     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;

--- 1.43/mysql-test/r/distinct.result	2006-03-05 18:47:10 +02:00
+++ 1.44/mysql-test/r/distinct.result	2006-05-09 18:12:55 +03:00
@@ -533,3 +533,15 @@
 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;

--- 1.22/mysql-test/t/distinct.test	2006-03-05 18:47:10 +02:00
+++ 1.23/mysql-test/t/distinct.test	2006-05-09 18:12:55 +03:00
@@ -382,3 +382,19 @@
 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;

--- 1.22/mysql-test/r/group_min_max.result	2006-03-31 12:39:27 +03:00
+++ 1.23/mysql-test/r/group_min_max.result	2006-05-09 18:12:55 +03:00
@@ -2116,3 +2116,25 @@
 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;

--- 1.20/mysql-test/t/group_min_max.test	2006-03-31 12:39:27 +03:00
+++ 1.21/mysql-test/t/group_min_max.test	2006-05-09 18:12:55 +03:00
@@ -782,3 +782,19 @@
 
 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;