diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index a7fd7b8..b42b7ec 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -670,51 +670,138 @@ d p422 explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 5 Using where; Using index for group-by +explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 5 Using where; Using index for group-by +explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 5 Using where; Using index for group-by +explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b' or b = 'a') group by a1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 5 Using where; Using index for group-by explain select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 5 Using where; Using index for group-by +explain select a1,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 5 Using where; Using index for group-by +explain select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 5 Using where; Using index for group-by explain select a1,a2,b, max(c) from t1 where (b = 'b') group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 9 Using where; Using index for group-by +explain select a1,a2,b, max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 9 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 9 Using where; Using index for group-by +explain select a1,a2,b,min(c),max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 9 Using where; Using index for group-by explain select a1,a2, max(c) from t1 where (b = 'b') group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 9 Using where; Using index for group-by +explain select a1,a2, max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 9 Using where; Using index for group-by explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; 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 163 NULL 5 Using where; Using index for group-by +explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +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 163 NULL 5 Using where; Using index for group-by +explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; +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 163 NULL 5 Using where; Using index for group-by explain select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; 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 163 NULL 5 Using where; Using index for group-by +explain select a1,max(c),min(c) from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +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 163 NULL 5 Using where; Using index for group-by +explain select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; +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 163 NULL 5 Using where; Using index for group-by explain select a1,a2,b, max(c) from t2 where (b = 'b') group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 146 NULL 10 Using where; Using index for group-by +explain select a1,a2,b, max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 146 NULL 10 Using where; Using index for group-by explain select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 10 Using where; Using index for group-by +explain select a1,a2,b,min(c),max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 10 Using where; Using index for group-by explain select a1,a2, max(c) from t2 where (b = 'b') group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 146 NULL 10 Using where; Using index for group-by +explain select a1,a2, max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 146 NULL 10 Using where; Using index for group-by explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 range idx_t3_0,idx_t3_1,idx_t3_2 idx_t3_1 6 NULL 4 Using where; Using index for group-by +explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 range idx_t3_0,idx_t3_1,idx_t3_2 idx_t3_1 6 NULL 4 Using where; Using index for group-by +explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 range idx_t3_0,idx_t3_1,idx_t3_2 idx_t3_1 6 NULL 4 Using where; Using index for group-by explain select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 range idx_t3_0,idx_t3_1,idx_t3_2 idx_t3_1 6 NULL 4 Using where; Using index for group-by +explain select a1,max(c),min(c) from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 range idx_t3_0,idx_t3_1,idx_t3_2 idx_t3_1 6 NULL 4 Using where; Using index for group-by +explain select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 range idx_t3_0,idx_t3_1,idx_t3_2 idx_t3_1 6 NULL 4 Using where; Using index for group-by select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; a1 a2 b max(c) min(c) a a b h112 e112 b a b h212 e212 c a b h312 e312 d a b h412 e412 +select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +a1 a2 b max(c) min(c) +a b b p122 e112 +b b b p222 e212 +c b b p322 e312 +d b b p422 e412 +select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; +a1 a2 b max(c) min(c) +a a b h112 a111 +b a b h212 a211 +c a b h312 a311 +d a b h412 a411 +select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b' or b = 'a') group by a1; +a1 a2 b max(c) min(c) +a b b p122 a111 +b b b p222 a211 +c b b p322 a311 +d b b p422 a411 select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; a1 max(c) min(c) a h112 e112 b h212 e212 c h312 e312 d h412 e412 +select a1,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +a1 max(c) min(c) +a p122 e112 +b p222 e212 +c p322 e312 +d p422 e412 +select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; +a1 max(c) min(c) +a h112 a111 +b h212 a211 +c h312 a311 +d h412 a411 select a1,a2,b, max(c) from t1 where (b = 'b') group by a1,a2; a1 a2 b max(c) a a b h112 @@ -725,6 +812,16 @@ c a b h312 c b b p322 d a b h412 d b b p422 +select a1,a2,b, max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; +a1 a2 b max(c) +a a b h112 +a b b p122 +b a b h212 +b b b p222 +c a b h312 +c b b p322 +d a b h412 +d b b p422 select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2; a1 a2 b min(c) max(c) a a b e112 h112 @@ -735,6 +832,16 @@ c a b e312 h312 c b b m322 p322 d a b e412 h412 d b b m422 p422 +select a1,a2,b,min(c),max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; +a1 a2 b min(c) max(c) +a a b a111 h112 +a b b i121 p122 +b a b a211 h212 +b b b i221 p222 +c a b a311 h312 +c b b i321 p322 +d a b a411 h412 +d b b i421 p422 select a1,a2, max(c) from t1 where (b = 'b') group by a1,a2; a1 a2 max(c) a a h112 @@ -745,6 +852,16 @@ c a h312 c b p322 d a h412 d b p422 +select a1,a2, max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; +a1 a2 max(c) +a a h112 +a b p122 +b a h212 +b b p222 +c a h312 +c b p322 +d a h412 +d b p422 select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; a1 a2 b max(c) min(c) a a b h112 e112 @@ -752,6 +869,19 @@ b a b h212 e212 c a b h312 e312 d a b h412 e412 e a b NULL NULL +select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +a1 a2 b max(c) min(c) +a b b p122 e112 +b b b p222 e212 +c b b p322 e312 +d b b p422 e412 +select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; +a1 a2 b max(c) min(c) +a a b h112 a111 +b a b h212 a211 +c a b h312 a311 +d a b h412 a411 +e a b NULL NULL select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; a1 max(c) min(c) a h112 e112 @@ -759,6 +889,19 @@ b h212 e212 c h312 e312 d h412 e412 e NULL NULL +select a1,max(c),min(c) from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +a1 max(c) min(c) +a p122 e112 +b p222 e212 +c p322 e312 +d p422 e412 +select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; +a1 max(c) min(c) +a h112 a111 +b h212 a211 +c h312 a311 +d h412 a411 +e NULL NULL select a1,a2,b, max(c) from t2 where (b = 'b') group by a1,a2; a1 a2 b max(c) a a b h112 @@ -770,6 +913,17 @@ c b b p322 d a b h412 d b b p422 e a b NULL +select a1,a2,b, max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; +a1 a2 b max(c) +a a b h112 +a b b p122 +b a b h212 +b b b p222 +c a b h312 +c b b p322 +d a b h412 +d b b p422 +e a b NULL select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2; a1 a2 b min(c) max(c) a a b e112 h112 @@ -781,6 +935,17 @@ c b b m322 p322 d a b e412 h412 d b b m422 p422 e a b NULL NULL +select a1,a2,b,min(c),max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; +a1 a2 b min(c) max(c) +a a b a111 h112 +a b b i121 p122 +b a b a211 h212 +b b b i221 p222 +c a b a311 h312 +c b b i321 p322 +d a b a411 h412 +d b b i421 p422 +e a b NULL NULL select a1,a2, max(c) from t2 where (b = 'b') group by a1,a2; a1 a2 max(c) a a h112 @@ -792,22 +957,59 @@ c b p322 d a h412 d b p422 e a NULL +select a1,a2, max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; +a1 a2 max(c) +a a h112 +a b p122 +b a h212 +b b p222 +c a h312 +c b p322 +d a h412 +d b p422 +e a NULL select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; a1 a2 b max(c) min(c) a a b h112 e112 b a b h212 e212 c a b h312 e312 +select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +a1 a2 b max(c) min(c) +a b b p122 e112 +b b b p222 e212 +c b b p322 e312 +select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; +a1 a2 b max(c) min(c) +a a b h112 a111 +b a b h212 a211 +c a b h312 a311 select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; a1 max(c) min(c) a h112 e112 b h212 e212 c h312 e312 +select a1,max(c),min(c) from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +a1 max(c) min(c) +a p122 e112 +b p222 e212 +c p322 e312 +select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; +a1 max(c) min(c) +a h112 a111 +b h212 a211 +c h312 a311 explain select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1; 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 163 NULL 5 Using where; Using index for group-by +explain select a1,a2,b,min(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1; +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 163 NULL 5 Using where; Using index for group-by explain select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1; 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 5 Using where; Using index for group-by +explain select a1,a2,b,max(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1; +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 5 Using where; Using index for group-by explain select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 10 Using where; Using index for group-by @@ -817,17 +1019,18 @@ id select_type table type possible_keys key key_len ref rows Extra explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 10 Using where; Using index for group-by -explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 10 Using where; Using index for group-by select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1; a1 a2 b min(c) a a NULL a777 c a NULL c777 +select a1,a2,b,min(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1; +a1 a2 b min(c) select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1; a1 a2 b max(c) a a NULL a999 c a NULL c999 +select a1,a2,b,max(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1; +a1 a2 b max(c) select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2; a1 a2 b min(c) a a NULL a777 @@ -1498,6 +1701,9 @@ id select_type table type possible_keys key key_len ref rows Extra explain select a1,a2,b,c from t1 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 t1 range idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 17 Using where; Using index for group-by +explain select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx_t1_1,idx_t1_2 idx_t1_1 163 NULL 17 Using where; Using index for group-by explain select a1,a2,b from t1 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 t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 14 Using where; Using index for group-by @@ -1510,6 +1716,9 @@ id select_type table type possible_keys key key_len ref rows Extra 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 idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index +explain select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index idx_t2_1,idx_t2_2 idx_t2_1 163 NULL # Using where; Using index 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 @@ -1530,6 +1739,9 @@ d b a select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; a1 a2 b c a b a i121 +select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b; +a1 a2 b c +a b a i121 select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; a1 a2 b select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; @@ -1550,6 +1762,9 @@ d b a select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; a1 a2 b c a b a i121 +select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b; +a1 a2 b c +a b a i121 select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; a1 a2 b explain select distinct a1,a2,b from t1; @@ -1940,6 +2155,15 @@ Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2 explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b < 'b') group by a1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 164 Using where; Using index +explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b < 'b') group by a1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 164 Using where; Using index +explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b <= 'b') group by a1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 164 Using where; Using index +explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b <= 'b' and b >= 'a') group by a1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 164 Using where; Using index explain extended select a1,a2,b from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 76 85.53 Using where; Using index @@ -1974,6 +2198,14 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 128 75.00 Using where; Using index Warnings: Note 1003 /* select#1 */ select sum(ord(`test`.`t1`.`a1`)) AS `sum(ord(a1))` from `test`.`t1` where (`test`.`t1`.`a1` > 'a') group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` +set optimizer_switch = 'multi_range_groupby=off'; +explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'a' or b = 'b') group by a1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index idx_t2_0,idx_t2_1,idx_t2_2 idx_t2_1 163 NULL 164 Using where; Using index +set optimizer_switch = 'default'; +explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'a' or b = 'b') group by a1; +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 163 NULL 5 Using where; Using index for group-by explain select distinct(a1) from t1 where ord(a2) = 98; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 128 Using where; Using index diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result index 514d3b3..8553bc3 100644 --- a/mysql-test/r/index_merge_myisam.result +++ b/mysql-test/r/index_merge_myisam.result @@ -1550,19 +1550,19 @@ DROP TABLE t1,t2; # select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on,multi_range_groupby=on set optimizer_switch='index_merge=off,index_merge_union=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on +index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on,multi_range_groupby=on set optimizer_switch='index_merge_union=on'; select @@optimizer_switch; @@optimizer_switch -index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on +index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on,multi_range_groupby=on set optimizer_switch='default,index_merge_sort_union=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on +index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on,multi_range_groupby=on set optimizer_switch=4; set optimizer_switch=NULL; ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'NULL' @@ -1588,21 +1588,21 @@ set optimizer_switch=default; set optimizer_switch='index_merge=off,index_merge_union=off,default'; select @@optimizer_switch; @@optimizer_switch -index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on +index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on,multi_range_groupby=on set optimizer_switch=default; select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on,multi_range_groupby=on set @@global.optimizer_switch=default; select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on,multi_range_groupby=on # # Check index_merge's @@optimizer_switch flags # select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on,multi_range_groupby=on create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 (a int, b int, c int, filler char(100), @@ -1712,5 +1712,5 @@ id select_type table type possible_keys key key_len ref rows Extra set optimizer_switch=default; show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on,multi_range_groupby=on drop table t0, t1; diff --git a/mysql-test/r/mysqld--help-notwin-profiling.result b/mysql-test/r/mysqld--help-notwin-profiling.result index 818b071..1e0e1ae 100644 --- a/mysql-test/r/mysqld--help-notwin-profiling.result +++ b/mysql-test/r/mysqld--help-notwin-profiling.result @@ -750,8 +750,8 @@ The following options may be given as the first argument: mrr_cost_based, materialization, semijoin, loosescan, firstmatch, subquery_materialization_cost_based, block_nested_loop, batched_key_access, - use_index_extensions, skip_scan, skip_scan_cost_based} - and val is one of {on, off, default} + use_index_extensions, skip_scan, skip_scan_cost_based, + multi_range_groupby} and val is one of {on, off, default} --optimizer-trace=name Controls tracing of the Optimizer: optimizer_trace=option=val[,option=val...], where option @@ -2044,7 +2044,7 @@ optimizer-full-scan TRUE optimizer-low-limit-heuristic TRUE optimizer-prune-level 1 optimizer-search-depth 62 -optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on +optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on,multi_range_groupby=on optimizer-trace optimizer-trace-features greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on optimizer-trace-limit 1 diff --git a/mysql-test/r/mysqld--help-notwin.result b/mysql-test/r/mysqld--help-notwin.result index 0624bc9..1d57e1d 100644 --- a/mysql-test/r/mysqld--help-notwin.result +++ b/mysql-test/r/mysqld--help-notwin.result @@ -750,8 +750,8 @@ The following options may be given as the first argument: mrr_cost_based, materialization, semijoin, loosescan, firstmatch, subquery_materialization_cost_based, block_nested_loop, batched_key_access, - use_index_extensions, skip_scan, skip_scan_cost_based} - and val is one of {on, off, default} + use_index_extensions, skip_scan, skip_scan_cost_based, + multi_range_groupby} and val is one of {on, off, default} --optimizer-trace=name Controls tracing of the Optimizer: optimizer_trace=option=val[,option=val...], where option @@ -2042,7 +2042,7 @@ optimizer-full-scan TRUE optimizer-low-limit-heuristic TRUE optimizer-prune-level 1 optimizer-search-depth 62 -optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on +optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on,multi_range_groupby=on optimizer-trace optimizer-trace-features greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on optimizer-trace-limit 1 diff --git a/mysql-test/r/optimizer_switch.result b/mysql-test/r/optimizer_switch.result index 0fa7c43..0bde14a 100644 --- a/mysql-test/r/optimizer_switch.result +++ b/mysql-test/r/optimizer_switch.result @@ -3,47 +3,47 @@ BUG#37120 optimizer_switch allowable values not according to specification select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on,multi_range_groupby=on set optimizer_switch='default'; set optimizer_switch='materialization=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=off,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=off,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on,multi_range_groupby=on set optimizer_switch='default'; set optimizer_switch='semijoin=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=off,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=off,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on,multi_range_groupby=on set optimizer_switch='default'; set optimizer_switch='loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=off,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=off,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on,multi_range_groupby=on set optimizer_switch='default'; set optimizer_switch='semijoin=off,materialization=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=off,semijoin=off,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=off,semijoin=off,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on,multi_range_groupby=on set optimizer_switch='default'; set optimizer_switch='materialization=off,semijoin=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=off,semijoin=off,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=off,semijoin=off,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on,multi_range_groupby=on set optimizer_switch='default'; set optimizer_switch='semijoin=off,materialization=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on,multi_range_groupby=on set optimizer_switch='default'; set optimizer_switch='semijoin=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=off,loosescan=off,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=off,loosescan=off,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on,multi_range_groupby=on set optimizer_switch='default'; set optimizer_switch='materialization=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=off,semijoin=on,loosescan=off,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=off,semijoin=on,loosescan=off,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on,multi_range_groupby=on set optimizer_switch='default'; create table t1 (a1 char(8), a2 char(8)); create table t2 (b1 char(8), b2 char(8)); diff --git a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result index 8a9f57f..76c006f 100644 --- a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result +++ b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result @@ -1,57 +1,57 @@ SET @start_global_value = @@global.optimizer_switch; SELECT @start_global_value; @start_global_value -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on,multi_range_groupby=on select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on,multi_range_groupby=on select @@session.optimizer_switch; @@session.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on,multi_range_groupby=on show global variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on,multi_range_groupby=on show session variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on,multi_range_groupby=on select * from information_schema.global_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE -OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on +OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on,multi_range_groupby=on select * from information_schema.session_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE -OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on +OPTIMIZER_SWITCH index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on,multi_range_groupby=on set global optimizer_switch=10; set session optimizer_switch=5; select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off,use_index_extensions=off,skip_scan=off,skip_scan_cost_based=off +index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off,use_index_extensions=off,skip_scan=off,skip_scan_cost_based=off,multi_range_groupby=off select @@session.optimizer_switch; @@session.optimizer_switch -index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off,use_index_extensions=off,skip_scan=off,skip_scan_cost_based=off +index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off,use_index_extensions=off,skip_scan=off,skip_scan_cost_based=off,multi_range_groupby=off set global optimizer_switch="index_merge_sort_union=on"; set session optimizer_switch="index_merge=off"; select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off,use_index_extensions=off,skip_scan=off,skip_scan_cost_based=off +index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off,use_index_extensions=off,skip_scan=off,skip_scan_cost_based=off,multi_range_groupby=off select @@session.optimizer_switch; @@session.optimizer_switch -index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off,use_index_extensions=off,skip_scan=off,skip_scan_cost_based=off +index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off,use_index_extensions=off,skip_scan=off,skip_scan_cost_based=off,multi_range_groupby=off show global variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off,use_index_extensions=off,skip_scan=off,skip_scan_cost_based=off +optimizer_switch index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off,use_index_extensions=off,skip_scan=off,skip_scan_cost_based=off,multi_range_groupby=off show session variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off,use_index_extensions=off,skip_scan=off,skip_scan_cost_based=off +optimizer_switch index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off,use_index_extensions=off,skip_scan=off,skip_scan_cost_based=off,multi_range_groupby=off select * from information_schema.global_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE -OPTIMIZER_SWITCH index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off,use_index_extensions=off,skip_scan=off,skip_scan_cost_based=off +OPTIMIZER_SWITCH index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off,use_index_extensions=off,skip_scan=off,skip_scan_cost_based=off,multi_range_groupby=off select * from information_schema.session_variables where variable_name='optimizer_switch'; VARIABLE_NAME VARIABLE_VALUE -OPTIMIZER_SWITCH index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off,use_index_extensions=off,skip_scan=off,skip_scan_cost_based=off +OPTIMIZER_SWITCH index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off,use_index_extensions=off,skip_scan=off,skip_scan_cost_based=off,multi_range_groupby=off set session optimizer_switch="default"; select @@session.optimizer_switch; @@session.optimizer_switch -index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off,use_index_extensions=off,skip_scan=off,skip_scan_cost_based=off +index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=off,mrr=off,mrr_cost_based=off,block_nested_loop=off,batched_key_access=off,materialization=off,semijoin=off,loosescan=off,firstmatch=off,subquery_materialization_cost_based=off,use_index_extensions=off,skip_scan=off,skip_scan_cost_based=off,multi_range_groupby=off set global optimizer_switch=1.1; ERROR 42000: Incorrect argument type to variable 'optimizer_switch' set global optimizer_switch=1e1; @@ -70,4 +70,4 @@ ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'e' SET @@global.optimizer_switch = @start_global_value; SELECT @@global.optimizer_switch; @@global.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,skip_scan=off,skip_scan_cost_based=on,multi_range_groupby=on diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test index 86bf278..e4f267f 100644 --- a/mysql-test/t/group_min_max.test +++ b/mysql-test/t/group_min_max.test @@ -267,50 +267,112 @@ select a1, max(c) from t2 where a1 in ('a','b','d') group by a1,a2,b; # B) Equalities only over the non-group 'B' attributes # plans explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; +explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; +explain select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b' or b = 'a') group by a1; + explain select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; +explain select a1,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +explain select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; + explain select a1,a2,b, max(c) from t1 where (b = 'b') group by a1,a2; +explain select a1,a2,b, max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; + explain select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2; +explain select a1,a2,b,min(c),max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; + explain select a1,a2, max(c) from t1 where (b = 'b') group by a1,a2; +explain select a1,a2, max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; +explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; + explain select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; +explain select a1,max(c),min(c) from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +explain select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; + explain select a1,a2,b, max(c) from t2 where (b = 'b') group by a1,a2; +explain select a1,a2,b, max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; + explain select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2; +explain select a1,a2,b,min(c),max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; + explain select a1,a2, max(c) from t2 where (b = 'b') group by a1,a2; +explain select a1,a2, max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; # these queries test case 2) in TRP_GROUP_MIN_MAX::update_cost() explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; +explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +explain select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; + explain select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; +explain select a1,max(c),min(c) from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +explain select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; # queries select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; +select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; +select a1,a2,b,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b' or b = 'a') group by a1; + select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b') group by a1; +select a1,max(c),min(c) from t1 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +select a1,max(c),min(c) from t1 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; + select a1,a2,b, max(c) from t1 where (b = 'b') group by a1,a2; +select a1,a2,b, max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; + select a1,a2,b,min(c),max(c) from t1 where (b = 'b') group by a1,a2; +select a1,a2,b,min(c),max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; + select a1,a2, max(c) from t1 where (b = 'b') group by a1,a2; +select a1,a2, max(c) from t1 where (b = 'b' or b = 'a') group by a1,a2; select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; +select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; + select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') group by a1; +select a1,max(c),min(c) from t2 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +select a1,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; + select a1,a2,b, max(c) from t2 where (b = 'b') group by a1,a2; +select a1,a2,b, max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; + select a1,a2,b,min(c),max(c) from t2 where (b = 'b') group by a1,a2; +select a1,a2,b,min(c),max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; + select a1,a2, max(c) from t2 where (b = 'b') group by a1,a2; +select a1,a2, max(c) from t2 where (b = 'b' or b = 'a') group by a1,a2; # these queries test case 2) in TRP_GROUP_MIN_MAX::update_cost() select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; +select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +select a1,a2,b,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; + select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b') group by a1; +select a1,max(c),min(c) from t3 where (a2 = 'a' or a2 = 'b') and (b = 'b') group by a1; +select a1,max(c),min(c) from t3 where (a2 = 'a') and (b = 'b' or b = 'a') group by a1; # IS NULL (makes sense for t2 only) # plans explain select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1; +explain select a1,a2,b,min(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1; + explain select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1; +explain select a1,a2,b,max(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1; + explain select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2; explain select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2; explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; -explain select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; + # queries select a1,a2,b,min(c) from t2 where (a2 = 'a') and b is NULL group by a1; +select a1,a2,b,min(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1; select a1,a2,b,max(c) from t2 where (a2 = 'a') and b is NULL group by a1; +select a1,a2,b,max(c) from t2 where (a2 = 'a' or a2 = 'b') and b is NULL group by a1; select a1,a2,b,min(c) from t2 where b is NULL group by a1,a2; select a1,a2,b,max(c) from t2 where b is NULL group by a1,a2; select a1,a2,b,min(c),max(c) from t2 where b is NULL group by a1,a2; @@ -460,6 +522,7 @@ select a1,a2,b,min(c) from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') grou explain select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; explain select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; explain select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; +explain select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b; explain select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; --replace_column 9 # @@ -469,17 +532,21 @@ explain select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; --replace_column 9 # explain select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; --replace_column 9 # +explain select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b; +--replace_column 9 # explain select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; # queries select a1,a2,b from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; select a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; +select a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b; select a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; select a1,a2,b from t2 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b; select a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by a1,a2,b; +select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121' or c = 'i121') group by a1,a2,b; select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1,a2,b; # @@ -619,6 +686,9 @@ where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy # non-equality predicate for a non-group select attribute explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b < 'b') group by a1; +explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b < 'b') group by a1; +explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b <= 'b') group by a1; +explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b <= 'b' and b >= 'a') group by a1; explain extended select a1,a2,b from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2,b; # non-group field with an equality predicate that references a keypart after the @@ -641,6 +711,12 @@ explain select a1,a2,count(a2) from t1 group by a1,a2,b; explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b; explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b; +# test multi_range_groupby flag +set optimizer_switch = 'multi_range_groupby=off'; +explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'a' or b = 'b') group by a1; +set optimizer_switch = 'default'; +explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'a' or b = 'b') group by a1; + # # Bug #16710: select distinct doesn't return all it should diff --git a/sql/opt_range.cc b/sql/opt_range.cc index c61495e..115da2f 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -2541,7 +2541,6 @@ public: uint group_prefix_len_arg, uint used_key_parts_arg, uint group_key_parts_arg, KEY *index_info_arg, uint index_arg, uint key_infix_len_arg, - uchar *key_infix_arg, SEL_TREE *tree_arg, SEL_ARG *index_tree_arg, uint param_idx_arg, ha_rows quick_prefix_records_arg) : have_min(have_min_arg), have_max(have_max_arg), @@ -2553,8 +2552,6 @@ public: index_tree(index_tree_arg), param_idx(param_idx_arg), is_index_scan(FALSE), quick_prefix_records(quick_prefix_records_arg) { - if (key_infix_len) - memcpy(this->key_infix, key_infix_arg, key_infix_len); } virtual ~TRP_GROUP_MIN_MAX() {} /* Remove gcc warning */ @@ -11448,12 +11445,13 @@ static inline SEL_ARG * get_index_range_tree(uint index, SEL_TREE* range_tree, PARAM *param, uint *param_idx); static bool get_sel_arg_for_keypart(Field *field, SEL_ARG *index_range_tree, SEL_ARG **cur_range); -static bool get_constant_key_infix(KEY *index_info, SEL_ARG *index_range_tree, - KEY_PART_INFO *first_non_group_part, - KEY_PART_INFO *min_max_arg_part, - KEY_PART_INFO *last_part, THD *thd, - uchar *key_infix, uint *key_infix_len, - KEY_PART_INFO **first_non_infix_part); +static bool check_key_infix(KEY *index_info, SEL_ARG *index_range_tree, + KEY_PART_INFO *first_non_group_part, + KEY_PART_INFO *min_max_arg_part, + KEY_PART_INFO *last_part, THD *thd, + uint *key_infix_len, + double *infix_factor, + KEY_PART_INFO **first_non_infix_part); static bool check_group_min_max_predicates(Item *cond, Item_field *min_max_arg_item, Field::imagetype image_type); @@ -11462,7 +11460,7 @@ static void cost_group_min_max(TABLE* table, KEY *index_info, uint used_key_parts, uint group_key_parts, SEL_TREE *range_tree, SEL_ARG *index_tree, ha_rows quick_prefix_records, - bool have_min, bool have_max, + bool have_min, bool have_max, double infix_factor, double *read_cost, ha_rows *records); @@ -11545,8 +11543,6 @@ cost_group_min_max(TABLE* table, KEY *index_info, uint used_key_parts, (const = NG_i), such that each NG_i is referenced in exactly one conjunct. Informally, the predicates provide constants to fill the gap in the index. - NGA3.If BA <> {}, there can only be one range. TODO: This is a code - limitation and is not strictly needed. See BUG#15947433 WA1. There are no other attributes in the WHERE clause except the ones referenced in predicates RNG, PA, PC, EQ defined above. Therefore WA is subset of (GA union NGA union C) for GA,NGA,C that pass the @@ -11622,7 +11618,6 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) uint index= 0; /* The id of the chosen index. */ uint group_key_parts= 0; // Number of index key parts in the group prefix. uint used_key_parts= 0; /* Number of index key parts used for access. */ - uchar key_infix[MAX_KEY_LENGTH]; /* Constants from equality predicates.*/ uint key_infix_len= 0; /* Length of key_infix. */ TRP_GROUP_MIN_MAX *read_plan= NULL; /* The eventually constructed TRP. */ uint key_part_nr; @@ -11771,11 +11766,11 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) uint cur_group_key_parts= 0; uint cur_group_prefix_len= 0; double cur_read_cost; + double cur_infix_factor= 1.0; ha_rows cur_records; key_map used_key_parts_map; uint max_key_part= 0; uint cur_key_infix_len= 0; - uchar cur_key_infix[MAX_KEY_LENGTH]; uint cur_used_key_parts; /* Check (B1) - if current index is covering. */ @@ -11967,13 +11962,14 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) uint dummy; SEL_ARG *index_range_tree= get_index_range_tree(cur_index, tree, param, &dummy); - if (!get_constant_key_infix(cur_index_info, index_range_tree, - first_non_group_part, min_max_arg_part, - last_part, thd, cur_key_infix, - &cur_key_infix_len, - &first_non_infix_part)) + if (!check_key_infix(cur_index_info, index_range_tree, + first_non_group_part, min_max_arg_part, + last_part, thd, + &cur_key_infix_len, + &cur_infix_factor, + &first_non_infix_part)) { - cause= "nonconst_equality_gap_attribute"; + cause= "non_equality_gap_attribute"; goto next_index; } } @@ -12091,7 +12087,7 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) 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); + cur_infix_factor, &cur_read_cost, &cur_records); /* If cur_read_cost is lower than best_read_cost use cur_index. Do not compare doubles directly because they may have different @@ -12110,8 +12106,6 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) 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; } @@ -12145,7 +12139,6 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) group_prefix_len, used_key_parts, group_key_parts, index_info, index, key_infix_len, - (key_infix_len > 0) ? key_infix : NULL, tree, best_index_tree, best_param_idx, best_quick_prefix_records); if (read_plan) @@ -13365,9 +13358,7 @@ void QUICK_SKIP_SCAN_SELECT::add_keys_and_lengths(String *key_names, 2. "(kp1=2 AND kp2=10) OR (kp1=3 AND kp2=11)" 3. "(kp1=2 AND kp2=10) OR (kp1=3 AND (kp2=10 OR kp2=11))" - This function effectively tests requirement WA2. In combination with - a test that the returned tree has no more than one range it is also - a test of NGA3. + This function effectively tests requirement WA2. @param[in] field The field we want the SEL_ARG tree for @param[in] keypart_tree Root node of the SEL_ARG* tree for the index @@ -13428,43 +13419,42 @@ get_sel_arg_for_keypart(Field *field, } /* - Extract a sequence of constants from a conjunction of equality predicates. + Check for a conjunction of range predicates in sequence. SYNOPSIS - get_constant_key_infix() + check_key_infix() index_info [in] Descriptor of the chosen index. index_range_tree [in] Range tree for the chosen index first_non_group_part [in] First index part after group attribute parts min_max_arg_part [in] The keypart of the MIN/MAX argument if any last_part [in] Last keypart of the index thd [in] Current thread - key_infix [out] Infix of constants to be used for index lookup + infix_factor [out] Increase in number of dives due to infix. key_infix_len [out] Lenghth of the infix first_non_infix_part [out] The first keypart after the infix (if any) DESCRIPTION Test conditions (NGA1, NGA2) from get_best_group_min_max(). Namely, - for each keypart field NGF_i not in GROUP-BY, check that there is a - constant equality predicate among conds with the form (NGF_i = const_ci) or + for each keypart field NGF_i not in GROUP-BY, check that there is at least one + range predicate for each key part among conds with the form (NGF_i = const_ci) or (const_ci = NGF_i). Thus all the NGF_i attributes must fill the 'gap' between the last group-by - attribute and the MIN/MAX attribute in the index (if present). Also ensure - that there is only a single range on NGF_i (NGA3). If these - conditions hold, copy each constant from its corresponding predicate into - key_infix, in the order its NG_i attribute appears in the index, and update - key_infix_len with the total length of the key parts in key_infix. + attribute and the MIN/MAX attribute in the index (if present). If these + conditions hold, update key_infix_len with the total length of the key + parts in key_infix. RETURN TRUE if the index passes the test FALSE o/w */ static bool -get_constant_key_infix(KEY *index_info, SEL_ARG *index_range_tree, - KEY_PART_INFO *first_non_group_part, - KEY_PART_INFO *min_max_arg_part, - KEY_PART_INFO *last_part, THD *thd, - uchar *key_infix, uint *key_infix_len, - KEY_PART_INFO **first_non_infix_part) +check_key_infix(KEY *index_info, SEL_ARG *index_range_tree, + KEY_PART_INFO *first_non_group_part, + KEY_PART_INFO *min_max_arg_part, + KEY_PART_INFO *last_part, THD *thd, + uint *key_infix_len, + double *infix_factor, + KEY_PART_INFO **first_non_infix_part) { SEL_ARG *cur_range; KEY_PART_INFO *cur_part; @@ -13472,26 +13462,19 @@ get_constant_key_infix(KEY *index_info, SEL_ARG *index_range_tree, KEY_PART_INFO *end_part= min_max_arg_part ? min_max_arg_part : last_part; *key_infix_len= 0; - uchar *key_ptr= key_infix; + *infix_factor = 1.0; for (cur_part= first_non_group_part; cur_part != end_part; cur_part++) { cur_range= NULL; - /* - Check NGA3: - 1. get_sel_arg_for_keypart gets the range tree for the 'field' and also - checks for a unique conjunction of this tree with all the predicates - on the earlier keyparts in the index. - 2. Check for multiple ranges on the found keypart tree. - - We assume that index_range_tree points to the leftmost keypart in - the index. - */ if (get_sel_arg_for_keypart(cur_part->field, index_range_tree, &cur_range)) return false; - if (cur_range && cur_range->elements > 1) - return false; + if (!thd->optimizer_switch_flag(OPTIMIZER_MULTI_RANGE_GROUPBY)) + { + if (cur_range && cur_range->elements > 1) + return false; + } if (!cur_range || cur_range->type != SEL_ARG::KEY_RANGE) { @@ -13510,24 +13493,12 @@ get_constant_key_infix(KEY *index_info, SEL_ARG *index_range_tree, return false; uint field_length= cur_part->store_length; - if (cur_range->maybe_null && - cur_range->min_value[0] && cur_range->max_value[0]) + if ((cur_range->maybe_null && + cur_range->min_value[0] && cur_range->max_value[0]) || + memcmp(cur_range->min_value, cur_range->max_value, field_length) == 0) { - /* - cur_range specifies 'IS NULL'. In this case the argument points - to a "null value" (a copy of is_null_string) that we do not - memcmp(), or memcpy to a field. - */ - DBUG_ASSERT (field_length > 0); - *key_ptr= 1; - key_ptr+= field_length; - *key_infix_len+= field_length; - } - else if (memcmp(cur_range->min_value, cur_range->max_value, field_length) == 0) - { /* cur_range specifies an equality condition. */ - memcpy(key_ptr, cur_range->min_value, field_length); - key_ptr+= field_length; *key_infix_len+= field_length; + *infix_factor*= cur_range->elements; } else return false; @@ -13625,6 +13596,8 @@ SEL_ARG * get_index_range_tree(uint index, SEL_TREE* range_tree, PARAM *param, used quick range select if any have_min [in] True if there is a MIN function have_max [in] True if there is a MAX function + infix_factor [in] The number of groups increases by this factor + due to infix permutations. read_cost [out] The cost to retrieve rows via this quick select records [out] The number of rows retrieved @@ -13673,7 +13646,7 @@ SEL_ARG * get_index_range_tree(uint index, SEL_TREE* range_tree, PARAM *param, void cost_group_min_max(TABLE* table, KEY *index_info, uint used_key_parts, uint group_key_parts, SEL_TREE *range_tree, SEL_ARG *index_tree, ha_rows quick_prefix_records, - bool have_min, bool have_max, + bool have_min, bool have_max, double infix_factor, double *read_cost, ha_rows *records) { ha_rows table_records; @@ -13706,7 +13679,7 @@ void cost_group_min_max(TABLE* table, KEY *index_info, uint used_key_parts, { quick_prefix_selectivity= (double) quick_prefix_records / (double) table_records; - num_groups= (uint) rint(num_groups * quick_prefix_selectivity); + num_groups= (uint) rint(num_groups * quick_prefix_selectivity * infix_factor); set_if_bigger(num_groups, 1); } @@ -13797,7 +13770,7 @@ TRP_GROUP_MIN_MAX::make_quick(PARAM *param, bool retrieve_full_rows, group_prefix_len, group_key_parts, used_key_parts, index_info, index, read_cost, records, key_infix_len, - key_infix, parent_alloc, is_index_scan); + parent_alloc, is_index_scan); if (!quick) DBUG_RETURN(NULL); @@ -13826,6 +13799,54 @@ TRP_GROUP_MIN_MAX::make_quick(PARAM *param, bool retrieve_full_rows, DBUG_RETURN(NULL); } } + + /* + Populate key_infix_ranges from index_tree. + */ + if (key_infix_len > 0) + { + uint key_infix_parts = used_key_parts - group_key_parts; + KEY_PART_INFO *key_infix_part= index_info->key_part + group_key_parts; + + /* Find the first keypart for key_infix_ranges. */ + SEL_ARG *key_infix_range= index_tree; + while (key_infix_range) + { + if (key_infix_range->field->eq(key_infix_part->field)) + break; + key_infix_range= key_infix_range->next_key_part; + } + + /* + Since the keyparts in key_infix_ranges must be contiguous, the rest + of the keyparts can be fetched via next_key_part. + + key_infix_range tracks current keypart while cur_range tracks current + range within a keypart. + */ + for (uint i = 0; i < key_infix_parts; i++) + { + SEL_ARG *cur_range= key_infix_range; + DBUG_ASSERT(cur_range->field->eq(key_infix_part[i].field)); + + while (cur_range->prev) + cur_range= cur_range->prev; + + while (cur_range) + { + if (quick->add_range(cur_range, i)) + { + delete quick; + quick= NULL; + DBUG_RETURN(NULL); + } + cur_range= cur_range->next; + } + + key_infix_range= key_infix_range->next_key_part; + } + } + /* Extract the SEL_ARG subtree that contains only ranges for the MIN/MAX attribute, and create an array of QUICK_RANGES to be used by the @@ -13846,7 +13867,7 @@ TRP_GROUP_MIN_MAX::make_quick(PARAM *param, bool retrieve_full_rows, /* Create an array of QUICK_RANGEs for the MIN/MAX argument. */ while (min_max_range) { - if (quick->add_range(min_max_range)) + if (quick->add_range(min_max_range, -1)) { delete quick; quick= NULL; @@ -13901,14 +13922,13 @@ QUICK_GROUP_MIN_MAX_SELECT(TABLE *table, JOIN *join_arg, bool have_min_arg, uint used_key_parts_arg, KEY *index_info_arg, uint use_index, double read_cost_arg, ha_rows records_arg, uint key_infix_len_arg, - uchar *key_infix_arg, MEM_ROOT *parent_alloc, - bool is_index_scan_arg) + MEM_ROOT *parent_alloc, bool is_index_scan_arg) :join(join_arg), index_info(index_info_arg), group_prefix_len(group_prefix_len_arg), group_key_parts(group_key_parts_arg), have_min(have_min_arg), have_max(have_max_arg), have_agg_distinct(have_agg_distinct_arg), seen_first_key(FALSE), min_max_arg_part(min_max_arg_part_arg), - key_infix(key_infix_arg), key_infix_len(key_infix_len_arg), + key_infix_len(key_infix_len_arg), min_functions_it(NULL), max_functions_it(NULL), is_index_scan(is_index_scan_arg) { @@ -13920,9 +13940,11 @@ QUICK_GROUP_MIN_MAX_SELECT(TABLE *table, JOIN *join_arg, bool have_min_arg, records= records_arg; used_key_parts= used_key_parts_arg; real_key_parts= used_key_parts_arg; + key_infix_parts= used_key_parts_arg - group_key_parts_arg; real_prefix_len= group_prefix_len + key_infix_len; group_prefix= NULL; min_max_arg_len= min_max_arg_part ? min_max_arg_part->store_length : 0; + memset(cur_key_infix_range, 0, sizeof(cur_key_infix_range)); /* We can't have parent_alloc set as the init function can't handle this case @@ -13973,15 +13995,11 @@ int QUICK_GROUP_MIN_MAX_SELECT::init() if (key_infix_len > 0) { - /* - The memory location pointed to by key_infix will be deleted soon, so - allocate a new buffer and copy the key_infix into it. - */ - uchar *tmp_key_infix= (uchar*) alloc_root(&alloc, key_infix_len); - if (!tmp_key_infix) - return 1; - memcpy(tmp_key_infix, this->key_infix, key_infix_len); - this->key_infix= tmp_key_infix; + for (uint i = 0; i < key_infix_parts; i++) + { + if (my_init_dynamic_array(&key_infix_ranges[i], sizeof(QUICK_RANGE*), 16, 16)) + return 1; + } } if (min_max_arg_part) @@ -14046,6 +14064,10 @@ QUICK_GROUP_MIN_MAX_SELECT::~QUICK_GROUP_MIN_MAX_SELECT() head->file->ha_index_or_rnd_end(); if (min_max_arg_part) delete_dynamic(&min_max_ranges); + for (uint i = 0; i < key_infix_parts; i++) + { + delete_dynamic(&key_infix_ranges[i]); + } free_root(&alloc,MYF(0)); delete min_functions_it; delete max_functions_it; @@ -14060,6 +14082,8 @@ QUICK_GROUP_MIN_MAX_SELECT::~QUICK_GROUP_MIN_MAX_SELECT() SYNOPSIS QUICK_GROUP_MIN_MAX_SELECT::add_range() sel_range Range object from which a + idx If negative, inserts into min_max_ranges + otherwise insert into key_infix_ranges[idx] NOTES Construct a new QUICK_RANGE object from a SEL_ARG object, and @@ -14072,10 +14096,24 @@ QUICK_GROUP_MIN_MAX_SELECT::~QUICK_GROUP_MIN_MAX_SELECT() TRUE otherwise */ -bool QUICK_GROUP_MIN_MAX_SELECT::add_range(SEL_ARG *sel_range) +bool QUICK_GROUP_MIN_MAX_SELECT::add_range(SEL_ARG *sel_range, int idx) { QUICK_RANGE *range; uint range_flag= sel_range->min_flag | sel_range->max_flag; + DYNAMIC_ARRAY *array= NULL; + uint key_length= 0; + if (idx < 0) + { + array= &min_max_ranges; + key_length= min_max_arg_len; + } + else + { + DBUG_ASSERT((uint)idx < MAX_REF_PARTS); + KEY_PART_INFO *key_infix_part= index_info->key_part + group_key_parts + idx; + array= &key_infix_ranges[idx]; + key_length= key_infix_part->store_length; + } /* Skip (-inf,+inf) ranges, e.g. (x < 5 or x > 4). */ if ((range_flag & NO_MIN_RANGE) && (range_flag & NO_MAX_RANGE)) @@ -14093,18 +14131,20 @@ bool QUICK_GROUP_MIN_MAX_SELECT::add_range(SEL_ARG *sel_range) else if (!sel_range->min_value[0] && !sel_range->max_value[0] && memcmp(sel_range->min_value, sel_range->max_value, - min_max_arg_len) == 0) + key_length) == 0) range_flag|= EQ_RANGE; /* equality condition */ } - range= new QUICK_RANGE(sel_range->min_value, min_max_arg_len, + range= new QUICK_RANGE(sel_range->min_value, key_length, make_keypart_map(sel_range->part), - sel_range->max_value, min_max_arg_len, + sel_range->max_value, key_length, make_keypart_map(sel_range->part), range_flag); if (!range) return TRUE; - if (insert_dynamic(&min_max_ranges, &range)) + + if (insert_dynamic(array, &range)) return TRUE; + return FALSE; } @@ -14313,6 +14353,7 @@ int QUICK_GROUP_MIN_MAX_SELECT::get_next() do { result= next_prefix(); + /* Check if this is the last group prefix. Notice that at this point this->record contains the current prefix in record format. @@ -14330,34 +14371,52 @@ int QUICK_GROUP_MIN_MAX_SELECT::get_next() break; } - if (have_min) - { - min_res= next_min(); - if (min_res == 0) - update_min_result(); - } - /* If there is no MIN in the group, there is no MAX either. */ - if ((have_max && !have_min) || - (have_max && have_min && (min_res == 0))) + /* Reset various per-group variables. */ + reset_group(); + + while (!append_next_infix()) { - max_res= next_max(); - if (max_res == 0) - update_max_result(); - /* If a MIN was found, a MAX must have been found as well. */ - DBUG_ASSERT((have_max && !have_min) || - (have_max && have_min && (max_res == 0))); - } - /* - If this is just a GROUP BY or DISTINCT without MIN or MAX and there - are equality predicates for the key parts after the group, find the - first sub-group with the extended prefix. - */ - if (!have_min && !have_max && key_infix_len > 0) - result= head->file->ha_index_read_map(record, group_prefix, - make_prev_keypart_map(real_key_parts), - HA_READ_KEY_EXACT); + if (have_min) + { + min_res= next_min(); + if (min_res == 0) + update_min_result(); + } + /* If there is no MIN in the group, there is no MAX either. */ + if ((have_max && !have_min) || + (have_max && have_min && (min_res == 0))) + { + max_res= next_max(); + if (max_res == 0) + update_max_result(); + /* If a MIN was found, a MAX must have been found as well. */ + DBUG_ASSERT((have_max && !have_min) || + (have_max && have_min && (max_res == 0))); + } + /* + If this is just a GROUP BY or DISTINCT without MIN or MAX and there + are equality predicates for the key parts after the group, find the + first sub-group with the extended prefix. + */ + if (!have_min && !have_max && key_infix_len > 0) + result= head->file->ha_index_read_map(record, group_prefix, + make_prev_keypart_map(real_key_parts), + HA_READ_KEY_EXACT); + + result= have_min ? min_res : have_max ? max_res : result; + + /* + If this is just a GROUP BY or DISTINCT without MIN or MAX, there is no + need to iterate through the whole group to accumulate the MIN/MAX and + returning just the one distinct record is enough. + */ + if (!have_min && !have_max && result == 0) + break; - result= have_min ? min_res : have_max ? max_res : result; + /* Break early on error. */ + if (result != HA_ERR_KEY_NOT_FOUND && result != HA_ERR_END_OF_FILE && result != 0) + break; + } } while ((result == HA_ERR_KEY_NOT_FOUND || result == HA_ERR_END_OF_FILE) && is_last_prefix != 0); @@ -14550,10 +14609,8 @@ static int index_next_different (bool is_index_scan, handler *file, Determine the prefix of the next group that satisfies the query conditions. If there is a range condition referencing the group attributes, use a QUICK_RANGE_SELECT object to retrieve the *first* key that satisfies the - condition. If there is a key infix of constants, append this infix - immediately after the group attributes. The possibly extended prefix is - stored in this->group_prefix. The first key of the found group is stored in - this->record, on which relies this->next_min(). + condition. The prefix is stored in this->group_prefix. The first key of + the found group is stored in this->record, on which relies this->next_min(). RETURN 0 on success @@ -14598,14 +14655,99 @@ int QUICK_GROUP_MIN_MAX_SELECT::next_prefix() /* Save the prefix of this group for subsequent calls. */ key_copy(group_prefix, record, index_info, group_prefix_len); - /* Append key_infix to group_prefix. */ - if (key_infix_len > 0) - memcpy(group_prefix + group_prefix_len, - key_infix, key_infix_len); DBUG_RETURN(0); } +/* + Determine and append the next infix. + + SYNOPSIS + QUICK_GROUP_MIN_MAX_SELECT::append_next_infix() + + DESCRIPTION + Appends the next infix onto this->group_prefix based on the current + position stored in cur_key_infix_range. + + RETURN + true No next infix exists + false on success +*/ +bool QUICK_GROUP_MIN_MAX_SELECT::append_next_infix() +{ + if (key_infix_finished) + return true; + + if (key_infix_len > 0) + { + uchar *key_ptr= group_prefix + group_prefix_len; + for (uint i = 0; i < key_infix_parts; i++) + { + QUICK_RANGE *cur_range; + DBUG_ASSERT(key_infix_ranges[i].elements > 0); + get_dynamic(&key_infix_ranges[i], (uchar *)&cur_range, cur_key_infix_range[i]); + + memcpy(key_ptr, cur_range->min_key, cur_range->min_length); + key_ptr+= cur_range->min_length; + } + + /* Advance cur_key_infix_range position. */ + for (int i = (int)key_infix_parts - 1; i >= 0; i--) + { + cur_key_infix_range[i]++; + if (cur_key_infix_range[i] == key_infix_ranges[i].elements) + { + cur_key_infix_range[i] = 0; + if (i == 0) + key_infix_finished = true; + } + else + break; + } + } else { + key_infix_finished = true; + } + return false; +} + +/* + Reset various variables used to track position within a group. + + SYNOPSIS + QUICK_GROUP_MIN_MAX_SELECT::reset_group() + + DESCRIPTION + Must be called before each group. + + RETURN + None +*/ + +void QUICK_GROUP_MIN_MAX_SELECT::reset_group() +{ + /* Reset key_infix position. */ + key_infix_finished = false; + memset(cur_key_infix_range, 0, sizeof(cur_key_infix_range)); + + /* Reset min/max aggregators */ + Item_sum *min_func, *max_func; + + if (have_min) { + min_functions_it->rewind(); + while ((min_func= (*min_functions_it)++)) + { + min_func->aggregator_clear(); + } + } + + if (have_max) { + max_functions_it->rewind(); + while ((max_func= (*max_functions_it)++)) + { + max_func->aggregator_clear(); + } + } +} /* Find the minimal key in a group that satisfies some range conditions for the @@ -14866,8 +15008,8 @@ int QUICK_GROUP_MIN_MAX_SELECT::next_max_in_range() DESCRIPTION The method iterates through all MIN functions and updates the result value - of each function by calling Item_sum::reset(), which in turn picks the new - result value from this->head->record[0], previously updated by + of each function by calling Item_sum::aggregator_add(), which in turn picks + the new result value from this->head->record[0], previously updated by next_min(). The updated value is stored in a member variable of each of the Item_sum objects, depending on the value type. @@ -14887,10 +15029,11 @@ void QUICK_GROUP_MIN_MAX_SELECT::update_min_result() min_functions_it->rewind(); while ((min_func= (*min_functions_it)++)) - min_func->reset_and_add(); + { + min_func->aggregator_add(); + } } - /* Update all MAX function results with the newly found value. @@ -14899,8 +15042,8 @@ void QUICK_GROUP_MIN_MAX_SELECT::update_min_result() DESCRIPTION The method iterates through all MAX functions and updates the result value - of each function by calling Item_sum::reset(), which in turn picks the new - result value from this->head->record[0], previously updated by + of each function by calling Item_sum::aggregator_add(), which in turn picks + the new result value from this->head->record[0], previously updated by next_max(). The updated value is stored in a member variable of each of the Item_sum objects, depending on the value type. @@ -14919,7 +15062,9 @@ void QUICK_GROUP_MIN_MAX_SELECT::update_max_result() max_functions_it->rewind(); while ((max_func= (*max_functions_it)++)) - max_func->reset_and_add(); + { + max_func->aggregator_add(); + } } diff --git a/sql/opt_range.h b/sql/opt_range.h index 667bb90..47a4ee9 100644 --- a/sql/opt_range.h +++ b/sql/opt_range.h @@ -817,8 +817,11 @@ private: KEY_PART_INFO *min_max_arg_part; /* The keypart of the only argument field */ /* of all MIN/MAX functions. */ uint min_max_arg_len; /* The length of the MIN/MAX argument field */ - uchar *key_infix; /* Infix of constants from equality predicates. */ uint key_infix_len; + uint key_infix_parts; /* Indicates the number gap attributes we have */ + DYNAMIC_ARRAY key_infix_ranges[MAX_REF_PARTS]; /* The set of QUICK_RANGEs to traverse for every part. */ + uint cur_key_infix_range[MAX_REF_PARTS]; /* Indicates the current permutation of key_infix_ranges. */ + bool key_infix_finished; /* Indicates whether we are finished traversing through all permutations of key_infix_ranges. */ DYNAMIC_ARRAY min_max_ranges; /* Array of range ptrs for the MIN/MAX field. */ uint real_prefix_len; /* Length of key prefix extended with key_infix. */ uint real_key_parts; /* A number of keyparts in the above value. */ @@ -840,6 +843,8 @@ public: QUICK_RANGE_SELECT *quick_prefix_select;/* For retrieval of group prefixes. */ private: int next_prefix(); + bool append_next_infix(); + void reset_group(); int next_min_in_range(); int next_max_in_range(); int next_min(); @@ -853,10 +858,10 @@ public: uint group_prefix_len, uint group_key_parts, uint used_key_parts, KEY *index_info, uint use_index, double read_cost, ha_rows records, uint - key_infix_len, uchar *key_infix, MEM_ROOT - *parent_alloc, bool is_index_scan); + key_infix_len, MEM_ROOT *parent_alloc, + bool is_index_scan); ~QUICK_GROUP_MIN_MAX_SELECT(); - bool add_range(SEL_ARG *sel_range); + bool add_range(SEL_ARG *sel_range, int idx); void update_key_stat(); void adjust_prefix_ranges(); bool alloc_buffers(); diff --git a/sql/sql_priv.h b/sql/sql_priv.h index c5d16d1..f3968f3 100644 --- a/sql/sql_priv.h +++ b/sql/sql_priv.h @@ -242,7 +242,8 @@ template bool valid_buffer_range(T jump, /** The following two work similar to MRR. See above comments. */ #define OPTIMIZER_SKIP_SCAN (1ULL << 16) #define OPTIMIZER_SKIP_SCAN_COST_BASED (1ULL << 17) -#define OPTIMIZER_SWITCH_LAST (1ULL << 18) +#define OPTIMIZER_MULTI_RANGE_GROUPBY (1ULL << 18) +#define OPTIMIZER_SWITCH_LAST (1ULL << 19) /** If OPTIMIZER_SWITCH_ALL is defined, optimizer_switch flags for newer @@ -270,7 +271,8 @@ template bool valid_buffer_range(T jump, OPTIMIZER_SWITCH_FIRSTMATCH | \ OPTIMIZER_SWITCH_SUBQ_MAT_COST_BASED | \ OPTIMIZER_SWITCH_USE_INDEX_EXTENSIONS | \ - OPTIMIZER_SKIP_SCAN_COST_BASED) + OPTIMIZER_SKIP_SCAN_COST_BASED | \ + OPTIMIZER_MULTI_RANGE_GROUPBY) #else #define OPTIMIZER_SWITCH_DEFAULT (OPTIMIZER_SWITCH_INDEX_MERGE | \ OPTIMIZER_SWITCH_INDEX_MERGE_UNION | \ @@ -282,7 +284,8 @@ template bool valid_buffer_range(T jump, OPTIMIZER_SWITCH_MRR_COST_BASED | \ OPTIMIZER_SWITCH_BNL | \ OPTIMIZER_SWITCH_USE_INDEX_EXTENSIONS | \ - OPTIMIZER_SKIP_SCAN_COST_BASED) + OPTIMIZER_SKIP_SCAN_COST_BASED | \ + OPTIMIZER_MULTI_RANGE_GROUPBY) #endif /* Replication uses 8 bytes to store SQL_MODE in the binary log. The day you diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index ee1c65d..6ec55a0 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -2625,6 +2625,7 @@ static const char *optimizer_switch_names[]= "subquery_materialization_cost_based", #endif "use_index_extensions", "skip_scan", "skip_scan_cost_based", + "multi_range_groupby", "default", NullS }; /** propagates changes to @@engine_condition_pushdown */ @@ -2648,7 +2649,7 @@ static Sys_var_flagset Sys_optimizer_switch( " subquery_materialization_cost_based" #endif ", block_nested_loop, batched_key_access, use_index_extensions" - ", skip_scan, skip_scan_cost_based" + ", skip_scan, skip_scan_cost_based, multi_range_groupby" "} and val is one of {on, off, default}", SESSION_VAR(optimizer_switch), CMD_LINE(REQUIRED_ARG), optimizer_switch_names, DEFAULT(OPTIMIZER_SWITCH_DEFAULT),