List:Commits« Previous MessageNext Message »
From:Georgi Kodinov Date:February 13 2009 2:46pm
Subject:bzr commit into mysql-5.0-bugteam branch (joro:2744) Bug#41610
View as plain text  
#At file:///home/kgeorge/mysql/work/B41610-5.0-bugteam/ based on revid:joro@stripped

 2744 Georgi Kodinov	2009-02-13
      Bug #41610: key_infix_len can be overwritten causing some group by queries to return no rows
      
      The algorithm of determining the best key for loose index scan is doing a loop over the 
      available indexes and selects the one that has the best cost. 
      It retrieves the parameters of the current index into a set of variables.
      If the cost of using the current index is lower than the best cost so far it 
      copies these variables into another set of variables that contain the information for the
      best index so far.
      After having checked all the indexes it uses these variables (outside of the index 
      loop) to create the table read plan object instance.
      The was a single omission : the key_infix/key_infix_len variables were used outside of the 
      loop without being preserved in the loop for the best index so far.
      This causes these variables to get overwritten by the next index(es) checked.
      Fixed by adding variables to hold the data for the current index, passing the new variables 
      to the function that assigns values to them and copying the new variables into the existing ones
      when selecting a new current best index.
      To avoid further such problems moved the declarations of the variables used to keep information
      about the current index inside the loop's compound statement.
      Also fixed a wrong test output in group_min_max.test
      modified:
        mysql-test/r/group_min_max.result
        mysql-test/t/group_min_max.test
        sql/opt_range.cc

per-file messages:
  mysql-test/r/group_min_max.result
    Bug #41610: test case and fix for wrong output
  mysql-test/t/group_min_max.test
    Bug #41610: test case
  sql/opt_range.cc
    Bug #41610: copy the infix data for the current best index
=== modified file 'mysql-test/r/group_min_max.result'
--- a/mysql-test/r/group_min_max.result	2008-08-27 13:03:17 +0000
+++ b/mysql-test/r/group_min_max.result	2009-02-13 14:45:30 +0000
@@ -1509,7 +1509,7 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	#	Using where; Using index for group-by
 explain select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	#	Using where; Using index
+1	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
 explain select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	146	NULL	#	Using where; Using index for group-by
@@ -1719,7 +1719,7 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	#	Using where; Using index for group-by
 explain select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	#	Using where; Using index
+1	SIMPLE	t2	range	NULL	idx_t2_1	163	NULL	#	Using where; Using index for group-by
 explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	146	NULL	#	Using where; Using index for group-by
@@ -2429,3 +2429,18 @@ id	select_type	table	type	possible_keys	
 Warnings:
 Note	1003	select sql_buffer_result `test`.`t1`.`a` AS `a`,(max(`test`.`t1`.`b`) + 1) AS `max(b)+1` from `test`.`t1` where (`test`.`t1`.`a` = 0) group by `test`.`t1`.`a`
 drop table t1;
+CREATE TABLE t1 (a int, b int, c int, d int,
+KEY foo (c,d,a,b), KEY bar (c,a,b,d));
+INSERT INTO t1 VALUES (1, 1, 1, 1), (1, 1, 1, 2), (1, 1, 1, 3), (1, 1, 1, 4);
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT a,b,c+1,d FROM t1;
+EXPLAIN SELECT DISTINCT c FROM t1 WHERE d=4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	NULL	foo	10	NULL	9	Using where; Using index for group-by
+SELECT DISTINCT c FROM t1 WHERE d=4;
+c
+1
+2
+DROP TABLE t1;
+End of 5.0 tests

=== modified file 'mysql-test/t/group_min_max.test'
--- a/mysql-test/t/group_min_max.test	2008-08-27 13:03:17 +0000
+++ b/mysql-test/t/group_min_max.test	2009-02-13 14:45:30 +0000
@@ -935,3 +935,26 @@ insert into t1 (a,b) select a, max(b)+1 
 select * from t1;
 explain extended select sql_buffer_result a, max(b)+1 from t1 where a = 0 group by a;
 drop table t1;
