# # simple test of group_concat function # --disable_warnings drop table if exists t1, t2; --enable_warnings create table t1 (grp int, a bigint unsigned, c char(10) not null, d char(10) not null); insert into t1 values (1,1,"a","a"); insert into t1 values (2,2,"b","a"); insert into t1 values (2,3,"c","b"); insert into t1 values (3,4,"E","a"); insert into t1 values (3,5,"C","b"); insert into t1 values (3,6,"D","b"); insert into t1 values (3,7,"d","d"); insert into t1 values (3,8,"d","d"); insert into t1 values (3,9,"D","c"); # Test of MySQL simple request select grp,group_concat(c) from t1 group by grp; explain extended select grp,group_concat(c) from t1 group by grp; select grp,group_concat(a,c) from t1 group by grp; select grp,group_concat("(",a,":",c,")") from t1 group by grp; # Test of MySQL with options select grp,group_concat(c separator ",") from t1 group by grp; select grp,group_concat(c separator "---->") from t1 group by grp; select grp,group_concat(c order by c) from t1 group by grp; select grp,group_concat(c order by c desc) from t1 group by grp; select grp,group_concat(d order by a) from t1 group by grp; select grp,group_concat(d order by a desc) from t1 group by grp; select grp,group_concat(a order by a,d+c-ascii(c)-a) from t1 group by grp; select grp,group_concat(a order by d+c-ascii(c),a) from t1 group by grp; select grp,group_concat(c order by 1) from t1 group by grp; select grp,group_concat(c order by "c") from t1 group by grp; select grp,group_concat(distinct c order by c) from t1 group by grp; select grp,group_concat(distinct c order by c desc) from t1 group by grp; explain extended select grp,group_concat(distinct c order by c desc) from t1 group by grp; select grp,group_concat(c order by c separator ",") from t1 group by grp; select grp,group_concat(c order by c desc separator ",") from t1 group by grp; select grp,group_concat(distinct c order by c separator ",") from t1 group by grp; explain extended select grp,group_concat(distinct c order by c separator ",") from t1 group by grp; select grp,group_concat(distinct c order by c desc separator ",") from t1 group by grp; # Test of SQL_LIST objects select grp,group_concat(c order by grp desc) from t1 group by grp order by grp; # Test transfer to real values 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 create table t1 (grp int, c char(10)); 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 set group_concat_max_len = 4; select grp,group_concat(c) from t1 group by grp; show warnings; set group_concat_max_len = 1024; # Test errors --error 1111 select group_concat(sum(a)) from t1 group by grp; --error 1054 select grp,group_concat(c order by 2) from t1 group by grp; drop table t1; # Test variable length create table t1 ( URL_ID int(11), URL varchar(80)); create table t2 ( REQ_ID int(11), URL_ID int(11)); insert into t1 values (4,'www.host.com'), (5,'www.google.com'),(5,'www.help.com'); insert into t2 values (1,4), (5,4), (5,5); # Make this order independent --replace_result www.help.com X www.host.com X www.google.com X select REQ_ID, Group_Concat(URL) as URL from t1, t2 where t2.URL_ID = t1.URL_ID group by REQ_ID; # check min/max function --replace_result www.help.com X www.host.com X www.google.com X select REQ_ID, Group_Concat(URL) as URL, Min(t1.URL_ID) urll, Max(t1.URL_ID) urlg from t1, t2 where t2.URL_ID = t1.URL_ID group by REQ_ID; drop table t1; drop table t2; create table t1 (id int, name varchar(16)); insert into t1 values (1,'longername'),(1,'evenlongername'); select ifnull(group_concat(concat(t1.id, ':', t1.name)), 'shortname') as 'without distinct: how it should be' from t1; select distinct ifnull(group_concat(concat(t1.id, ':', t1.name)), 'shortname') as 'with distinct: cutoff at length of shortname' from t1; drop table t1; # check zero rows create table t1(id int); create table t2(id int); insert into t1 values(0),(1); select group_concat(t1.id) FROM t1,t2; drop table t1; drop table t2; # check having create table t1 (bar varchar(32)); insert into t1 values('test1'),('test2'); select group_concat(bar order by concat(bar,bar)) from t1; select group_concat(bar order by concat(bar,bar) desc) from t1; select bar from t1 having group_concat(bar)=''; select bar from t1 having instr(group_concat(bar), "test") > 0; select bar from t1 having instr(group_concat(bar order by concat(bar,bar) desc), "test2,test1") > 0; drop table t1; # ORDER BY fix_fields() create table t1 (a int, a1 varchar(10)); create table t2 (a0 int); 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; # # 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,s1; 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; select t1.a, group_concat(c order by (select c from t2 where t2.a=t1.a limit 1)) as grp from t1 group by 1; select t1.a, 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 group by 1; select t1.a, 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 group by 1; # 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; # # group_concat of expression with GROUP BY and external GROUP BY # CREATE TABLE t1 ( a int ); CREATE TABLE t2 ( a int ); INSERT INTO t1 VALUES (1), (2); INSERT INTO t2 VALUES (1), (2); SELECT GROUP_CONCAT(t1.a*t2.a ORDER BY t2.a) FROM t1, t2 GROUP BY t1.a; DROP TABLE t1, t2; # # Bug #4035: group_concat() and HAVING # CREATE TABLE t1 (a char(4)); INSERT INTO t1 VALUES ('John'), ('Anna'), ('Bill'); SELECT GROUP_CONCAT(a SEPARATOR '||') AS names FROM t1 HAVING names LIKE '%An%'; SELECT GROUP_CONCAT(a SEPARATOR '###') AS names FROM t1 HAVING LEFT(names, 1) ='J'; DROP TABLE t1; # # check blobs # CREATE TABLE t1 ( a int, b TEXT ); INSERT INTO t1 VALUES (1,'First Row'), (2,'Second Row'); SELECT GROUP_CONCAT(b ORDER BY b) FROM t1 GROUP BY a; DROP TABLE t1; # # check null values #2 # CREATE TABLE t1 (A_ID INT NOT NULL,A_DESC CHAR(3) NOT NULL,PRIMARY KEY (A_ID)); INSERT INTO t1 VALUES (1,'ABC'), (2,'EFG'), (3,'HIJ'); CREATE TABLE t2 (A_ID INT NOT NULL,B_DESC CHAR(3) NOT NULL,PRIMARY KEY (A_ID,B_DESC)); INSERT INTO t2 VALUES (1,'A'),(1,'B'),(3,'F'); SELECT t1.A_ID, GROUP_CONCAT(t2.B_DESC) AS B_DESC FROM t1 LEFT JOIN t2 ON t1.A_ID=t2.A_ID GROUP BY t1.A_ID ORDER BY t1.A_DESC; DROP TABLE t1; DROP TABLE t2; # # blobs # create table t1 (a int, b text); insert into t1 values (1, 'bb'), (1, 'ccc'), (1, 'a'), (1, 'bb'), (1, 'ccc'); insert into t1 values (2, 'BB'), (2, 'CCC'), (2, 'A'), (2, 'BB'), (2, 'CCC'); select group_concat(b) from t1 group by a; select group_concat(distinct b) from t1 group by a; select group_concat(b order by b) from t1 group by a; select group_concat(distinct b order by b) from t1 group by a; set local group_concat_max_len=4; select group_concat(b) from t1 group by a; select group_concat(distinct b) from t1 group by a; select group_concat(b order by b) from t1 group by a; select group_concat(distinct b order by b) from t1 group by a; # # long blobs # insert into t1 values (1, concat(repeat('1', 300), '2')), (1, concat(repeat('1', 300), '2')), (1, concat(repeat('0', 300), '1')), (2, concat(repeat('1', 300), '2')), (2, concat(repeat('1', 300), '2')), (2, concat(repeat('0', 300), '1')); set local group_concat_max_len=1024; select group_concat(b) from t1 group by a; select group_concat(distinct b) from t1 group by a; select group_concat(b order by b) from t1 group by a; select group_concat(distinct b order by b) from t1 group by a; set local group_concat_max_len=400; select group_concat(b) from t1 group by a; select group_concat(distinct b) from t1 group by a; select group_concat(b order by b) from t1 group by a; select group_concat(distinct b order by b) from t1 group by a; drop table t1; # # Bug#10201 # create table t1 (a varchar(255) character set cp1250 collate cp1250_general_ci, b varchar(255) character set koi8r); insert into t1 values ('xxx','yyy'); select collation(a) from t1; select collation(group_concat(a)) from t1; create table t2 select group_concat(a) as a from t1; show create table t2; select collation(group_concat(a,_koi8r'test')) from t1; --error 1267 select collation(group_concat(a,_koi8r 0xC1C2)) from t1; --error 1267 select collation(group_concat(a,b)) from t1; drop table t1; drop table t2; # # bug #7769: group_concat returning null is checked in having # CREATE TABLE t1 (id int); SELECT GROUP_CONCAT(id) AS gc FROM t1 HAVING gc IS NULL; DROP TABLE t1; # # Bug #8656: Crash with group_concat on alias in outer table # create table t2 (a int, b int); insert into t2 values (1,1), (2,2); select b x, (select group_concat(x) from t2) from t2; drop table t2; # # Bug #7405: problems with rollup # create table t1 (d int not null auto_increment,primary key(d), a int, b int, c int); insert into t1(a,b) values (1,3), (1,4), (1,2), (2,7), (1,1), (1,2), (2,3), (2,3); select d,a,b from t1 order by a; explain select a, group_concat(b) from t1 group by a with rollup; select a, group_concat(b) from t1 group by a with rollup; select a, group_concat(distinct b) from t1 group by a with rollup; select a, group_concat(b order by b) from t1 group by a with rollup; select a, group_concat(distinct b order by b) from t1 group by a with rollup; drop table t1; # # Bug #12095: GROUP_CONCAT for one row table # CREATE TABLE t1 ( aID smallint(5) unsigned NOT NULL auto_increment, sometitle varchar(255) NOT NULL default '', bID smallint(5) unsigned NOT NULL, PRIMARY KEY (aID), UNIQUE KEY sometitle (sometitle) ); INSERT INTO t1 SET sometitle = 'title1', bID = 1; INSERT INTO t1 SET sometitle = 'title2', bID = 1; CREATE TABLE t2 ( bID smallint(5) unsigned NOT NULL auto_increment, somename varchar(255) NOT NULL default '', PRIMARY KEY (bID), UNIQUE KEY somename (somename) ); INSERT INTO t2 SET somename = 'test'; SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |') FROM t1 JOIN t2 ON t1.bID = t2.bID; INSERT INTO t2 SET somename = 'test2'; SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |') FROM t1 JOIN t2 ON t1.bID = t2.bID; DELETE FROM t2 WHERE somename = 'test2'; SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |') FROM t1 JOIN t2 ON t1.bID = t2.bID; DROP TABLE t1,t2; # # Bug #12861 hang with group_concat insubquery FROM DUAL # select * from (select group_concat('c') from DUAL) t; # # Bug #12859 group_concat in subquery cause incorrect not null # create table t1 ( a int not null default 0); select * from (select group_concat(a) from t1) t2; select group_concat('x') UNION ALL select 1; drop table t1; # # Bug #12863 : missing separators after first empty cancatanated elements # CREATE TABLE t1 (id int, a varchar(9)); INSERT INTO t1 VALUES (2, ''), (1, ''), (2, 'x'), (1, 'y'), (3, 'z'), (3, ''); SELECT GROUP_CONCAT(a) FROM t1; SELECT GROUP_CONCAT(a ORDER BY a) FROM t1; SELECT GROUP_CONCAT(a) FROM t1 GROUP BY id; SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY id; DROP TABLE t1; # End of 4.1 tests