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 16:11:14 gkodinov@stripped +3 -0
  BUG#18068: SELECT DISTINCT
  
  When converting DISTINCT to GROUP BY where the columns 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 key are not recognized as such and treated as non-key
  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 ..." to
  "SELECT a,a FROM ... GROUP BY a" instead of "SELECT a,a FROM .. GROUP BY
  a,a". We can even transform "SELECT DISTINCT a,b,a FROM ..." to
  "SELECT a,b,a FROM ... GROUP 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 16:11:09 gkodinov@stripped +19 -0
    duplicates check and removal

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

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

# 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 16:11:09 +03:00
@@ -835,6 +835,14 @@
 	    /*
 	      Force MySQL to read the table in sorted order to get result in
 	      ORDER BY order.
+              gkodinov: this IMHO is not the right place to do that, as it 
+              introduces a difference between SELECT DISTINCT ... and 
+              SELECT ... GROUP BY ... processing, but it seems that this 
+              fixes BUG#275 (witch apparently needs to be fixed in a 
+              different place) so I'll not remove it.
+              However to circumvent the problems that this assignment 
+              creates (BUG#18068) I'm introducing duplicate removal 
+              in the create_distinct_group() function.
 	    */
 	    tmp_table_param.quick_group=0;
 	  }
@@ -12698,6 +12706,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 (BUG#18068).
+      */
+      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.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 16:11:09 +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 16:11:09 +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;