+
+
+#
+# Bug #41610: key_infix_len can be overwritten causing some group by queries
+# to return no rows
+#
+
+CREATE TABLE t1 (a int, b int, c int, d int,
+  KEY foo (c,d,a,b), KEY bar (c,a,b,d));
+
+INSERT INTO t1 VALUES (1, 1, 1, 1), (1, 1, 1, 2), (1, 1, 1, 3), (1, 1, 1, 4);
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT * FROM t1;
+INSERT INTO t1 SELECT a,b,c+1,d FROM t1;
+
+#Should be non-empty
+--ordered_result
+EXPLAIN SELECT DISTINCT c FROM t1 WHERE d=4;
+SELECT DISTINCT c FROM t1 WHERE d=4;
+
+DROP TABLE t1;
+
+--echo End of 5.0 tests

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2008-12-22 20:28:08 +0000
+++ b/sql/opt_range.cc	2009-02-13 14:45:30 +0000
@@ -7775,32 +7775,35 @@ get_best_group_min_max(PARAM *param, SEL
   */
   KEY *cur_index_info= table->key_info;
   KEY *cur_index_info_end= cur_index_info + table->s->keys;
-  KEY_PART_INFO *cur_part= NULL;
-  KEY_PART_INFO *end_part; /* Last part for loops. */
-  /* Last index part. */
-  KEY_PART_INFO *last_part= NULL;
-  KEY_PART_INFO *first_non_group_part= NULL;
-  KEY_PART_INFO *first_non_infix_part= NULL;
-  uint key_infix_parts= 0;
-  uint cur_group_key_parts= 0;
-  uint cur_group_prefix_len= 0;
   /* Cost-related variables for the best index so far. */
   double best_read_cost= DBL_MAX;
   ha_rows best_records= 0;
   SEL_ARG *best_index_tree= NULL;
   ha_rows best_quick_prefix_records= 0;
   uint best_param_idx= 0;
-  double cur_read_cost= DBL_MAX;
-  ha_rows cur_records;
-  SEL_ARG *cur_index_tree= NULL;
-  ha_rows cur_quick_prefix_records= 0;
-  uint cur_param_idx=MAX_KEY;
-  key_map cur_used_key_parts;
-  uint pk= param->table->s->primary_key;
 
   for (uint cur_index= 0 ; cur_index_info != cur_index_info_end ;
        cur_index_info++, cur_index++)
   {
+    KEY_PART_INFO *cur_part= NULL;
+    KEY_PART_INFO *end_part; /* Last part for loops. */
+    /* Last index part. */
+    KEY_PART_INFO *last_part= NULL;
+    KEY_PART_INFO *first_non_group_part= NULL;
+    KEY_PART_INFO *first_non_infix_part= NULL;
+    uint key_infix_parts= 0;
+    uint cur_group_key_parts= 0;
+    uint cur_group_prefix_len= 0;
+    double cur_read_cost= DBL_MAX;
+    ha_rows cur_records;
+    SEL_ARG *cur_index_tree= NULL;
+    ha_rows cur_quick_prefix_records= 0;
+    uint cur_param_idx=MAX_KEY;
+    key_map cur_used_key_parts;
+    uint pk= param->table->s->primary_key;
+    uint cur_key_infix_len= 0;
+    byte cur_key_infix[MAX_KEY_LENGTH];
+
     /* Check (B1) - if current index is covering. */
     if (!table->used_keys.is_set(cur_index))
       goto next_index;
@@ -7958,7 +7961,8 @@ get_best_group_min_max(PARAM *param, SEL
                                                         &dummy);
         if (!get_constant_key_infix(cur_index_info, index_range_tree,
                                     first_non_group_part, min_max_arg_part,
-                                    last_part, thd, key_infix, &key_infix_len,
+                                    last_part, thd, cur_key_infix, 
+                                    &cur_key_infix_len,
                                     &first_non_infix_part))
           goto next_index;
       }
@@ -8045,6 +8049,9 @@ get_best_group_min_max(PARAM *param, SEL
       best_param_idx= cur_param_idx;
       group_key_parts= cur_group_key_parts;
       group_prefix_len= cur_group_prefix_len;
+      key_infix_len= cur_key_infix_len;
+      if (key_infix_len)
+        memcpy (key_infix, cur_key_infix, sizeof (key_infix));
     }
 
   next_index:

Thread
bzr commit into mysql-5.0-bugteam branch (joro:2744) Bug#41610Georgi Kodinov13 Feb