diff options
| -rw-r--r-- | mysql-test/r/func_group.result | 120 | ||||
| -rw-r--r-- | mysql-test/t/func_group.test | 57 | ||||
| -rw-r--r-- | sql/opt_sum.cc | 56 |
3 files changed, 198 insertions, 35 deletions
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 71c74959438..b7bf3a5cd80 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -389,12 +389,12 @@ select max(a3) from t1 where a3 < 'SEA' and a2 = 2 and a3 <= 'MIN'; max(a3) MIN explain -select max(a3) from t1 where a3 = 'SEA' and a2 = 2; +select max(a3) from t1 where a3 = 'MIN' and a2 = 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away -select max(a3) from t1 where a3 = 'SEA' and a2 = 2; +select max(a3) from t1 where a3 = 'MIN' and a2 = 2; max(a3) -SEA +MIN explain select max(a3) from t1 where a3 = 'DEN' and a2 = 2; id select_type table type possible_keys key key_len ref rows Extra @@ -430,6 +430,83 @@ id select_type table type possible_keys key key_len ref rows Extra select min(a3) from t1 where a2 = 2 and a3 < 'SEA'; min(a3) CHI +explain +select min(a3) from t1 where a2 = 4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No matching min/max row +select min(a3) from t1 where a2 = 4; +min(a3) +NULL +explain +select min(a3) from t1 where a2 = 2 and a3 > 'SEA'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No matching min/max row +select min(a3) from t1 where a2 = 2 and a3 > 'SEA'; +min(a3) +NULL +explain +select (min(a4)+max(a4))/2 from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +select (min(a4)+max(a4))/2 from t1; +(min(a4)+max(a4))/2 +0.085 +explain +select min(a3) from t1 where 2 = a2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +select min(a3) from t1 where 2 = a2; +min(a3) +CHI +explain +select max(a3) from t1 where a2 = 2 and 'SEA' > a3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +select max(a3) from t1 where a2 = 2 and 'SEA' > a3; +max(a3) +MIN +explain +select max(a3) from t1 where a2 = 2 and 'SEA' < a3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No matching min/max row +select max(a3) from t1 where a2 = 2 and 'SEA' < a3; +max(a3) +NULL +explain +select min(a3) from t1 where a2 = 2 and a3 >= 'CHI'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +select min(a3) from t1 where a2 = 2 and a3 >= 'CHI'; +min(a3) +CHI +explain +select min(a3) from t1 where a2 = 2 and a3 >= 'CHI' and a3 < 'SEA'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +select min(a3) from t1 where a2 = 2 and a3 >= 'CHI' and a3 < 'SEA'; +min(a3) +CHI +explain +select min(a3) from t1 where a2 = 2 and a3 >= 'CHI' and a3 = 'MIN'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +select min(a3) from t1 where a2 = 2 and a3 >= 'CHI' and a3 = 'MIN'; +min(a3) +MIN +explain +select min(a3) from t1 where a2 = 2 and a3 >= 'SEA' and a3 = 'MIN'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +select min(a3) from t1 where a2 = 2 and a3 >= 'SEA' and a3 = 'MIN'; +min(a3) +NULL +explain +select min(t1.a1), min(t2.a4) from t1,t2 where t1.a1 < 'KKK' and t2.a4 < 'KKK'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +select min(t1.a1), min(t2.a4) from t1,t2 where t1.a1 < 'KKK' and t2.a4 < 'KKK'; +min(t1.a1) min(t2.a4) +AME AME explain select min(a1) from t1 where a1 > 'KKK' or a1 < 'XXX'; id select_type table type possible_keys key key_len ref rows Extra @@ -447,4 +524,41 @@ select max(t1.a3), min(t2.a2) from t1, t2 where t1.a2 = 2 and t1.a3 < 'MIN' and id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range k1 k1 7 NULL 1 Using where; Using index 1 SIMPLE t2 range k1 k1 3 NULL 4 Using where; Using index +explain +select min(a4 - 0.01) from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL k2 12 NULL 14 Using index +explain +select max(a4 + 0.01) from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL k2 12 NULL 14 Using index +explain +select min(a3) from t1 where (a2 +1 ) is null; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL k1 7 NULL 14 Using where; Using index +explain +select min(a3) from t1 where (a2 + 1) = 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL k1 7 NULL 14 Using where; Using index +explain +select min(a3) from t1 where 2 = (a2 + 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL k1 7 NULL 14 Using where; Using index +explain +select min(a2) from t1 where a2 < 2 * a2 - 8; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL k1 7 NULL 14 Using where; Using index +explain +select min(a1) from t1 where a1 between a3 and 'KKK'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 14 Using where +explain +select min(a4) from t1 where (a4 + 0.01) between 0.07 and 0.08; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL k2 12 NULL 14 Using where; Using index +explain +select concat(min(t1.a1),min(t2.a4)) from t1, t2 where t2.a4 <> 'AME'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL PRIMARY 3 NULL 14 Using index +1 SIMPLE t2 index NULL k2 4 NULL 6 Using where; Using index drop table if exists t1, t2; diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 7c51877a90a..c78509d3869 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -241,8 +241,8 @@ explain select max(a3) from t1 where a3 < 'SEA' and a2 = 2 and a3 <= 'MIN'; select max(a3) from t1 where a3 < 'SEA' and a2 = 2 and a3 <= 'MIN'; explain -select max(a3) from t1 where a3 = 'SEA' and a2 = 2; -select max(a3) from t1 where a3 = 'SEA' and a2 = 2; +select max(a3) from t1 where a3 = 'MIN' and a2 = 2; +select max(a3) from t1 where a3 = 'MIN' and a2 = 2; explain select max(a3) from t1 where a3 = 'DEN' and a2 = 2; select max(a3) from t1 where a3 = 'DEN' and a2 = 2; @@ -262,6 +262,41 @@ explain select min(a3) from t1 where a2 = 2 and a3 < 'SEA'; select min(a3) from t1 where a2 = 2 and a3 < 'SEA'; +explain +select min(a3) from t1 where a2 = 4; +select min(a3) from t1 where a2 = 4; +explain +select min(a3) from t1 where a2 = 2 and a3 > 'SEA'; +select min(a3) from t1 where a2 = 2 and a3 > 'SEA'; +explain +select (min(a4)+max(a4))/2 from t1; +select (min(a4)+max(a4))/2 from t1; +explain +select min(a3) from t1 where 2 = a2; +select min(a3) from t1 where 2 = a2; +explain +select max(a3) from t1 where a2 = 2 and 'SEA' > a3; +select max(a3) from t1 where a2 = 2 and 'SEA' > a3; +explain +select max(a3) from t1 where a2 = 2 and 'SEA' < a3; +select max(a3) from t1 where a2 = 2 and 'SEA' < a3; +explain +select min(a3) from t1 where a2 = 2 and a3 >= 'CHI'; +select min(a3) from t1 where a2 = 2 and a3 >= 'CHI'; +explain +select min(a3) from t1 where a2 = 2 and a3 >= 'CHI' and a3 < 'SEA'; +select min(a3) from t1 where a2 = 2 and a3 >= 'CHI' and a3 < 'SEA'; +explain +select min(a3) from t1 where a2 = 2 and a3 >= 'CHI' and a3 = 'MIN'; +select min(a3) from t1 where a2 = 2 and a3 >= 'CHI' and a3 = 'MIN'; +explain +select min(a3) from t1 where a2 = 2 and a3 >= 'SEA' and a3 = 'MIN'; +select min(a3) from t1 where a2 = 2 and a3 >= 'SEA' and a3 = 'MIN'; + +explain +select min(t1.a1), min(t2.a4) from t1,t2 where t1.a1 < 'KKK' and t2.a4 < 'KKK'; +select min(t1.a1), min(t2.a4) from t1,t2 where t1.a1 < 'KKK' and t2.a4 < 'KKK'; + # Queries to which max/min optimization is not applied explain @@ -273,6 +308,24 @@ select max(a3) from t1 where a2 < 2 and a3 < 'SEA'; explain select max(t1.a3), min(t2.a2) from t1, t2 where t1.a2 = 2 and t1.a3 < 'MIN' and t2.a3 > 'CA'; +explain +select min(a4 - 0.01) from t1; +explain +select max(a4 + 0.01) from t1; +explain +select min(a3) from t1 where (a2 +1 ) is null; +explain +select min(a3) from t1 where (a2 + 1) = 2; +explain +select min(a3) from t1 where 2 = (a2 + 1); +explain +select min(a2) from t1 where a2 < 2 * a2 - 8; +explain +select min(a1) from t1 where a1 between a3 and 'KKK'; +explain +select min(a4) from t1 where (a4 + 0.01) between 0.07 and 0.08; +explain +select concat(min(t1.a1),min(t2.a4)) from t1, t2 where t2.a4 <> 'AME'; # Clean up drop table if exists t1, t2;
\ No newline at end of file diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc index 110509b78ef..1e116518da0 100644 --- a/sql/opt_sum.cc +++ b/sql/opt_sum.cc @@ -168,7 +168,9 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds) Look for a partial key that can be used for optimization. If we succeed, ref.key_length will contain the length of this key, while prefix_len will contain the length of - the beginning of this key without field used in MIN() + the beginning of this key without field used in MIN(). + Type of range for the key part for this field will be + returned in range_fl. */ if ((outer_tables & table->map) || !find_key_for_maxmin(0, &ref, item_field->field, conds, @@ -178,24 +180,15 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds) break; } bool error= table->file->index_init((uint) ref.key); - enum ha_rkey_function find_flag= range_fl & NEAR_MIN ? - HA_READ_AFTER_KEY : HA_READ_KEY_OR_NEXT; - /* - If we are doing MIN() on a column with NULL fields - we must read the key after the NULL column - */ - if (item_field->field->null_bit) - { - ref.key_buff[ref.key_length++]= 1; - find_flag= HA_READ_AFTER_KEY; - } if (!ref.key_length) error= table->file->index_first(table->record[0]) != 0; else - error= table->file->index_read(table->record[0], key_buff, - ref.key_length, - find_flag) || + error= table->file->index_read(table->record[0],key_buff, + ref.key_length, + range_fl & NEAR_MIN ? + HA_READ_AFTER_KEY : + HA_READ_KEY_OR_NEXT) || reckey_in_range(0, &ref, item_field->field, conds, range_fl, prefix_len); if (table->key_read) @@ -240,7 +233,9 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds) Look for a partial key that can be used for optimization. If we succeed, ref.key_length will contain the length of this key, while prefix_len will contain the length of - the beginning of this key without field used in MAX() + the beginning of this key without field used in MAX(). + Type of range for the key part for this field will be + returned in range_fl. */ if ((outer_tables & table->map) || !find_key_for_maxmin(1, &ref, item_field->field, conds, @@ -520,12 +515,13 @@ static bool matching_cond(bool max_fl, TABLE_REF *ref, KEY *keyinfo, if (org_key_part_used != *key_part_used || (is_field_part && - (between || max_fl == less_fl) && !cond->val_int())) + (between || eq_type || max_fl == less_fl) && !cond->val_int())) { /* It's the first predicate for this part or a predicate of the following form that moves upper/lower bounds for max/min values: - field BETWEEN const AND const + - field = const - field {<|<=} const, when searching for MAX - field {>|>=} const, when searching for MIN */ @@ -545,7 +541,7 @@ static bool matching_cond(bool max_fl, TABLE_REF *ref, KEY *keyinfo, } if (is_field_part) { - if (between) + if (between || eq_type) *range_fl&= ~(NO_MAX_RANGE | NO_MIN_RANGE); else { @@ -578,7 +574,7 @@ static bool matching_cond(bool max_fl, TABLE_REF *ref, KEY *keyinfo, ref in/out Reference to the structure we store the key value field in: Field used inside MIN() / MAX() cond in: WHERE condition - range_fl in/out Bit flags for how to search if key is ok + range_fl out: Bit flags for how to search if key is ok prefix_len out: Length of prefix for the search range DESCRIPTION @@ -739,17 +735,17 @@ static int maxmin_in_range(bool max_fl, Field* field, COND *cond) case Item_func::GT_FUNC: case Item_func::GE_FUNC: { - Item *item= ((Item_func*) cond)->arguments()[1]; - /* In case of 'const op item' we have to swap the operator */ - if (!item->const_item()) - less_fl= 1-less_fl; - /* - We only have to check the expression if we are using an expression like - SELECT MAX(b) FROM t1 WHERE a=const AND b>const - not for - SELECT MAX(b) FROM t1 WHERE a=const AND b<const - */ - if (max_fl != less_fl) + Item *item= ((Item_func*) cond)->arguments()[1]; + /* In case of 'const op item' we have to swap the operator */ + if (!item->const_item()) + less_fl= 1-less_fl; + /* + We only have to check the expression if we are using an expression like + SELECT MAX(b) FROM t1 WHERE a=const AND b>const + not for + SELECT MAX(b) FROM t1 WHERE a=const AND b<const + */ + if (max_fl != less_fl) return cond->val_int() == 0; // Return 1 if WHERE is false return 0; } |