diff options
Diffstat (limited to 'mysql-test/r')
| -rw-r--r-- | mysql-test/r/count_distinct.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/group_by.result | 48 | ||||
| -rw-r--r-- | mysql-test/r/have_crypt.require | 2 |
3 files changed, 54 insertions, 0 deletions
diff --git a/mysql-test/r/count_distinct.result b/mysql-test/r/count_distinct.result index 3bff6c6ad88..81976d268ef 100644 --- a/mysql-test/r/count_distinct.result +++ b/mysql-test/r/count_distinct.result @@ -36,6 +36,10 @@ select t2.isbn,city,t1.libname,count(distinct t1.libname) as a from t3 left join isbn city libname a 007 Berkeley Berkeley Public1 2 000 New York New York Public Libra 2 +select t2.isbn,city,t1.libname,count(distinct t1.libname) as a from t3 left join t1 on t3.libname=t1.libname left join t2 on t3.isbn=t2.isbn group by city having count(distinct concat(t1.libname,'a')) > 1; +isbn city libname a +007 Berkeley Berkeley Public1 2 +000 New York New York Public Libra 2 drop table t1, t2, t3; create table t1 (f1 int); insert into t1 values (1); 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; diff --git a/mysql-test/r/have_crypt.require b/mysql-test/r/have_crypt.require new file mode 100644 index 00000000000..739fbb738f0 --- /dev/null +++ b/mysql-test/r/have_crypt.require @@ -0,0 +1,2 @@ +Variable_name Value +have_crypt YES |