diff options
Diffstat (limited to 'mysql-test/r/group_by.result')
| -rw-r--r-- | mysql-test/r/group_by.result | 48 |
1 files changed, 48 insertions, 0 deletions
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 02773f2eb44..35867cf6532 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -32,6 +32,11 @@ userid MIN(t1.score) 1 1 2 2 3 3 +SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID GROUP BY t2.userid ORDER BY NULL; +userid MIN(t1.score) +1 1 +2 2 +3 3 SELECT t2.userid, MIN(t1.score) FROM t1, t2 WHERE t1.userID=t2.userID AND t1.spID=2 GROUP BY t2.userid; userid MIN(t1.score) 1 1 @@ -40,6 +45,10 @@ SELECT t2.userid, MIN(t1.score+0.0) FROM t1, t2 WHERE t1.userID=t2.userID AND t1 userid MIN(t1.score+0.0) 1 1.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 drop table test.t1,test.t2; CREATE TABLE t1 ( PID int(10) unsigned DEFAULT '0' NOT NULL auto_increment, @@ -86,6 +95,8 @@ INSERT INTO t2 VALUES (91,2); INSERT INTO t2 VALUES (92,2); SELECT cid, CONCAT(firstname, ' ', surname), COUNT(call_id) FROM t1 LEFT JOIN t2 ON cid=contact_id WHERE firstname like '%foo%' GROUP BY cid; cid CONCAT(firstname, ' ', surname) COUNT(call_id) +SELECT cid, CONCAT(firstname, ' ', surname), COUNT(call_id) FROM t1 LEFT JOIN t2 ON cid=contact_id WHERE firstname like '%foo%' GROUP BY cid ORDER BY NULL; +cid CONCAT(firstname, ' ', surname) COUNT(call_id) SELECT HIGH_PRIORITY cid, CONCAT(firstname, ' ', surname), COUNT(call_id) FROM t1 LEFT JOIN t2 ON cid=contact_id WHERE firstname like '%foo%' GROUP BY cid ORDER BY surname, firstname; cid CONCAT(firstname, ' ', surname) COUNT(call_id) drop table t1,t2; @@ -235,6 +246,9 @@ INSERT INTO t1 VALUES (1,1,1),(2,2,2),(2,1,1),(3,3,3),(4,3,3),(5,3,3); explain select userid,count(*) from t1 group by userid desc; table type possible_keys key key_len ref rows Extra t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort +explain select userid,count(*) from t1 group by userid desc order by null; +table type possible_keys key key_len ref rows Extra +t1 ALL NULL NULL NULL NULL 6 Using temporary select userid,count(*) from t1 group by userid desc; userid count(*) 3 3 @@ -253,6 +267,9 @@ t1 range spID spID 5 NULL 2 Using where; Using index explain select spid,count(*) from t1 where spid between 1 and 2 group by spid; table type possible_keys key key_len ref rows Extra t1 range spID spID 5 NULL 2 Using where; Using index +explain select spid,count(*) from t1 where spid between 1 and 2 group by spid order by null; +table type possible_keys key key_len ref rows Extra +t1 range spID spID 5 NULL 2 Using where; Using index select spid,count(*) from t1 where spid between 1 and 2 group by spid; spid count(*) 1 1 @@ -264,6 +281,9 @@ spid count(*) explain select sql_big_result spid,sum(userid) from t1 group by spid desc; table type possible_keys key key_len ref rows Extra t1 ALL NULL NULL NULL NULL 6 Using filesort +explain select sql_big_result spid,sum(userid) from t1 group by spid desc order by null; +table type possible_keys key key_len ref rows Extra +t1 ALL NULL NULL NULL NULL 6 select sql_big_result spid,sum(userid) from t1 group by spid desc; spid sum(userid) 5 3 @@ -274,6 +294,9 @@ spid sum(userid) explain select sql_big_result score,count(*) from t1 group by score desc; table type possible_keys key key_len ref rows Extra t1 index NULL score 3 NULL 6 Using index +explain select sql_big_result score,count(*) from t1 group by score desc order by null; +table type possible_keys key key_len ref rows Extra +t1 index NULL score 3 NULL 6 Using index select sql_big_result score,count(*) from t1 group by score desc; score count(*) 3 3 @@ -481,3 +504,28 @@ NULL 9 3 b 1 drop table t1; +create table t1 (a int not null, b int not null); +insert into t1 values (1,1),(1,2),(3,1),(3,2),(2,2),(2,1); +create table t2 (a int not null, b int not null, key(a)); +insert into t2 values (1,3),(3,1),(2,2),(1,1); +select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b; +a b +1 1 +1 3 +2 2 +3 1 +select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL; +a b +1 3 +3 1 +2 2 +1 1 +explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b; +table type possible_keys key key_len ref rows Extra +t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort +t2 ALL a NULL NULL NULL 4 Using where +explain select t1.a,t2.b from t1,t2 where t1.a=t2.a group by t1.a,t2.b ORDER BY NULL; +table type possible_keys key key_len ref rows Extra +t1 ALL NULL NULL NULL NULL 6 Using temporary +t2 ALL a NULL NULL NULL 4 Using where +drop table t1,t2; |