diff options
| -rw-r--r-- | mysql-test/r/func_group.result | 24 | ||||
| -rw-r--r-- | mysql-test/t/func_group.test | 19 | ||||
| -rw-r--r-- | sql/opt_sum.cc | 17 | ||||
| -rw-r--r-- | sql/sql_select.cc | 6 | ||||
| -rw-r--r-- | sql/sql_select.h | 2 |
5 files changed, 61 insertions, 7 deletions
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 67966b999d4..ffa68f279f3 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -916,3 +916,27 @@ select count(*), min(7), max(7) from t2m, t1i; count(*) min(7) max(7) 0 NULL NULL drop table t1m, t1i, t2m, t2i; +CREATE TABLE t1 (id int PRIMARY KEY, b char(3), INDEX(b)); +INSERT INTO t1 VALUES (1,'xx'), (2,'aa'); +SELECT * FROM t1; +id b +1 xx +2 aa +SELECT MAX(b) FROM t1 WHERE b < 'ppppp'; +MAX(b) +aa +SHOW WARNINGS; +Level Code Message +SELECT MAX(b) FROM t1 WHERE b < 'pp'; +MAX(b) +aa +DROP TABLE t1; +CREATE TABLE t1 (id int PRIMARY KEY, b char(16), INDEX(b(4))); +INSERT INTO t1 VALUES (1, 'xxxxbbbb'), (2, 'xxxxaaaa'); +SELECT MAX(b) FROM t1; +MAX(b) +xxxxbbbb +EXPLAIN SELECT MAX(b) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +DROP TABLE t1; diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index dc647dbcfeb..f8a3ed0f25e 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -598,4 +598,23 @@ select count(*), min(7), max(7) from t2m, t1i; drop table t1m, t1i, t2m, t2i; +# +# Bug #18206: min/max optimization cannot be applied to partial index +# + +CREATE TABLE t1 (id int PRIMARY KEY, b char(3), INDEX(b)); +INSERT INTO t1 VALUES (1,'xx'), (2,'aa'); +SELECT * FROM t1; + +SELECT MAX(b) FROM t1 WHERE b < 'ppppp'; +SHOW WARNINGS; +SELECT MAX(b) FROM t1 WHERE b < 'pp'; +DROP TABLE t1; + +CREATE TABLE t1 (id int PRIMARY KEY, b char(16), INDEX(b(4))); +INSERT INTO t1 VALUES (1, 'xxxxbbbb'), (2, 'xxxxaaaa'); +SELECT MAX(b) FROM t1; +EXPLAIN SELECT MAX(b) FROM t1; +DROP TABLE t1; + # End of 4.1 tests diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc index cfb5b3695a3..82211120c57 100644 --- a/sql/opt_sum.cc +++ b/sql/opt_sum.cc @@ -543,6 +543,10 @@ static bool matching_cond(bool max_fl, TABLE_REF *ref, KEY *keyinfo, break; // Found a part od the key for the field } +#if 0 + if (part->length != (((Item_field*) args[0])->field)->field_length) + return 0; +#endif bool is_field_part= part == field_part; if (!(is_field_part || eq_type)) return 0; @@ -582,7 +586,8 @@ static bool matching_cond(bool max_fl, TABLE_REF *ref, KEY *keyinfo, } else { - store_val_in_field(part->field, args[between && max_fl ? 2 : 1]); + store_val_in_field(part->field, args[between && max_fl ? 2 : 1], + CHECK_FIELD_IGNORE); if (part->null_bit) *key_ptr++= (byte) test(part->field->is_null()); part->field->get_key_image((char*) key_ptr, part->length, @@ -638,6 +643,8 @@ static bool matching_cond(bool max_fl, TABLE_REF *ref, KEY *keyinfo, field BETWEEN const1 AND const2 3. all references to the columns from the same table as column field occur only in conjucts mentioned above. + 4. each of k first components the index is not partial, i.e. is not + defined on a fixed length proper prefix of the field. If such an index exists the function through the ref parameter returns the key value to find max/min for the field using the index, @@ -647,8 +654,8 @@ static bool matching_cond(bool max_fl, TABLE_REF *ref, KEY *keyinfo, of the whole search key) NOTE - This function may set table->key_read to 1, which must be reset after - index is used! (This can only happen when function returns 1) + This function may set table->key_read to 1, which must be reset after + index is used! (This can only happen when function returns 1) RETURN 0 Index can not be used to optimize MIN(field)/MAX(field) @@ -682,6 +689,10 @@ static bool find_key_for_maxmin(bool max_fl, TABLE_REF *ref, if (!(table->file->index_flags(idx, jdx, 0) & HA_READ_ORDER)) return 0; + /* Check whether the index component is partial */ + if (part->length < table->field[part->fieldnr-1]->pack_length()) + break; + if (field->eq(part->field)) { ref->key= idx; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 57fb9738612..5a7e9e52aed 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -3434,7 +3434,7 @@ get_store_key(THD *thd, KEYUSE *keyuse, table_map used_tables, */ bool -store_val_in_field(Field *field,Item *item) +store_val_in_field(Field *field, Item *item, enum_check_fields check_flag) { bool error; THD *thd=current_thd; @@ -3445,7 +3445,7 @@ store_val_in_field(Field *field,Item *item) with select_insert, which make count_cuted_fields= 1 */ enum_check_fields old_count_cuted_fields= thd->count_cuted_fields; - thd->count_cuted_fields= CHECK_FIELD_WARN; + thd->count_cuted_fields= check_flag; error= item->save_in_field(field, 1); thd->count_cuted_fields= old_count_cuted_fields; return error || cuted_fields != thd->cuted_fields; @@ -7097,7 +7097,7 @@ static bool test_if_ref(Item_field *left_item,Item *right_item) field->real_type() != FIELD_TYPE_VAR_STRING && (field->type() != FIELD_TYPE_FLOAT || field->decimals() == 0)) { - return !store_val_in_field(field,right_item); + return !store_val_in_field(field, right_item, CHECK_FIELD_WARN); } } } diff --git a/sql/sql_select.h b/sql/sql_select.h index 94cc8839466..75cd0b4d797 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -335,7 +335,7 @@ extern const char *join_type_str[]; void TEST_join(JOIN *join); /* Extern functions in sql_select.cc */ -bool store_val_in_field(Field *field,Item *val); +bool store_val_in_field(Field *field, Item *val, enum_check_fields check_flag); TABLE *create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields, ORDER *group, bool distinct, bool save_sum_fields, ulong select_options, ha_rows rows_limit, |