1 files changed, 57 insertions, 13 deletions

diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test
index d004c81f14e..87df427616a 100644
--- a/mysql-test/t/func_gconcat.test
+++ b/mysql-test/t/func_gconcat.test
@@ -50,22 +50,12 @@ select grp,group_concat(c order by grp desc) from t1 group by grp order by grp;
 select grp, group_concat(a separator "")+0 from t1 group by grp;

 select grp, group_concat(a separator "")+0.0 from t1 group by grp;

 select grp, ROUND(group_concat(a separator "")) from t1 group by grp;

+drop table t1;
 
 # Test NULL values
 
-drop table t1;
 create table t1 (grp int, c char(10));
-insert into t1 values (1,NULL);
-insert into t1 values (2,"b");
-insert into t1 values (2,NULL);
-insert into t1 values (3,"E");
-insert into t1 values (3,NULL);
-insert into t1 values (3,"D");
-insert into t1 values (3,NULL);
-insert into t1 values (3,NULL);
-insert into t1 values (3,"D");
-insert into t1 values (4,"");
-insert into t1 values (5,NULL);
+insert into t1 values (1,NULL),(2,"b"),(2,NULL),(3,"E"),(3,NULL),(3,"D"),(3,NULL),(3,NULL),(3,"D"),(4,""),(5,NULL);
 select grp,group_concat(c order by c) from t1 group by grp;
 
 # Test warnings
@@ -129,4 +119,58 @@ insert into t1 values (0,"a"),(0,"b"),(1,"c");
 insert into t2 values (1),(2),(3);
 select  group_concat(a1 order by (t1.a IN (select a0 from t2))) from t1;
 select  group_concat(a1 order by (t1.a)) from t1;
-drop table t1, t2;
\ No newline at end of file
+drop table t1, t2;
+
+#
+# Problem with GROUP BY (Bug #2695)
+#
+
+CREATE TABLE t1 (id1 tinyint(4) NOT NULL, id2 tinyint(4) NOT NULL);
+INSERT INTO t1 VALUES (1, 1),(1, 2),(1, 3),(1, 4),(1, 5),(2, 1),(2, 2),(2, 3);
+CREATE TABLE t2 (id1 tinyint(4) NOT NULL); 
+INSERT INTO t2 VALUES (1),(2),(3),(4),(5);
+SELECT t1.id1, GROUP_CONCAT(t1.id2 ORDER BY t1.id2 ASC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 AND t1.id1=1 GROUP BY t1.id1;
+SELECT t1.id1, GROUP_CONCAT(t1.id2 ORDER BY t1.id2 ASC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 GROUP BY t1.id1;
+SELECT t1.id1, GROUP_CONCAT(t1.id2 ORDER BY t1.id2 DESC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 GROUP BY t1.id1;
+SELECT t1.id1, GROUP_CONCAT(t1.id2 ORDER BY 6-t1.id2 ASC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 GROUP BY t1.id1;
+
+# The following failed when it was run twice:
+SELECT t1.id1, GROUP_CONCAT(t1.id2,6-t1.id2 ORDER BY 6-t1.id2 ASC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 GROUP BY t1.id1;
+SELECT t1.id1, GROUP_CONCAT(t1.id2,6-t1.id2 ORDER BY 6-t1.id2 ASC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 GROUP BY t1.id1;
+
+SELECT t1.id1, GROUP_CONCAT(t1.id2,"/",6-t1.id2 ORDER BY 1+0,6-t1.id2,t1.id2 ASC) AS concat_id FROM t1, t2 WHERE t1.id1 = t2.id1 GROUP BY t1.id1;
+drop table t1,t2;
+
+#
+# Problem with distinct (Bug #3381)
+#
+
+create table t1 (s1 char(10), s2 int not null);
+insert into t1 values ('a',2),('b',2),('c',1),('a',3),('b',4),('c',4);
+select distinct s1 from t1 order by s2;
+select group_concat(distinct s1) from t1;
+select group_concat(distinct s1 order by s2) from t1 where s2 < 4;
+# The following is wrong and needs to be fixed ASAP
+select group_concat(distinct s1 order by s2) from t1;
+drop table t1;
+
+#
+# Test with subqueries (Bug #3319)
+#
+
+create table t1 (a int, c int); 
+insert into t1 values (1, 2), (2, 3), (2, 4), (3, 5);
+create table t2 (a int, c int);
+insert into t2 values (1, 5), (2, 4), (3, 3), (3,3);
+select group_concat(c) from t1;
+select group_concat(c order by (select c from t2 where t2.a=t1.a limit 1)) as grp from t1;
+
+select group_concat(c order by (select mid(group_concat(c order by a),1,5) from t2 where t2.a=t1.a)) as grp from t1;
+select group_concat(c order by (select mid(group_concat(c order by a),1,5) from t2 where t2.a=t1.a) desc) as grp from t1;
+
+# The following returns random results as we are sorting on blob addresses
+# select group_concat(c order by (select group_concat(c order by a) from t2 where t2.a=t1.a)) as grp from t1;
+# select group_concat(c order by (select group_concat(c) from t2 where a=t1.a)) as grp from t1;
+
+select a,c,(select group_concat(c order by a) from t2 where a=t1.a) as grp from t1 order by grp;
+drop table t1,t2;