bk commit into 4.1 tree (sergefp:1.2164) BUG#9213

[Sergey Petrunia]

Below is the list of changes that have just been committed into a local
4.1 repository of psergey. When psergey 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.2164 05/03/30 15:57:42 sergefp@stripped +4 -0
  Fix for BUG#9213: GROUP BY returns wrong query results:
  Make test_if_skip_sort_order() rebuild tab->ref if it decides to use an index
  different from the index join optimizer has choosen.  

  sql/table.h
    1.70 05/03/30 15:57:38 sergefp@stripped +3 -1
    Added comments about TABLE::used_keys

  sql/sql_select.cc
    1.391 05/03/30 15:57:38 sergefp@stripped +13 -1
    Fix for BUG#9213: GROUP BY returns wrong query results:
    Make test_if_skip_sort_order() rebuild tab->ref if it decides to use an index
    different from the index join optimizer has choosen.  

  mysql-test/t/group_by.test
    1.33 05/03/30 15:57:38 sergefp@stripped +25 -0
    Testcase for BUG#9213

  mysql-test/r/group_by.result
    1.43 05/03/30 15:57:38 sergefp@stripped +25 -0
    Testcase for BUG#9213

# 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:	sergefp
# Host:	newbox.mylan
# Root:	/home/psergey/mysql-4.1-look100

--- 1.390/sql/sql_select.cc	2005-03-30 13:43:51 +04:00
+++ 1.391/sql/sql_select.cc	2005-03-30 15:57:38 +04:00
@@ -7185,7 +7185,19 @@
 	/* Found key that can be used to retrieve data in sorted order */
 	if (tab->ref.key >= 0)
 	{
-	  tab->ref.key= new_ref_key;
+          /*
+            We'll use ref access method on key new_ref_key. In general case 
+            the index search tuple for new_ref_key will be different (e.g.
+            when one of the indexes only covers prefix of the field, see
+            BUG#9213 in group_by.test).
+            So we build tab->ref from scratch here.
+          */
+          KEYUSE *keyuse= tab->keyuse;
+          while (keyuse->key != new_ref_key && keyuse->table ==
tab->table)
+            keyuse++;
+          if (create_ref_for_key(tab->join, tab, keyuse, 
+                                 tab->join->const_table_map))
+            DBUG_RETURN(0);
 	}
 	else
 	{

--- 1.69/sql/table.h	2004-12-11 15:54:07 +03:00
+++ 1.70/sql/table.h	2005-03-30 15:57:38 +04:00
@@ -90,7 +90,9 @@
   uint null_fields;			/* number of null fields */
   uint blob_fields;			/* number of blob fields */
   key_map keys_in_use, keys_for_keyread, read_only_keys;
-  key_map quick_keys, used_keys, keys_in_use_for_query;
+  key_map quick_keys;
+  key_map used_keys;  /* keys that cover all used table fields */
+  key_map keys_in_use_for_query;
   KEY  *key_info;			/* data of keys in database */
   TYPELIB keynames;			/* Pointers to keynames */
   ha_rows max_rows;			/* create information */

--- 1.42/mysql-test/r/group_by.result	2005-02-08 15:41:05 +03:00
+++ 1.43/mysql-test/r/group_by.result	2005-03-30 15:57:38 +04:00
@@ -677,3 +677,28 @@
 d
 10
 drop table t1;
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(8),(9);
+create table t2 (
+a int,
+b varchar(200) NOT NULL,
+c varchar(50) NOT NULL,
+d varchar(100) NOT NULL,
+primary key (a,b(132),c,d),
+key a (a,b)
+) charset=utf8;
+insert into t2 select 
+x3.a,  -- 3
+concat('val-', x3.a + 3*x4.a), -- 12
+concat('val-', @a:=x3.a + 3*x4.a + 12*C.a), -- 120
+concat('val-', @a + 120*D.a)
+from t1 x3, t1 x4, t1 C, t1 D where x3.a < 3 and x4.a < 4 and D.a < 4;
+delete from t2  where a = 2 and b = 'val-2' limit 30;
+explain select c from t2 where a = 2 and b = 'val-2' group by c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ref	PRIMARY,a	PRIMARY	400	const,const	6	Using where
+select c from t2 where a = 2 and b = 'val-2' group by c;
+c
+val-74
+val-98
+drop table t1,t2;

--- 1.32/mysql-test/t/group_by.test	2005-02-15 15:49:50 +03:00
+++ 1.33/mysql-test/t/group_by.test	2005-03-30 15:57:38 +04:00
@@ -489,3 +489,28 @@
 select a as d,sum(b) from t1 where a=1 group by c having d=1;
 select sum(a)*sum(b) as d from t1 where a=1 group by c having d > 0;
 drop table t1;
+
+# Test for BUG#9213 GROUP BY query on utf-8 key returns wrong results
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(8),(9);
+create table t2 (
+  a int,
+  b varchar(200) NOT NULL,
+  c varchar(50) NOT NULL,
+  d varchar(100) NOT NULL,
+  primary key (a,b(132),c,d),
+  key a (a,b)
+) charset=utf8;
+
+insert into t2 select 
+   x3.a,  -- 3
+   concat('val-', x3.a + 3*x4.a), -- 12
+   concat('val-', @a:=x3.a + 3*x4.a + 12*C.a), -- 120
+   concat('val-', @a + 120*D.a)
+from t1 x3, t1 x4, t1 C, t1 D where x3.a < 3 and x4.a < 4 and D.a < 4;
+delete from t2  where a = 2 and b = 'val-2' limit 30;
+
+explain select c from t2 where a = 2 and b = 'val-2' group by c;
+select c from t2 where a = 2 and b = 'val-2' group by c;
+drop table t1,t2;
+