#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#41610 | Georgi Kodinov | 13 Feb |
