diff options
Diffstat (limited to 'mysql-test/r/group_by.result')
| -rw-r--r-- | mysql-test/r/group_by.result | 36 |
1 files changed, 17 insertions, 19 deletions
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 337d5639056..5a5689f0185 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -29,6 +29,8 @@ PRIMARY KEY (userID) INSERT INTO t2 VALUES (1,'name','pass','mail','Y','v','n','adr','1','1','1'); INSERT INTO t2 VALUES (2,'name','pass','mail','Y','v','n','adr','1','1','1'); INSERT INTO t2 VALUES (3,'name','pass','mail','Y','v','n','adr','1','1','1'); +INSERT INTO t2 VALUES (4,'name','pass','mail','Y','v','n','adr','1','1','1'); +INSERT INTO t2 VALUES (5,'name','pass','mail','Y','v','n','adr','1','1','1'); SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid; userid MIN(t1.score) 1 1 @@ -49,8 +51,12 @@ userid MIN(t1.score+0.0) 2 2.0 SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2 GROUP BY t2.userid ORDER BY NULL; userid MIN(t1.score+0.0) -1 1.0 2 2.0 +1 1.0 +EXPLAIN SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2 GROUP BY t2.userid ORDER BY NULL; +table type possible_keys key key_len ref rows Extra +t1 ALL NULL NULL NULL NULL 4 Using where; Using temporary +t2 eq_ref PRIMARY PRIMARY 4 t1.userID 1 Using index drop table t1,t2; CREATE TABLE t1 ( PID int(10) unsigned NOT NULL auto_increment, @@ -244,7 +250,7 @@ score smallint(5) unsigned, key (spid), key (score) ); -INSERT INTO t1 VALUES (1,1,1),(2,2,2),(2,1,1),(3,3,3),(4,3,3),(5,3,3); +INSERT INTO t1 VALUES (1,1,1),(2,2,2),(2,1,1),(3,3,3),(4,3,3),(5,3,3),(6,3,3),(7,3,3); explain select userid,count(*) from t1 group by userid desc; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort @@ -253,16 +259,14 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary select userid,count(*) from t1 group by userid desc; userid count(*) -3 3 +3 5 2 1 1 2 select userid,count(*) from t1 group by userid desc having (count(*)+1) IN (4,3); userid count(*) -3 3 1 2 select userid,count(*) from t1 group by userid desc having 3 IN (1,COUNT(*)); userid count(*) -3 3 explain select spid,count(*) from t1 where spid between 1 and 2 group by spid desc; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range spID spID 5 NULL 2 Using where; Using index @@ -282,12 +286,14 @@ spid count(*) 1 1 explain select sql_big_result spid,sum(userid) from t1 group by spid desc; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using filesort explain select sql_big_result spid,sum(userid) from t1 group by spid desc order by null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 6 +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 select sql_big_result spid,sum(userid) from t1 group by spid desc; spid sum(userid) +7 3 +6 3 5 3 4 3 3 3 @@ -295,13 +301,13 @@ spid sum(userid) 1 1 explain select sql_big_result score,count(*) from t1 group by score desc; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL score 3 NULL 6 Using index +1 SIMPLE t1 index NULL score 3 NULL 8 Using index explain select sql_big_result score,count(*) from t1 group by score desc order by null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL score 3 NULL 6 Using index +1 SIMPLE t1 index NULL score 3 NULL 8 Using index select sql_big_result score,count(*) from t1 group by score desc; score count(*) -3 3 +3 5 2 1 1 2 drop table t1; @@ -536,15 +542,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL a NULL NULL NULL 4 Using where drop table t1,t2; create table t1 (a int, b int); -insert into t1 values (1, 4); -insert into t1 values (10, 40); -insert into t1 values (1, 4); -insert into t1 values (10, 43); -insert into t1 values (1, 4); -insert into t1 values (10, 41); -insert into t1 values (1, 4); -insert into t1 values (10, 43); -insert into t1 values (1, 4); +insert into t1 values (1, 4),(10, 40),(1, 4),(10, 43),(1, 4),(10, 41),(1, 4),(10, 43),(1, 4); select a, MAX(b), INTERVAL (MAX(b), 1,3,10,30,39,40,50,60,100,1000) from t1 group by a; a MAX(b) INTERVAL (MAX(b), 1,3,10,30,39,40,50,60,100,1000) 1 4 2 |