diff options
Diffstat (limited to 'mysql-test/r/group_min_max.result')
| -rw-r--r-- | mysql-test/r/group_min_max.result | 71 |
1 files changed, 37 insertions, 34 deletions
diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index 3f8bb6395e9..81cdad8c523 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -1,6 +1,6 @@ drop table if exists t1; create table t1 ( -a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' ' +a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(248) default ' ' ); insert into t1 (a1, a2, b, c, d) values ('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), @@ -43,7 +43,7 @@ Table Op Msg_type Msg_text test.t1 analyze status Table is already up to date drop table if exists t2; create table t2 ( -a1 char(64), a2 char(64) not null, b char(16), c char(16), d char(16), dummy char(64) default ' ' +a1 char(64), a2 char(64) not null, b char(16), c char(16), d char(16), dummy char(248) default ' ' ); insert into t2 select * from t1; insert into t2 (a1, a2, b, c, d) values @@ -1358,8 +1358,9 @@ explain select a1,a2,b,min(c),max(c) from t1 where exists ( select * from t2 where t2.c = t1.c ) group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index -2 DEPENDENT SUBQUERY t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index +1 PRIMARY t1 index NULL idx_t1_1 163 NULL 128 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 func 1 +2 MATERIALIZED t2 index NULL idx_t2_1 163 NULL 164 Using index select a1,a2,b,min(c),max(c) from t1 where exists ( select * from t2 where t2.c = t1.c ) group by a1,a2,b; @@ -1384,7 +1385,7 @@ explain select a1,a2,b,min(c),max(c) from t1 where exists ( select * from t2 where t2.c > 'b1' ) group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by +1 PRIMARY t1 index NULL idx_t1_1 163 NULL 128 Using index 2 SUBQUERY t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index select a1,a2,b,min(c),max(c) from t1 where exists ( select * from t2 where t2.c > 'b1' ) @@ -1410,20 +1411,20 @@ explain select a1,a2,b,c,min(c), max(c) from t1 where exists ( select * from t2 where t1.b > 'a' and t2.c > 'b1' ) group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by +1 PRIMARY t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index 2 DEPENDENT SUBQUERY t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index select a1,a2,b,c,min(c), max(c) from t1 where exists ( select * from t2 where t1.b > 'a' and t2.c > 'b1' ) group by a1,a2,b; a1 a2 b c min(c) max(c) -a a b h112 e112 h112 -a b b p122 m122 p122 -b a b h212 e212 h212 -b b b p222 m222 p222 -c a b h312 e312 h312 -c b b p322 m322 p322 -d a b h412 e412 h412 -d b b p422 m422 p422 +a a b e112 e112 h112 +a b b m122 m122 p122 +b a b e212 e212 h212 +b b b m222 m222 p222 +c a b e312 e312 h312 +c b b m322 m322 p322 +d a b e412 e412 h412 +d b b m422 m422 p422 SET @save_optimizer_switch=@@optimizer_switch; SET optimizer_switch='semijoin_with_cache=off'; explain select a1,a2,b,c,min(c), max(c) from t1 @@ -1432,7 +1433,7 @@ where t2.c in (select c from t3 where t3.c > t1.b) and t2.c > 'b1' ) group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by +1 PRIMARY t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index 2 DEPENDENT SUBQUERY t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index 2 DEPENDENT SUBQUERY t3 index NULL idx_t3_1 10 NULL 192 Using where; Using index; FirstMatch(t2) select a1,a2,b,c,min(c), max(c) from t1 @@ -1441,22 +1442,22 @@ where t2.c in (select c from t3 where t3.c > t1.b) and t2.c > 'b1' ) group by a1,a2,b; a1 a2 b c min(c) max(c) -a a a d111 a111 d111 -a a b h112 e112 h112 -a b a l121 i121 l121 -a b b p122 m122 p122 -b a a d211 a211 d211 -b a b h212 e212 h212 -b b a l221 i221 l221 -b b b p222 m222 p222 -c a a d311 a311 d311 -c a b h312 e312 h312 -c b a l321 i321 l321 -c b b p322 m322 p322 -d a a d411 a411 d411 -d a b h412 e412 h412 -d b a l421 i421 l421 -d b b p422 m422 p422 +a a a a111 a111 d111 +a a b e112 e112 h112 +a b a i121 i121 l121 +a b b m122 m122 p122 +b a a a211 a211 d211 +b a b e212 e212 h212 +b b a i221 i221 l221 +b b b m222 m222 p222 +c a a a311 a311 d311 +c a b e312 e312 h312 +c b a i321 i321 l321 +c b b m322 m322 p322 +d a a a411 a411 d411 +d a b e412 e412 h412 +d b a i421 i421 l421 +d b b m422 m422 p422 SET optimizer_switch=@save_optimizer_switch; explain select a1,a2,b,c,min(c), max(c) from t1 where exists ( select * from t2 where t1.c > 'a' and t2.c > 'b1' ) @@ -2649,6 +2650,7 @@ DROP TABLE t1; CREATE TABLE t (a INT, b INT, INDEX (a,b)); INSERT INTO t VALUES (2,0), (2,0), (2,1), (2,1); INSERT INTO t SELECT * FROM t; +INSERT INTO t SELECT * FROM t; # test MIN #should use range with index for group by EXPLAIN @@ -3281,6 +3283,7 @@ drop table t1; # CREATE TABLE t1 (a int, b int, KEY (b, a)) ; INSERT INTO t1 VALUES (0,99),(9,99),(4,0),(7,0),(99,0),(7,0),(8,0),(99,0),(1,0); +INSERT INTO t1 VALUES (0,99),(9,99),(4,0),(7,0),(99,0),(7,0),(8,0),(99,0),(1,0); CREATE TABLE t2 (c int) ; INSERT INTO t2 VALUES (0),(1); EXPLAIN @@ -3303,10 +3306,10 @@ MIN(a) b EXPLAIN SELECT MIN(a), b FROM t1 WHERE a > ( SELECT min(c) FROM t2, t1 t1a, t1 t1b WHERE c = 0 ) GROUP BY b; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index NULL b 10 NULL 9 Using where; Using index +1 PRIMARY t1 index NULL b 10 NULL 18 Using where; Using index 2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where -2 SUBQUERY t1a index NULL b 10 NULL 9 Using index; Using join buffer (flat, BNL join) -2 SUBQUERY t1b index NULL b 10 NULL 9 Using index; Using join buffer (incremental, BNL join) +2 SUBQUERY t1a index NULL b 10 NULL 18 Using index; Using join buffer (flat, BNL join) +2 SUBQUERY t1b index NULL b 10 NULL 18 Using index; Using join buffer (incremental, BNL join) SELECT MIN(a), b FROM t1 WHERE a > ( SELECT min(c) FROM t2, t1 t1a, t1 t1b WHERE c = 0 ) GROUP BY b; MIN(a) b 1 0 |