diff options
Diffstat (limited to 'mysql-test/main/group_min_max.result')
| -rw-r--r-- | mysql-test/main/group_min_max.result | 24 |
1 files changed, 12 insertions, 12 deletions
diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result index 73db7e5ff96..d8c6d6c0929 100644 --- a/mysql-test/main/group_min_max.result +++ b/mysql-test/main/group_min_max.result @@ -2080,7 +2080,7 @@ id select_type table type possible_keys key key_len ref rows Extra explain extended select a1,a2,min(b),max(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; 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 276 100.00 Using where; Using index +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 276 97.06 Using where; Using index Warnings: Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,min(`test`.`t1`.`b`) AS `min(b)`,max(`test`.`t1`.`b`) AS `max(b)` from `test`.`t1` where (`test`.`t1`.`a1` = 'b' or `test`.`t1`.`a1` = 'd' or `test`.`t1`.`a1` = 'a' or `test`.`t1`.`a1` = 'c') and `test`.`t1`.`a2` > 'a' and `test`.`t1`.`c` > 'a111' group by `test`.`t1`.`a1`,`test`.`t1`.`a2` explain extended select a1,a2,b,min(c),max(c) from t1 @@ -2100,7 +2100,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL idx_t2_1 163 NULL 548 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 276 100.00 Using where; Using index +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 130 NULL 276 97.06 Using where; Using index Warnings: Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a1` = 'b' or `test`.`t1`.`a1` = 'd' or `test`.`t1`.`a1` = 'a' or `test`.`t1`.`a1` = 'c') and `test`.`t1`.`a2` > 'a' and `test`.`t1`.`c` > 'a111' group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` explain select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1; @@ -2230,7 +2230,7 @@ a BB EXPLAIN SELECT a FROM t1 WHERE a='AA' GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref PRIMARY PRIMARY 7 const 4 Using where; Using index +1 SIMPLE t1 range PRIMARY PRIMARY 7 NULL 1 Using where; Using index for group-by EXPLAIN SELECT a FROM t1 WHERE a='BB' GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref PRIMARY PRIMARY 7 const 1 Using where; Using index @@ -2368,11 +2368,11 @@ CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c)); INSERT INTO t2 SELECT a,b,b FROM t1; explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a,b a 10 NULL 2 Using where; Using index for group-by +1 SIMPLE t1 range a,b a 10 NULL 1 Using where; Using index for group-by insert into t1 select 1,seq from seq_1_to_100; explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a,b a 10 NULL 2 Using where; Using index for group-by +1 SIMPLE t1 range a,b a 10 NULL 1 Using where; Using index for group-by analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected @@ -2388,7 +2388,7 @@ MIN(b) a 2 1 explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range PRIMARY PRIMARY 12 NULL 1 Using where; Using index +1 SIMPLE t2 range PRIMARY PRIMARY 12 NULL 1 Using where; Using index for group-by SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a; MIN(c) 2 @@ -2678,7 +2678,7 @@ a b 3 13 explain extended select sql_buffer_result a, max(b)+1 from t1 where a = 0 group by a; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range a,index a 5 NULL 3 100.00 Using where; Using index for group-by; Using temporary +1 SIMPLE t1 range a,index a 5 NULL 1 100.00 Using where; Using index for group-by; Using temporary Warnings: Note 1003 select sql_buffer_result `test`.`t1`.`a` AS `a`,max(`test`.`t1`.`b`) + 1 AS `max(b)+1` from `test`.`t1` where `test`.`t1`.`a` = 0 group by `test`.`t1`.`a` drop table t1; @@ -3579,7 +3579,7 @@ a c COUNT(DISTINCT c, a, b) EXPLAIN SELECT COUNT(DISTINCT c, a, b) FROM t2 WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 5 NULL 1 Using where; Using index +1 SIMPLE t2 range a a 15 NULL 1 Using where; Using index for group-by SELECT COUNT(DISTINCT c, a, b) FROM t2 WHERE a > 5 AND b BETWEEN 10 AND 20 GROUP BY a, b, c; COUNT(DISTINCT c, a, b) @@ -3673,7 +3673,7 @@ insert into t1 values(1,'A'),(1 , 'B'), (1, 'C'), (2, 'A'), (3, 'A'), (3, 'B'), (3, 'C'), (3, 'D'); explain SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL f1 5 NULL 8 Using index for group-by (scanning) +1 SIMPLE t1 range NULL f1 5 NULL 8 Using index for group-by SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1; f1 COUNT(DISTINCT f2) 1 3 @@ -4036,10 +4036,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away explain select min(a) from t1 where a between "a" and "Cafeeeeeeeeeeeeeeeeeeeeeeeeee"; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index a a 13 NULL 2 Using where; Using index +1 SIMPLE t1 range a a 13 NULL 2 Using where; Using index explain select min(a) from t1 where a between "abbbbbbbbbbbbbbbbbbbb" and "Cafe2"; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index a a 13 NULL 2 Using where; Using index +1 SIMPLE t1 range a a 13 NULL 2 Using where; Using index drop table t1; # # MDEV-15433: Optimizer does not use group by optimization with distinct @@ -4106,7 +4106,7 @@ CREATE TABLE t1 (p int NOT NULL, a int NOT NULL, PRIMARY KEY (p,a)); insert into t1 select 2,seq from seq_0_to_1000; EXPLAIN select MIN(a) from t1 where p = 2 group by p; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 10 Using where; Using index for group-by +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using index for group-by SELECT MIN(a) from t1 where p = 2 group by p; MIN(a) 0 |