Merge with 4.0.11

1 files changed, 83 insertions, 0 deletions

diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index a63054da88b..40e2c72ff32 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -34,6 +34,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
@@ -42,6 +47,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 t1,t2;
 CREATE TABLE t1 (
 PID int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
@@ -88,6 +97,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;
@@ -237,6 +248,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;
 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
+explain select userid,count(*) from t1 group by userid 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	Using temporary
 select userid,count(*) from t1 group by userid desc;
 userid	count(*)
 3	3
@@ -252,6 +266,9 @@ userid	count(*)
 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
+explain select spid,count(*) from t1 where spid between 1 and 2 group by spid order by null;
+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
 explain select spid,count(*) from t1 where spid between 1 and 2 group by spid;
 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
@@ -266,6 +283,9 @@ spid	count(*)
 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
+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
 select sql_big_result spid,sum(userid) from t1 group by spid desc;
 spid	sum(userid)
 5	3
@@ -276,6 +296,9 @@ spid	sum(userid)
 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
+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
 select sql_big_result score,count(*) from t1 group by score desc;
 score	count(*)
 3	3
@@ -482,3 +505,63 @@ 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;
+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);
+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
+10	43	6
+select a, MAX(b), CASE MAX(b) when 4 then 4 when 43 then 43 else 0 end from t1 group by a;
+a	MAX(b)	CASE MAX(b) when 4 then 4 when 43 then 43 else 0 end
+1	4	4
+10	43	43
+select a, MAX(b), FIELD(MAX(b), '43', '4', '5') from t1 group by a;
+a	MAX(b)	FIELD(MAX(b), '43', '4', '5')
+1	4	2
+10	43	1
+select a, MAX(b), CONCAT_WS(MAX(b), '43', '4', '5') from t1 group by a;
+a	MAX(b)	CONCAT_WS(MAX(b), '43', '4', '5')
+1	4	434445
+10	43	43434435
+select a, MAX(b), ELT(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f') from t1 group by a;
+a	MAX(b)	ELT(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f')
+1	4	d
+10	43	NULL
+select a, MAX(b), MAKE_SET(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h') from t1 group by a;
+a	MAX(b)	MAKE_SET(MAX(b), 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h')
+1	4	c
+10	43	a,b,d,f
+drop table t1;