2764 Georgi Kodinov 2009-02-27
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.
@ mysql-test/r/group_min_max.result
Bug #41610: test case
@ 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:
mysql-test/r/group_min_max.result
mysql-test/t/group_min_max.test
sql/opt_range.cc
2763 Patrick Crews 2009-02-27
Bug#41893: Removal of trailing space noise causing Pushbuild failure.
modified:
mysql-test/r/variables.result
=== 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-27 13:25:06 +0000
@@ -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-27 13:25:06 +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-27 13:25:06 +0000
@@ -7775,32 +7775,37 @@ 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;
+
+ const uint pk= param->table->s->primary_key;
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;
+ KEY_PART_INFO *end_part; /* Last part for loops. */
+ /* Last index part. */
+ KEY_PART_INFO *last_part;
+ KEY_PART_INFO *first_non_group_part;
+ KEY_PART_INFO *first_non_infix_part;
+ uint key_infix_parts;
+ uint cur_group_key_parts= 0;
+ uint cur_group_prefix_len= 0;
+ double cur_read_cost;
+ ha_rows cur_records;
+ key_map used_key_parts_map;
+ uint cur_key_infix_len= 0;
+ byte cur_key_infix[MAX_KEY_LENGTH];
+ uint cur_used_key_parts;
+
/* Check (B1) - if current index is covering. */
if (!table->used_keys.is_set(cur_index))
goto next_index;
@@ -7879,7 +7884,7 @@ get_best_group_min_max(PARAM *param, SEL
else if (join->select_distinct)
{
select_items_it.rewind();
- cur_used_key_parts.clear_all();
+ used_key_parts_map.clear_all();
uint max_key_part= 0;
while ((item= select_items_it++))
{
@@ -7890,13 +7895,13 @@ get_best_group_min_max(PARAM *param, SEL
Check if this attribute was already present in the select list.
If it was present, then its corresponding key part was alredy used.
*/
- if (cur_used_key_parts.is_set(key_part_nr))
+ if (used_key_parts_map.is_set(key_part_nr))
continue;
if (key_part_nr < 1 || key_part_nr > join->fields_list.elements)
goto next_index;
cur_part= cur_index_info->key_part + key_part_nr - 1;
cur_group_prefix_len+= cur_part->store_length;
- cur_used_key_parts.set_bit(key_part_nr);
+ used_key_parts_map.set_bit(key_part_nr);
++cur_group_key_parts;
max_key_part= max(max_key_part,key_part_nr);
}
@@ -7908,7 +7913,7 @@ get_best_group_min_max(PARAM *param, SEL
*/
ulonglong all_parts, cur_parts;
all_parts= (1<<max_key_part) - 1;
- cur_parts= cur_used_key_parts.to_ulonglong() >> 1;
+ cur_parts= used_key_parts_map.to_ulonglong() >> 1;
if (all_parts != cur_parts)
goto next_index;
}
@@ -7958,7 +7963,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;
}
@@ -8010,9 +8016,9 @@ get_best_group_min_max(PARAM *param, SEL
}
/* If we got to this point, cur_index_info passes the test. */
- key_infix_parts= key_infix_len ?
+ key_infix_parts= cur_key_infix_len ?
(first_non_infix_part - first_non_group_part) : 0;
- used_key_parts= cur_group_key_parts + key_infix_parts;
+ cur_used_key_parts= cur_group_key_parts + key_infix_parts;
/* Compute the cost of using this index. */
if (tree)
@@ -8024,7 +8030,7 @@ get_best_group_min_max(PARAM *param, SEL
cur_quick_prefix_records= check_quick_select(param, cur_param_idx,
cur_index_tree);
}
- cost_group_min_max(table, cur_index_info, used_key_parts,
+ cost_group_min_max(table, cur_index_info, cur_used_key_parts,
cur_group_key_parts, tree, cur_index_tree,
cur_quick_prefix_records, have_min, have_max,
&cur_read_cost, &cur_records);
@@ -8035,7 +8041,6 @@ get_best_group_min_max(PARAM *param, SEL
*/
if (cur_read_cost < best_read_cost - (DBL_EPSILON * cur_read_cost))
{
- DBUG_ASSERT(tree != 0 || cur_param_idx == MAX_KEY);
index_info= cur_index_info;
index= cur_index;
best_read_cost= cur_read_cost;
@@ -8045,11 +8050,13 @@ 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));
+ used_key_parts= cur_used_key_parts;
}
- next_index:
- cur_group_key_parts= 0;
- cur_group_prefix_len= 0;
+ next_index:;
}
if (!index_info) /* No usable index found. */
DBUG_RETURN(NULL);
Attachment: [text/bzr-bundle] bzr/kgeorge@mysql.com-20090227132506-o95gw5q37ydbm7fx.bundle
| Thread |
|---|
| • bzr push into mysql-5.0-bugteam branch (kgeorge:2763 to 2764) Bug#41610 | Georgi Kodinov | 27 Feb |
