path: root/mysql-test/r/func_gconcat.result

drop table if exists t1, t2;
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");
select grp,group_concat(c) from t1 group by grp;
grp	group_concat(c)
1	a
2	b,c
3	E,C,D,d,d,D
explain extended select grp,group_concat(c) from t1 group by grp;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	9	100.00	Using filesort
Warnings:
Note	1003	select `test`.`t1`.`grp` AS `grp`,group_concat(`test`.`t1`.`c` separator ',') AS `group_concat(c)` from `test`.`t1` group by `test`.`t1`.`grp`
select grp,group_concat(a,c) from t1 group by grp;
grp	group_concat(a,c)
1	1a
2	2b,3c
3	4E,5C,6D,7d,8d,9D
select grp,group_concat("(",a,":",c,")") from t1 group by grp;
grp	group_concat("(",a,":",c,")")
1	(1:a)
2	(2:b),(3:c)
3	(4:E),(5:C),(6:D),(7:d),(8:d),(9:D)
select grp,group_concat(c separator ",") from t1 group by grp;
grp	group_concat(c separator ",")
1	a
2	b,c
3	E,C,D,d,d,D
select grp,group_concat(c separator "---->") from t1 group by grp;
grp	group_concat(c separator "---->")
1	a
2	b---->c
3	E---->C---->D---->d---->d---->D
select grp,group_concat(c order by c) from t1 group by grp;
grp	group_concat(c order by c)
1	a
2	b,c
3	C,D,d,d,D,E
select grp,group_concat(c order by c desc) from t1 group by grp;
grp	group_concat(c order by c desc)
1	a
2	c,b
3	E,D,d,d,D,C
select grp,group_concat(d order by a) from t1 group by grp;
grp	group_concat(d order by a)
1	a
2	a,b
3	a,b,b,d,d,c
select grp,group_concat(d order by a desc) from t1 group by grp;
grp	group_concat(d order by a desc)
1	a
2	b,a
3	c,d,d,b,b,a
select grp,group_concat(a order by a,d+c-ascii(c)-a) from t1 group by grp;
grp	group_concat(a order by a,d+c-ascii(c)-a)
1	1
2	2,3
3	4,5,6,7,8,9
select grp,group_concat(a order by d+c-ascii(c),a) from t1 group by grp;
grp	group_concat(a order by d+c-ascii(c),a)
1	1
2	3,2
3	7,8,4,6,9,5
select grp,group_concat(c order by 1) from t1 group by grp;
grp	group_concat(c order by 1)
1	a
2	b,c
3	C,D,d,d,D,E
select grp,group_concat(distinct c order by c) from t1 group by grp;
grp	group_concat(distinct c order by c)
1	a
2	b,c
3	C,D,E
select grp,group_concat(distinct c order by c desc) from t1 group by grp;
grp	group_concat(distinct c order by c desc)
1	a
2	c,b
3	E,D,C
explain extended select grp,group_concat(distinct c order by c desc) from t1 group by grp;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	9	100.00	Using filesort
Warnings:
Note	1003	select `test`.`t1`.`grp` AS `grp`,group_concat(distinct `test`.`t1`.`c` order by `test`.`t1`.`c` DESC separator ',') AS `group_concat(distinct c order by c desc)` from `test`.`t1` group by `test`.`t1`.`grp`
select grp,group_concat(c order by c separator ",") from t1 group by grp;
grp	group_concat(c order by c separator ",")
1	a
2	b,c
3	C,D,d,d,D,E
select grp,group_concat(c order by c desc separator ",") from t1 group by grp;
grp	group_concat(c order by c desc separator ",")
1	a
2	c,b
3	E,D,d,d,D,C
select grp,group_concat(distinct c order by c separator ",") from t1 group by grp;
grp	group_concat(distinct c order by c separator ",")
1	a
2	b,c
3	C,D,E
explain extended select grp,group_concat(distinct c order by c separator ",") from t1 group by grp;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	9	100.00	Using filesort
Warnings:
Note	1003	select `test`.`t1`.`grp` AS `grp`,group_concat(distinct `test`.`t1`.`c` order by `test`.`t1`.`c` ASC separator ',') AS `group_concat(distinct c order by c separator ",")` from `test`.`t1` group by `test`.`t1`.`grp`
select grp,group_concat(distinct c order by c desc separator ",") from t1 group by grp;
grp	group_concat(distinct c order by c desc separator ",")
1	a
2	c,b
3	E,D,C
select grp,group_concat(c order by grp desc) from t1 group by grp order by grp;
grp	group_concat(c order by grp desc)
1	a
2	c,b
3	D,d,d,D,C,E
select grp, group_concat(a separator "")+0 from t1 group by grp;
grp	group_concat(a separator "")+0
1	1
2	23
3	456789
select grp, group_concat(a separator "")+0.0 from t1 group by grp;
grp	group_concat(a separator "")+0.0
1	1.0
2	23.0
3	456789.0
select grp, ROUND(group_concat(a separator "")) from t1 group by grp;
grp	ROUND(group_concat(a separator ""))
1	1
2	23
3	456789
drop table t1;
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;
grp	group_concat(c order by c)
1	NULL
2	b
3	D,D,E
4	
5	NULL
set group_concat_max_len = 4;
select grp,group_concat(c) from t1 group by grp;
grp	group_concat(c)
1	NULL
2	b
3	D,D,
4	
5	NULL
Warnings:
Warning	1260	Row 4 was cut by GROUP_CONCAT()
show warnings;
Level	Code	Message
Warning	1260	Row 4 was cut by GROUP_CONCAT()
set group_concat_max_len = 1024;
select group_concat(sum(c)) from t1 group by grp;
ERROR HY000: Invalid use of group function
select grp,group_concat(c order by 2) from t1 group by grp;
ERROR 42S22: Unknown column '2' in 'order clause'
drop table t1;
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);
select REQ_ID, Group_Concat(URL) as URL from t1, t2 where
t2.URL_ID = t1.URL_ID group by REQ_ID;
REQ_ID	URL
1	X
5	X,X,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;
REQ_ID	URL	urll	urlg
1	X	4	4
5	X,X,X	4	5
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;
without distinct: how it should be
1:longername,1:evenlongername
select distinct ifnull(group_concat(concat(t1.id, ':', t1.name)), 'shortname') as 'with distinct: cutoff at length of shortname' from t1;
with distinct: cutoff at length of shortname
1:longername,1:evenlongername
drop table t1;
create table t1(id int);
create table t2(id int);
insert into t1 values(0),(1);
select group_concat(t1.id) FROM t1,t2;
group_concat(t1.id)
NULL
drop table t1;
drop table t2;
create table t1 (bar varchar(32));
insert into t1 values('test1'),('test2');
select group_concat(bar order by concat(bar,bar)) from t1;
group_concat(bar order by concat(bar,bar))
test1,test2
select group_concat(bar order by concat(bar,bar) desc) from t1;
group_concat(bar order by concat(bar,bar) desc)
test2,test1
select bar from t1 having group_concat(bar)='';
bar
select bar from t1 having instr(group_concat(bar), "test") > 0;
bar
test1
select bar from t1 having instr(group_concat(bar order by concat(bar,bar) desc), "test2,test1") > 0;
bar
test1
drop table t1;
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;
group_concat(a1 order by (t1.a IN (select a0 from t2)))
b,a,c
select  group_concat(a1 order by (t1.a)) from t1;
group_concat(a1 order by (t1.a))
b,a,c
drop table t1, t2;
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;
id1	concat_id
1	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 GROUP BY t1.id1;
id1	concat_id
1	1,2,3,4,5
2	1,2,3
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;
id1	concat_id
1	5,4,3,2,1
2	3,2,1
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;
id1	concat_id
1	5,4,3,2,1
2	3,2,1
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;
id1	concat_id
1	51,42,33,24,15
2	33,24,15
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;
id1	concat_id
1	51,42,33,24,15
2	33,24,15
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;
id1	concat_id
1	5/1,4/2,3/3,2/4,1/5
2	3/3,2/4,1/5
drop table t1,t2;
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;
s1
c
a
b
select group_concat(distinct s1) from t1;
group_concat(distinct s1)
a,b,c
select group_concat(distinct s1 order by s2) from t1 where s2 < 4;
group_concat(distinct s1 order by s2)
c,b,a
select group_concat(distinct s1 order by s2) from t1;
group_concat(distinct s1 order by s2)
c,b,a
drop table t1;
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;
group_concat(c)
2,3,4,5
select group_concat(c order by (select c from t2 where t2.a=t1.a limit 1)) as grp from t1;
grp
5,4,3,2
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;
grp
5,4,3,2
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;
grp
2,4,3,5
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;
a	grp
1	2
2	4,3
3	5
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;
a	grp
1	2
2	4,3
3	5
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;
a	grp
1	2
2	4,3
3	5
select group_concat(c order by (select concat(5-t1.c,group_concat(c order by a)) from t2 where t2.a=t1.a)) as grp from t1;
grp
5,4,3,2
select group_concat(c order by (select concat(t1.c,group_concat(c)) from t2 where a=t1.a)) as grp from t1;
grp
2,3,4,5
select a,c,(select group_concat(c order by a) from t2 where a=t1.a) as grp from t1 order by grp;
a	c	grp
3	5	3,3
2	3	4
2	4	4
1	2	5
drop table t1,t2;
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;
GROUP_CONCAT(t1.a*t2.a ORDER BY t2.a)
1,2
2,4
DROP TABLE t1, t2;
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%';
names
John||Anna||Bill
SELECT GROUP_CONCAT(a SEPARATOR '###') AS names FROM t1 
HAVING LEFT(names, 1) ='J';
names
John###Anna###Bill
DROP TABLE t1;
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;
GROUP_CONCAT(b ORDER BY b)
First Row
Second Row
DROP TABLE t1;
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;
A_ID	B_DESC
1	A,B
2	NULL
3	F
DROP TABLE t1;
DROP TABLE t2;
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;
group_concat(b)
bb,ccc,a,bb,ccc
BB,CCC,A,BB,CCC
select group_concat(distinct b) from t1 group by a;
group_concat(distinct b)
bb,ccc,a
BB,CCC,A
select group_concat(b order by b) from t1 group by a;
group_concat(b order by b)
a,bb,bb,ccc,ccc
A,BB,BB,CCC,CCC
select group_concat(distinct b order by b) from t1 group by a;
group_concat(distinct b order by b)
a,bb,ccc
A,BB,CCC
set local group_concat_max_len=4;
select group_concat(b) from t1 group by a;
group_concat(b)
bb,c
BB,C
Warnings:
Warning	1260	Row 2 was cut by GROUP_CONCAT()
Warning	1260	Row 4 was cut by GROUP_CONCAT()
select group_concat(distinct b) from t1 group by a;
group_concat(distinct b)
bb,c
BB,C
Warnings:
Warning	1260	Row 2 was cut by GROUP_CONCAT()
Warning	1260	Row 4 was cut by GROUP_CONCAT()
select group_concat(b order by b) from t1 group by a;
group_concat(b order by b)
a,bb
A,BB
Warnings:
Warning	1260	Row 3 was cut by GROUP_CONCAT()
Warning	1260	Row 6 was cut by GROUP_CONCAT()
select group_concat(distinct b order by b) from t1 group by a;
group_concat(distinct b order by b)
a,bb
A,BB
Warnings:
Warning	1260	Row 3 was cut by GROUP_CONCAT()
Warning	1260	Row 6 was cut by GROUP_CONCAT()
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;
group_concat(b)
bb,ccc,a,bb,ccc,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001
BB,CCC,A,BB,CCC,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001
select group_concat(distinct b) from t1 group by a;
group_concat(distinct b)
bb,ccc,a,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001
BB,CCC,A,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001
select group_concat(b order by b) from t1 group by a;
group_concat(b order by b)
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,a,bb,bb,ccc,ccc
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,A,BB,BB,CCC,CCC
select group_concat(distinct b order by b) from t1 group by a;
group_concat(distinct b order by b)
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,a,bb,ccc
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,A,BB,CCC
set local group_concat_max_len=400;
select group_concat(b) from t1 group by a;
group_concat(b)
bb,ccc,a,bb,ccc,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111
BB,CCC,A,BB,CCC,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111
Warnings:
Warning	1260	Row 7 was cut by GROUP_CONCAT()
Warning	1260	Row 14 was cut by GROUP_CONCAT()
select group_concat(distinct b) from t1 group by a;
group_concat(distinct b)
bb,ccc,a,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
BB,CCC,A,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
Warnings:
Warning	1260	Row 5 was cut by GROUP_CONCAT()
Warning	1260	Row 10 was cut by GROUP_CONCAT()
select group_concat(b order by b) from t1 group by a;
group_concat(b order by b)
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111
Warnings:
Warning	1260	Row 2 was cut by GROUP_CONCAT()
Warning	1260	Row 4 was cut by GROUP_CONCAT()
select group_concat(distinct b order by b) from t1 group by a;
group_concat(distinct b order by b)
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111
Warnings:
Warning	1260	Row 2 was cut by GROUP_CONCAT()
Warning	1260	Row 4 was cut by GROUP_CONCAT()
drop table t1;
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;
collation(a)
cp1250_general_ci
select collation(group_concat(a)) from t1;
collation(group_concat(a))
cp1250_general_ci
create table t2 select group_concat(a) as a from t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` varchar(400) CHARACTER SET cp1250 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select collation(group_concat(a,_koi8r'test')) from t1;
collation(group_concat(a,_koi8r'test'))
cp1250_general_ci
select collation(group_concat(a,_koi8r 0xC1C2)) from t1;
ERROR HY000: Illegal mix of collations (cp1250_general_ci,IMPLICIT) and (koi8r_general_ci,COERCIBLE) for operation 'group_concat('
select collation(group_concat(a,b)) from t1;
ERROR HY000: Illegal mix of collations (cp1250_general_ci,IMPLICIT) and (koi8r_general_ci,IMPLICIT) for operation 'group_concat('
drop table t1;
drop table t2;
CREATE TABLE t1 (a CHAR(10) CHARACTER SET cp850);
INSERT INTO t1 VALUES ('ภ');
SELECT a FROM t1;
a
ภ
SELECT GROUP_CONCAT(a) FROM t1;
GROUP_CONCAT(a)
ภ
DROP TABLE t1;
CREATE TABLE t1 (id int);
SELECT GROUP_CONCAT(id) AS gc FROM t1 HAVING gc IS NULL;
gc
NULL
DROP TABLE t1;
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;
x	(select group_concat(x) from t2)
1	1,1
2	2,2
drop table t2;
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;
d	a	b
1	1	3
2	1	4
3	1	2
5	1	1
6	1	2
4	2	7
7	2	3
8	2	3
explain select a, group_concat(b) from t1 group by a with rollup;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using filesort
select a, group_concat(b) from t1 group by a with rollup;
a	group_concat(b)
1	3,4,2,1,2
2	7,3,3
NULL	3,4,2,1,2,7,3,3
select a, group_concat(distinct b) from t1 group by a with rollup;
a	group_concat(distinct b)
1	3,4,2,1
2	7,3
NULL	3,4,2,1,7
select a, group_concat(b order by b) from t1 group by a with rollup;
a	group_concat(b order by b)
1	1,2,2,3,4
2	3,3,7
NULL	1,2,2,3,3,3,4,7
select a, group_concat(distinct b order by b) from t1 group by a with rollup;
a	group_concat(distinct b order by b)
1	1,2,3,4
2	3,7
NULL	1,2,3,4,7
drop table t1;
create table t1 (a char(3), b char(20), primary key (a, b));
insert into t1 values ('ABW', 'Dutch'), ('ABW', 'English');
select group_concat(a) from t1 group by b;
group_concat(a)
ABW
ABW
drop table t1;
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;
COUNT(*)	GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')
2	test
INSERT INTO t2 SET somename = 'test2';
SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')
FROM t1 JOIN t2 ON t1.bID = t2.bID;
COUNT(*)	GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')
2	test
DELETE FROM t2 WHERE somename = 'test2';
SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')
FROM t1 JOIN t2 ON t1.bID = t2.bID;
COUNT(*)	GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')
2	test
DROP TABLE t1,t2;
select * from (select group_concat('c') from DUAL) t;
group_concat('c')
c
create table t1 ( a int not null default 0);
select * from (select group_concat(a) from t1) t2;
group_concat(a)
NULL
select group_concat('x') UNION ALL select 1;
group_concat('x')
x
1
drop table t1;
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;
GROUP_CONCAT(a)
,,x,y,z,
SELECT GROUP_CONCAT(a ORDER BY a) FROM t1;
GROUP_CONCAT(a ORDER BY a)
,,,x,y,z
SELECT GROUP_CONCAT(a) FROM t1 GROUP BY id;
GROUP_CONCAT(a)
,y
,x
z,
SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY id;
GROUP_CONCAT(a ORDER BY a)
,y
,x
,z
DROP TABLE t1;
create table t1(f1 int);
insert into t1 values(1),(2),(3);
select f1, group_concat(f1+1) from t1 group by f1 with rollup;
f1	group_concat(f1+1)
1	2
2	3
3	4
NULL	2,3,4
select count(distinct (f1+1)) from t1 group by f1 with rollup;
count(distinct (f1+1))
1
1
1
3
drop table t1;
create table t1 (f1 int unsigned, f2 varchar(255));
insert into t1 values (1,repeat('a',255)),(2,repeat('b',255));
select f2,group_concat(f1) from t1 group by f2;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def	test	t1	t1	f2	f2	253	255	255	Y	0	0	8
def					group_concat(f1)	253	400	1	Y	0	0	8
f2	group_concat(f1)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	1
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb	2
drop table t1;
set names latin1;
create table t1 (a char, b char);
insert into t1 values ('a', 'a'), ('a', 'b'), ('b', 'a'), ('b', 'b');
create table t2 select group_concat(b) as a from t1 where a = 'a';
create table t3 (select group_concat(a) as a from t1 where a = 'a') union
(select group_concat(b) as a from t1 where a = 'b');
select charset(a) from t2;
charset(a)
latin1
select charset(a) from t3;
charset(a)
latin1
latin1
drop table t1, t2, t3;
set names default;
create table t1 (c1 varchar(10), c2 int);
select charset(group_concat(c1 order by c2)) from t1;
charset(group_concat(c1 order by c2))
latin1
drop table t1;
CREATE TABLE t1 (a INT(10), b LONGTEXT, PRIMARY KEY (a));
SET GROUP_CONCAT_MAX_LEN = 20000000;
INSERT INTO t1 VALUES (1,REPEAT(CONCAT('A',CAST(CHAR(0) AS BINARY),'B'), 40000));
INSERT INTO t1 SELECT a + 1, b FROM t1;
SELECT a, CHAR_LENGTH(b) FROM t1;
a	CHAR_LENGTH(b)
1	120000
2	120000
SELECT CHAR_LENGTH( GROUP_CONCAT(b) ) FROM t1;
CHAR_LENGTH( GROUP_CONCAT(b) )
240001
SET GROUP_CONCAT_MAX_LEN = 1024;
DROP TABLE t1;
CREATE TABLE t1 (a int, b int);
INSERT INTO t1 VALUES (2,1), (1,2), (2,2), (1,3);
SELECT GROUP_CONCAT(a), x 
FROM (SELECT a, GROUP_CONCAT(b) x FROM t1 GROUP BY a) AS s
GROUP BY x;
GROUP_CONCAT(a)	x
2	1,2
1	2,3
DROP TABLE t1;
set names utf8;
create table t1
(
x text character set utf8 not null,
y integer not null
);
insert into t1 values (repeat('a', 1022), 0), (repeat(_utf8 0xc3b7, 4), 0);
set group_concat_max_len= 1022 + 10;
select @x:=group_concat(x) from t1 group by y;
select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);
@@group_concat_max_len	length(@x)	char_length(@x)	right(@x,12)	right(HEX(@x),12)
1032	1031	1027	aaaaaaa,รทรทรทรท	C3B7C3B7C3B7
set group_concat_max_len= 1022 + 9;
select @x:=group_concat(x) from t1 group by y;
select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);
@@group_concat_max_len	length(@x)	char_length(@x)	right(@x,12)	right(HEX(@x),12)
1031	1031	1027	aaaaaaa,รทรทรทรท	C3B7C3B7C3B7
set group_concat_max_len= 1022 + 8;
select @x:=group_concat(x) from t1 group by y;
select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);
@@group_concat_max_len	length(@x)	char_length(@x)	right(@x,12)	right(HEX(@x),12)
1030	1029	1026	aaaaaaaa,รทรทรท	C3B7C3B7C3B7
set group_concat_max_len= 1022 + 7;
select @x:=group_concat(x) from t1 group by y;
select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);
@@group_concat_max_len	length(@x)	char_length(@x)	right(@x,12)	right(HEX(@x),12)
1029	1029	1026	aaaaaaaa,รทรทรท	C3B7C3B7C3B7
set group_concat_max_len= 1022 + 6;
select @x:=group_concat(x) from t1 group by y;
select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);
@@group_concat_max_len	length(@x)	char_length(@x)	right(@x,12)	right(HEX(@x),12)
1028	1027	1025	aaaaaaaaa,รทรท	612CC3B7C3B7
set group_concat_max_len= 1022 + 5;
select @x:=group_concat(x) from t1 group by y;
select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);
@@group_concat_max_len	length(@x)	char_length(@x)	right(@x,12)	right(HEX(@x),12)
1027	1027	1025	aaaaaaaaa,รทรท	612CC3B7C3B7
set group_concat_max_len= 1022 + 4;
select @x:=group_concat(x) from t1 group by y;
select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);
@@group_concat_max_len	length(@x)	char_length(@x)	right(@x,12)	right(HEX(@x),12)
1026	1025	1024	aaaaaaaaaa,รท	6161612CC3B7
set group_concat_max_len= 1022 + 3;
select @x:=group_concat(x) from t1 group by y;
select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);
@@group_concat_max_len	length(@x)	char_length(@x)	right(@x,12)	right(HEX(@x),12)
1025	1025	1024	aaaaaaaaaa,รท	6161612CC3B7
set group_concat_max_len= 1022 + 2;
select @x:=group_concat(x) from t1 group by y;
select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);
@@group_concat_max_len	length(@x)	char_length(@x)	right(@x,12)	right(HEX(@x),12)
1024	1023	1023	aaaaaaaaaaa,	61616161612C
set group_concat_max_len= 1022 + 1;
select @x:=group_concat(x) from t1 group by y;
select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);
@@group_concat_max_len	length(@x)	char_length(@x)	right(@x,12)	right(HEX(@x),12)
1023	1023	1023	aaaaaaaaaaa,	61616161612C
drop table t1;
set group_concat_max_len=1024;
set names latin1;
create table t1 (f1 int unsigned, f2 varchar(255));
insert into t1 values (1,repeat('a',255)),(2,repeat('b',255));
select f2,group_concat(f1) from t1 group by f2;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def	test	t1	t1	f2	f2	253	255	255	Y	0	0	8
def					group_concat(f1)	252	1024	1	Y	0	0	8
f2	group_concat(f1)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	1
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb	2
drop table t1;
CREATE TABLE t1(a TEXT, b CHAR(20));
INSERT INTO t1 VALUES ("one.1","one.1"),("two.2","two.2"),("one.3","one.3");
SELECT GROUP_CONCAT(DISTINCT UCASE(a)) FROM t1;
GROUP_CONCAT(DISTINCT UCASE(a))
ONE.1,TWO.2,ONE.3
SELECT GROUP_CONCAT(DISTINCT UCASE(b)) FROM t1;
GROUP_CONCAT(DISTINCT UCASE(b))
ONE.1,TWO.2,ONE.3
DROP TABLE t1;
CREATE TABLE t1( a VARCHAR( 10 ), b INT );
INSERT INTO t1 VALUES ( repeat( 'a', 10 ), 1), 
( repeat( 'b', 10 ), 2);
SET group_concat_max_len = 20;
SELECT GROUP_CONCAT( a ) FROM t1;
GROUP_CONCAT( a )
aaaaaaaaaa,bbbbbbbbb
Warnings:
Warning	1260	Row 2 was cut by GROUP_CONCAT()
SELECT GROUP_CONCAT( DISTINCT a ) FROM t1;
GROUP_CONCAT( DISTINCT a )
aaaaaaaaaa,bbbbbbbbb
Warnings:
Warning	1260	Row 2 was cut by GROUP_CONCAT()
SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1;
GROUP_CONCAT( a ORDER BY b )
aaaaaaaaaa,bbbbbbbbb
Warnings:
Warning	1260	Row 2 was cut by GROUP_CONCAT()
SELECT GROUP_CONCAT( DISTINCT a ORDER BY b ) FROM t1;
GROUP_CONCAT( DISTINCT a ORDER BY b )
aaaaaaaaaa,bbbbbbbbb
Warnings:
Warning	1260	Row 2 was cut by GROUP_CONCAT()
SET group_concat_max_len = DEFAULT;
DROP TABLE t1;
SET group_concat_max_len= 65535;
CREATE TABLE t1( a TEXT, b INTEGER );
INSERT INTO t1 VALUES ( 'a', 0 ), ( 'b', 1 );
SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1;
GROUP_CONCAT( a ORDER BY b )
a,b
SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1;
GROUP_CONCAT(DISTINCT a ORDER BY b)
a,b
SELECT GROUP_CONCAT(DISTINCT a) FROM t1;
GROUP_CONCAT(DISTINCT a)
a,b
SET group_concat_max_len= 10;
SELECT GROUP_CONCAT(a ORDER BY b) FROM t1;
GROUP_CONCAT(a ORDER BY b)
a,b
SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1;
GROUP_CONCAT(DISTINCT a ORDER BY b)
a,b
SELECT GROUP_CONCAT(DISTINCT a) FROM t1;
GROUP_CONCAT(DISTINCT a)
a,b
SET group_concat_max_len= 65535;
CREATE TABLE t2( a TEXT );
INSERT INTO t2 VALUES( REPEAT( 'a', 5000 ) );
INSERT INTO t2 VALUES( REPEAT( 'b', 5000 ) );
INSERT INTO t2 VALUES( REPEAT( 'a', 5000 ) );
SELECT LENGTH( GROUP_CONCAT( DISTINCT a ) ) FROM t2;
LENGTH( GROUP_CONCAT( DISTINCT a ) )
10001
CREATE TABLE t3( a TEXT, b INT  );
INSERT INTO t3 VALUES( REPEAT( 'a', 65534 ), 1 );
INSERT INTO t3 VALUES( REPEAT( 'a', 65535 ), 2 );
INSERT INTO t3 VALUES( REPEAT( 'a', 65536 ), 3 );
Warnings:
Warning	1265	Data truncated for column 'a' at row 1
SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 1;
LENGTH( GROUP_CONCAT( a ) )
65534
SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 2;
LENGTH( GROUP_CONCAT( a ) )
65535
SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 3;
LENGTH( GROUP_CONCAT( a ) )
65535
SET group_concat_max_len= DEFAULT;
DROP TABLE t1, t2, t3;
set names latin1;
create table t1 (id int, name varchar(20)) DEFAULT CHARSET=utf8;
insert into t1 (id, name) values (1, "๓ra");
insert into t1 (id, name) values (2, "๓ra");
select b.id, group_concat(b.name) from t1 a, t1 b group by b.id;
id	group_concat(b.name)
1	๓ra,๓ra
2	๓ra,๓ra
drop table t1;
create table t1(a bit not null);
insert into t1 values (), (), ();
Warnings:
Warning	1364	Field 'a' doesn't have a default value
select group_concat(distinct a) from t1;
group_concat(distinct a)
0
select group_concat(distinct a order by a) from t1;
group_concat(distinct a order by a)
0
drop table t1;
create table t1(a bit(2) not null);
insert into t1 values (1), (0), (0), (3), (1);
select group_concat(distinct a) from t1;
group_concat(distinct a)
1,0,3
select group_concat(distinct a order by a) from t1;
group_concat(distinct a order by a)
0,1,3
select group_concat(distinct a order by a desc) from t1;
group_concat(distinct a order by a desc)
3,1,0
drop table t1;
create table t1(a bit(2), b varchar(10), c bit);
insert into t1 values (1, 'a', 0), (0, 'b', 1), (0, 'c', 0), (3, 'd', 1),
(1, 'e', 1), (3, 'f', 1), (0, 'g', 1);
select group_concat(distinct a, c) from t1;
group_concat(distinct a, c)
10,01,00,31,11
select group_concat(distinct a, c order by a) from t1;
group_concat(distinct a, c order by a)
00,01,11,10,31
select group_concat(distinct a, c) from t1;
group_concat(distinct a, c)
10,01,00,31,11
select group_concat(distinct a, c order by a, c) from t1;
group_concat(distinct a, c order by a, c)
00,01,10,11,31
select group_concat(distinct a, c order by a desc, c desc) from t1;
group_concat(distinct a, c order by a desc, c desc)
31,11,10,01,00
drop table t1;
create table t1 (f1 char(20));
insert into t1 values (''),('');
select group_concat(distinct f1) from t1;
group_concat(distinct f1)

select group_concat(f1) from t1;
group_concat(f1)
,
drop table t1;
CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 VALUES (1, 1), (2, 2), (2, 3);
SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1;
GROUP_CONCAT(DISTINCT a ORDER BY b)
1,2
SELECT GROUP_CONCAT(DISTINCT a ORDER BY b DESC) FROM t1;
GROUP_CONCAT(DISTINCT a ORDER BY b DESC)
2,1
SELECT GROUP_CONCAT(DISTINCT a) FROM t1;
GROUP_CONCAT(DISTINCT a)
1,2
SELECT GROUP_CONCAT(DISTINCT a + 1 ORDER BY 3 - b) FROM t1;
GROUP_CONCAT(DISTINCT a + 1 ORDER BY 3 - b)
3,2
SELECT GROUP_CONCAT(DISTINCT a + 1 ORDER BY b) FROM t1;
GROUP_CONCAT(DISTINCT a + 1 ORDER BY b)
2,3
SELECT GROUP_CONCAT(a ORDER BY 3 - b) FROM t1;
GROUP_CONCAT(a ORDER BY 3 - b)
2,2,1
CREATE TABLE t2 (a INT, b INT, c INT, d INT);
INSERT INTO t2 VALUES (1,1, 1,1), (1,1, 2,2), (1,2, 2,1), (2,1, 1,2);
SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY c, d) FROM t2;
GROUP_CONCAT(DISTINCT a, b ORDER BY c, d)
11,21,12
SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY d, c) FROM t2;
GROUP_CONCAT(DISTINCT a, b ORDER BY d, c)
11,12,21
CREATE TABLE t3 (a INT, b INT, c INT);
INSERT INTO t3 VALUES (1, 1, 1), (2, 1, 2), (3, 2, 1);
SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY b, c) FROM t3;
GROUP_CONCAT(DISTINCT a, b ORDER BY b, c)
11,21,32
SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY c, b) FROM t3;
GROUP_CONCAT(DISTINCT a, b ORDER BY c, b)
11,32,21
SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY a, b) FROM t1;
GROUP_CONCAT(DISTINCT a, b ORDER BY a, b)
11,22,23
SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY a, b) FROM t1;
GROUP_CONCAT(DISTINCT b, a ORDER BY a, b)
11,22,32
SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY b, a) FROM t1;
GROUP_CONCAT(DISTINCT a, b ORDER BY b, a)
11,22,23
SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY a, b) FROM t1;
GROUP_CONCAT(DISTINCT b, a ORDER BY a, b)
11,22,32
SELECT GROUP_CONCAT(DISTINCT a ORDER BY a, b) FROM t1;
GROUP_CONCAT(DISTINCT a ORDER BY a, b)
1,2
SELECT GROUP_CONCAT(DISTINCT b ORDER BY b, a) FROM t1;
GROUP_CONCAT(DISTINCT b ORDER BY b, a)
1,2,3
SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY a) FROM t1;
GROUP_CONCAT(DISTINCT a, b ORDER BY a)
11,23,22
SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY b) FROM t1;
GROUP_CONCAT(DISTINCT b, a ORDER BY b)
11,22,32
DROP TABLE t1, t2, t3;
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES (),();
SELECT s1.d1 FROM
(
SELECT
t1.a as d1,
GROUP_CONCAT(DISTINCT t1.a) AS d2
FROM
t1 AS t1,
t1 AS t2
GROUP BY 1
) AS s1;
d1
NULL
DROP TABLE t1;
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT);
INSERT INTO t1 VALUES(1);
SELECT GROUP_CONCAT(DISTINCT t2.a) FROM t1 LEFT JOIN t2 ON t2.a = t1.a GROUP BY t1.a;
GROUP_CONCAT(DISTINCT t2.a)
NULL
DROP TABLE t1, t2;
CREATE TABLE t1 (a INT, KEY(a));
CREATE TABLE t2 (b INT);
INSERT INTO t1 VALUES (NULL), (8), (2);
INSERT INTO t2 VALUES (4), (10);
SELECT 1 FROM t1 WHERE t1.a NOT IN
(
SELECT GROUP_CONCAT(DISTINCT t1.a)
FROM  t1 WHERE t1.a IN   
(
SELECT b FROM t2
) 
AND NOT t1.a >= (SELECT t1.a FROM t1 LIMIT 1)
GROUP BY t1.a
);
1
1
1
1
DROP TABLE t1, t2;
CREATE TABLE t1 (f1 INT);
INSERT INTO t1 VALUES (),();
EXPLAIN EXTENDED SELECT 1 FROM
(SELECT DISTINCT GROUP_CONCAT(td.f1) FROM t1,t1 AS td GROUP BY td.f1) AS d,t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	100.00	Using join buffer (flat, BNL join)
2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary; Using filesort
2	DERIVED	td	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (flat, BNL join)
Warnings:
Note	1003	select 1 AS `1` from (select distinct group_concat(`test`.`td`.`f1` separator ',') AS `GROUP_CONCAT(td.f1)` from `test`.`t1` join `test`.`t1` `td` group by `test`.`td`.`f1`) `d` join `test`.`t1`
SELECT 1 FROM
(SELECT DISTINCT GROUP_CONCAT(td.f1) FROM t1,t1 AS td GROUP BY td.f1) AS d,t1;
1
1
1
DROP TABLE t1;
End of 5.0 tests
#
# Bug #52397: another crash with explain extended and group_concat
#
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (0), (0);
EXPLAIN EXTENDED SELECT 1 FROM
(SELECT GROUP_CONCAT(t1.a ORDER BY t1.a ASC) FROM 
t1 t2, t1 GROUP BY t1.a) AS d;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	100.00	
2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using temporary; Using filesort
2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (flat, BNL join)
Warnings:
Note	1003	select 1 AS `1` from (select group_concat(`test`.`t1`.`a` order by `test`.`t1`.`a` ASC separator ',') AS `GROUP_CONCAT(t1.a ORDER BY t1.a ASC)` from `test`.`t1` `t2` join `test`.`t1` group by `test`.`t1`.`a`) `d`
DROP TABLE t1;
End of 5.0 tests
#
# Bug #54476: crash when group_concat and 'with rollup' in prepared statements
# 
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1), (2);
PREPARE stmt FROM "SELECT GROUP_CONCAT(t1.a ORDER BY t1.a) FROM t1 JOIN t1 t2 GROUP BY t1.a WITH ROLLUP";
EXECUTE stmt;
GROUP_CONCAT(t1.a ORDER BY t1.a)
1,1
2,2
1,1,2,2
EXECUTE stmt;
GROUP_CONCAT(t1.a ORDER BY t1.a)
1,1
2,2
1,1,2,2
DEALLOCATE PREPARE stmt;
DROP TABLE t1;
#
# Bug#57194 group_concat cause crash and/or invalid memory reads with type errors
#
CREATE TABLE t1(f1 int);
INSERT INTO t1 values (0),(0);
SELECT POLYGON((SELECT 1 FROM (SELECT 1 IN (GROUP_CONCAT(t1.f1)) FROM t1, t1 t GROUP BY t.f1 ) d));
ERROR 22007: Illegal non geometric '(select 1 from (select (1 = group_concat(`test`.`t1`.`f1` separator ',')) AS `1 IN (GROUP_CONCAT(t1.f1))` from `test`.`t1` join `test`.`t1` `t` group by `test`.`t`.`f1`) `d`)' value found during parsing
DROP TABLE t1;
#
# Bug#58396 group_concat and explain extended are still crashy
#
CREATE TABLE t1(a INT);
EXPLAIN EXTENDED SELECT UPDATEXML('1', a, '1')
FROM t1 ORDER BY (SELECT GROUP_CONCAT(1) FROM t1);
ERROR HY000: Only constant XPATH queries are supported
SHOW WARNINGS;
Level	Code	Message
Error	1105	Only constant XPATH queries are supported
DROP TABLE t1;
End of 5.1 tests
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 (a VARCHAR(6), b INT);
CREATE TABLE t2 (a VARCHAR(6), b INT);
INSERT INTO t1 VALUES ('111111', 1);
INSERT INTO t1 VALUES ('222222', 2);
INSERT INTO t1 VALUES ('333333', 3);
INSERT INTO t1 VALUES ('444444', 4);
INSERT INTO t1 VALUES ('555555', 5);
SET group_concat_max_len = 5;
SET @old_sql_mode = @@sql_mode, @@sql_mode = 'traditional';
SELECT GROUP_CONCAT(a), b FROM t1 GROUP BY b LIMIT 3;
GROUP_CONCAT(a)	b
11111	1
22222	2
33333	3
Warnings:
Warning	1260	Row 1 was cut by GROUP_CONCAT()
Warning	1260	Row 2 was cut by GROUP_CONCAT()
Warning	1260	Row 3 was cut by GROUP_CONCAT()
INSERT INTO t2 SELECT GROUP_CONCAT(a), b FROM t1 GROUP BY b;
ERROR HY000: Row 1 was cut by GROUP_CONCAT()
UPDATE t1 SET a = '11111' WHERE b = 1;
UPDATE t1 SET a = '22222' WHERE b = 2;
INSERT INTO t2 SELECT GROUP_CONCAT(a), b FROM t1 GROUP BY b;
ERROR HY000: Row 3 was cut by GROUP_CONCAT()
SET group_concat_max_len = DEFAULT;
SET @@sql_mode = @old_sql_mode;
DROP TABLE t1, t2;
create table t1 (a char(1) character set utf8);
insert into t1 values ('a'),('b');
select 1 from t1 where a in (select group_concat(a) from t1);
1
drop table t1;
CREATE TABLE t1 (f1 VARCHAR(10)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('a'),('b');
CREATE TABLE t2 (f2 VARCHAR(10)) ENGINE=MyISAM;
INSERT INTO t2 VALUES ('c');
CREATE TABLE t3 (f3 VARCHAR(10)) ENGINE=MyISAM;
INSERT INTO t3 VALUES ('d'),('e');
SELECT GROUP_CONCAT( f2 ORDER BY ( f2 IN ( SELECT f1 FROM t1 WHERE f1 <= f2 ) ) ) AS field
FROM ( SELECT * FROM t2 ) AS sq2, t3
ORDER BY field;
field
c,c
drop table t3, t2, t1;
#
# MDEV-7821 - Server crashes in Item_func_group_concat::fix_fields on 2nd
#             execution of PS
#
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES(1),(2);
PREPARE stmt FROM "SELECT GROUP_CONCAT(t1a.a ORDER BY 1, t1a.a=0) FROM t1 AS t1a, t1 AS t1b GROUP BY t1a.a";
EXECUTE stmt;
GROUP_CONCAT(t1a.a ORDER BY 1, t1a.a=0)
1,1
2,2
EXECUTE stmt;
GROUP_CONCAT(t1a.a ORDER BY 1, t1a.a=0)
1,1
2,2
DROP TABLE t1;
#
#  WL#6098 Eliminate GROUP_CONCAT intermediate result limitation.
#  Bug#13387020 GROUP_CONCAT WITH ORDER BY RESULTS ARE TRUNCATED.
#
SET group_concat_max_len= 9999999;
CREATE TABLE t1 (f1 LONGTEXT , f2  INTEGER);
INSERT INTO t1 VALUES (REPEAT('a', 500000), 0), (REPEAT('b', 500000), 1), (REPEAT('c', 500000), 2);
SELECT LENGTH(GROUP_CONCAT(f1 ORDER BY f2)) FROM t1;
LENGTH(GROUP_CONCAT(f1 ORDER BY f2))
1500002
SELECT LENGTH(GROUP_CONCAT(DISTINCT f1 ORDER BY f1 DESC)) FROM t1;
LENGTH(GROUP_CONCAT(DISTINCT f1 ORDER BY f1 DESC))
1500002
SELECT SUBSTRING(GROUP_CONCAT(DISTINCT f1 ORDER BY f1 DESC), 1, 5) FROM t1;
SUBSTRING(GROUP_CONCAT(DISTINCT f1 ORDER BY f1 DESC), 1, 5)
ccccc
SELECT LENGTH(GROUP_CONCAT(DISTINCT f1)) FROM t1;
LENGTH(GROUP_CONCAT(DISTINCT f1))
1500002
SELECT LENGTH(GROUP_CONCAT(UPPER(f1) ORDER BY f2)) FROM t1;
LENGTH(GROUP_CONCAT(UPPER(f1) ORDER BY f2))
1500002
SELECT LENGTH(GROUP_CONCAT(DISTINCT UPPER(f1) ORDER BY f1)) FROM t1;
LENGTH(GROUP_CONCAT(DISTINCT UPPER(f1) ORDER BY f1))
1500002
SELECT SUBSTRING(GROUP_CONCAT(DISTINCT UPPER(f1) ORDER BY f1), 1, 5) FROM t1;
SUBSTRING(GROUP_CONCAT(DISTINCT UPPER(f1) ORDER BY f1), 1, 5)
AAAAA
SELECT LENGTH(GROUP_CONCAT(DISTINCT UPPER(f1))) FROM t1;
LENGTH(GROUP_CONCAT(DISTINCT UPPER(f1)))
1500002
CREATE TABLE t2 SELECT GROUP_CONCAT(f1 order by f2) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `GROUP_CONCAT(f1 order by f2)` mediumtext DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 SELECT GROUP_CONCAT(UPPER(f1) ORDER BY f2) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `GROUP_CONCAT(UPPER(f1) ORDER BY f2)` mediumtext DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
SET group_concat_max_len= DEFAULT;
SELECT LENGTH(GROUP_CONCAT(f1 ORDER BY f2)) FROM t1;
LENGTH(GROUP_CONCAT(f1 ORDER BY f2))
1024
Warnings:
Warning	1260	Row 2 was cut by GROUP_CONCAT()
SET group_concat_max_len= 499999;
SELECT LENGTH(GROUP_CONCAT(f1 ORDER BY f2)) FROM t1 WHERE f2 = 0;
LENGTH(GROUP_CONCAT(f1 ORDER BY f2))
499999
Warnings:
Warning	1260	Row 1 was cut by GROUP_CONCAT()
SELECT LENGTH(GROUP_CONCAT(f1 ORDER BY f2)) FROM t1 GROUP BY f2;
LENGTH(GROUP_CONCAT(f1 ORDER BY f2))
499999
499999
499999
Warnings:
Warning	1260	Row 1 was cut by GROUP_CONCAT()
Warning	1260	Row 2 was cut by GROUP_CONCAT()
Warning	1260	Row 3 was cut by GROUP_CONCAT()
INSERT INTO t1 VALUES (REPEAT('a', 499999), 3), (REPEAT('b', 500000), 4);
SELECT LENGTH(GROUP_CONCAT(f1 ORDER BY f2)) FROM t1 GROUP BY f2;
LENGTH(GROUP_CONCAT(f1 ORDER BY f2))
499999
499999
499999
499999
499999
Warnings:
Warning	1260	Row 1 was cut by GROUP_CONCAT()
Warning	1260	Row 2 was cut by GROUP_CONCAT()
Warning	1260	Row 3 was cut by GROUP_CONCAT()
Warning	1260	Row 5 was cut by GROUP_CONCAT()
DROP TABLE t1;
SET group_concat_max_len= DEFAULT;
#
# Start of 10.2 tests
#
#
# MDEV-10124 Incorrect usage of CUBE/ROLLUP and ORDER BY with GROUP_CONCAT(a ORDER BY a)
#
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10),(20),(30);
SELECT a,GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP;
a	GROUP_CONCAT(a ORDER BY a)
10	10
20	20
30	30
NULL	10,20,30
CREATE VIEW v1 AS
SELECT a,GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP;
SELECT * FROM v1;
a	GROUP_CONCAT(a ORDER BY a)
10	10
20	20
30	30
NULL	10,20,30
DROP VIEW v1;
SELECT a,GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP HAVING GROUP_CONCAT(a ORDER BY a)='10,20,30';
a	GROUP_CONCAT(a ORDER BY a)
NULL	10,20,30
CREATE VIEW v1 AS
SELECT a,GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP HAVING GROUP_CONCAT(a ORDER BY a)='10,20,30';
SELECT * FROM v1;
a	GROUP_CONCAT(a ORDER BY a)
NULL	10,20,30
DROP VIEW v1;
SELECT * FROM (SELECT a,GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP HAVING GROUP_CONCAT(a ORDER BY a)='10,20,30') t1;
a	GROUP_CONCAT(a ORDER BY a)
NULL	10,20,30
CREATE VIEW v1 AS
SELECT * FROM (SELECT a,GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP HAVING GROUP_CONCAT(a ORDER BY a)='10,20,30') t1;
SELECT * FROM v1;
a	GROUP_CONCAT(a ORDER BY a)
NULL	10,20,30
DROP VIEW v1;
SELECT (SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP HAVING GROUP_CONCAT(a ORDER BY a)='10,20,30');
(SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP HAVING GROUP_CONCAT(a ORDER BY a)='10,20,30')
10,20,30
CREATE VIEW v1 AS
SELECT (SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY a WITH ROLLUP HAVING GROUP_CONCAT(a ORDER BY a)='10,20,30');
SELECT * FROM v1;
Name_exp_1
10,20,30
DROP VIEW v1;
DROP TABLE t1;
#
# End of 10.2 tests
#