func_gconcat.result 34.6 KB
Newer Older
1
drop table if exists t1, t2;
2 3 4 5 6 7 8 9 10 11 12 13 14
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
15 16
2	b,c
3	E,C,D,d,d,D
17
explain extended select grp,group_concat(c) from t1 group by grp;
18 19
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
20
Warnings:
unknown's avatar
unknown committed
21
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`
22 23 24
select grp,group_concat(a,c) from t1 group by grp;
grp	group_concat(a,c)
1	1a
25 26
2	2b,3c
3	4E,5C,6D,7d,8d,9D
27 28 29
select grp,group_concat("(",a,":",c,")") from t1 group by grp;
grp	group_concat("(",a,":",c,")")
1	(1:a)
30 31
2	(2:b),(3:c)
3	(4:E),(5:C),(6:D),(7:d),(8:d),(9:D)
32 33 34 35 36 37 38 39 40 41 42 43 44
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
45 46
2	b,c
3	C,D,d,d,D,E
47 48 49
select grp,group_concat(c order by c desc) from t1 group by grp;
grp	group_concat(c order by c desc)
1	a
50 51
2	c,b
3	E,D,d,d,D,C
52 53 54
select grp,group_concat(d order by a) from t1 group by grp;
grp	group_concat(d order by a)
1	a
55 56
2	a,b
3	a,b,b,d,d,c
57 58 59
select grp,group_concat(d order by a desc) from t1 group by grp;
grp	group_concat(d order by a desc)
1	a
60 61
2	b,a
3	c,d,d,b,b,a
unknown's avatar
unknown committed
62 63
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)
64
1	1
65 66
2	2,3
3	4,5,6,7,8,9
unknown's avatar
unknown committed
67 68 69 70 71
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
72 73 74
select grp,group_concat(c order by 1) from t1 group by grp;
grp	group_concat(c order by 1)
1	a
75 76
2	b,c
3	C,D,d,d,D,E
77 78 79
select grp,group_concat(distinct c order by c) from t1 group by grp;
grp	group_concat(distinct c order by c)
1	a
80 81
2	b,c
3	C,D,E
82 83 84
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
85 86
2	c,b
3	E,D,C
87
explain extended select grp,group_concat(distinct c order by c desc) from t1 group by grp;
88 89
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
90
Warnings:
unknown's avatar
unknown committed
91
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`
92 93 94 95 96 97 98 99 100 101 102 103 104 105 106
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
107
explain extended select grp,group_concat(distinct c order by c separator ",") from t1 group by grp;
108 109
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
110
Warnings:
unknown's avatar
unknown committed
111
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`
112 113 114 115 116 117 118 119
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
120 121
2	c,b
3	D,d,d,D,C,E
122 123 124 125 126 127 128 129 130 131 132 133 134 135 136
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
unknown's avatar
SCRUM  
unknown committed
137
drop table t1;
138
create table t1 (grp int, c char(10));
unknown's avatar
unknown committed
139
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);
140 141
select grp,group_concat(c order by c) from t1 group by grp;
grp	group_concat(c order by c)
unknown's avatar
SCRUM  
unknown committed
142 143
1	NULL
2	b
144
3	D,D,E
unknown's avatar
SCRUM  
unknown committed
145 146
4	
5	NULL
147
set group_concat_max_len = 4;
148 149
select grp,group_concat(c) from t1 group by grp;
grp	group_concat(c)
unknown's avatar
SCRUM  
unknown committed
150 151
1	NULL
2	b
152
3	D,D,
unknown's avatar
SCRUM  
unknown committed
153 154
4	
5	NULL
155 156
Warnings:
Warning	1260	1 line(s) were cut by GROUP_CONCAT()
157 158
show warnings;
Level	Code	Message
159
Warning	1260	1 line(s) were cut by GROUP_CONCAT()
unknown's avatar
unknown committed
160
set group_concat_max_len = 1024;
unknown's avatar
unknown committed
161
select group_concat(sum(c)) from t1 group by grp;
162
ERROR HY000: Invalid use of group function
163
select grp,group_concat(c order by 2) from t1 group by grp;
unknown's avatar
unknown committed
164
ERROR 42S22: Unknown column '2' in 'order clause'
unknown's avatar
unknown committed
165
drop table t1;
166 167 168 169
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);
unknown's avatar
unknown committed
170
select REQ_ID, Group_Concat(URL) as URL from t1, t2 where
171 172 173 174
t2.URL_ID = t1.URL_ID group by REQ_ID;
REQ_ID	URL
1	X
5	X,X,X
unknown's avatar
BUG  
unknown committed
175
select REQ_ID, Group_Concat(URL) as URL, Min(t1.URL_ID) urll,
176
Max(t1.URL_ID) urlg from t1, t2 where t2.URL_ID = t1.URL_ID group by REQ_ID;
unknown's avatar
BUG  
unknown committed
177 178 179
REQ_ID	URL	urll	urlg
1	X	4	4
5	X,X,X	4	5
180 181
drop table t1;
drop table t2;
182 183 184 185 186 187 188 189 190
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;
unknown's avatar
BUG  
unknown committed
191 192 193 194 195 196 197 198 199
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));
unknown's avatar
unknown committed
200 201 202 203 204 205 206 207 208 209 210 211 212
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;
unknown's avatar
BUG  
unknown committed
213
bar
unknown's avatar
unknown committed
214
test1
unknown's avatar
BUG  
unknown committed
215
drop table t1;
216 217 218 219 220 221 222 223 224 225 226
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;
unknown's avatar
unknown committed
227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260
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);
unknown's avatar
unknown committed
261
select distinct s1 from t1 order by s2,s1;
unknown's avatar
unknown committed
262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291
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,c
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
292 293 294 295 296 297 298 299 300 301 302 303 304 305 306
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
307 308 309 310 311 312
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
unknown's avatar
unknown committed
313 314 315 316 317 318 319
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;
320 321 322 323
CREATE TABLE t1 ( a int );
CREATE TABLE t2 ( a int );
INSERT INTO t1 VALUES (1), (2);
INSERT INTO t2 VALUES (1), (2);
unknown's avatar
unknown committed
324 325 326 327
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
328
DROP TABLE t1, t2;
unknown's avatar
a fix  
unknown committed
329 330 331 332 333 334 335 336 337 338 339
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;
unknown's avatar
unknown committed
340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357
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;
358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448
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	2 line(s) were 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	2 line(s) were 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	2 line(s) were 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	2 line(s) were 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	2 line(s) were 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	2 line(s) were 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	2 line(s) were 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	2 line(s) were cut by GROUP_CONCAT()
drop table t1;
449 450 451 452 453 454 455 456 457 458 459 460 461
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` (
462
  `a` varchar(400) CHARACTER SET cp1250 DEFAULT NULL
463 464 465 466 467 468 469 470 471 472
) 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;
unknown's avatar
unknown committed
473 474 475 476 477 478 479 480 481
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;
unknown's avatar
unknown committed
482 483 484 485 486
CREATE TABLE t1 (id int);
SELECT GROUP_CONCAT(id) AS gc FROM t1 HAVING gc IS NULL;
gc
NULL
DROP TABLE t1;
487 488 489 490
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)
491 492
1	1,1
2	2,2
493 494
drop table t2;
create table t1 (d int not null auto_increment,primary key(d), a int, b int, c int);
495
insert into t1(a,b) values (1,3), (1,4), (1,2), (2,7), (1,1), (1,2), (2,3), (2,3);
496 497 498 499 500 501 502 503 504 505 506 507 508
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
509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529
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;
530 531 532 533 534 535 536
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;
537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567
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;
568 569
select * from (select group_concat('c') from DUAL) t;
group_concat('c')
570
c
571 572 573 574 575 576
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')
577
x
578 579
1
drop table t1;
580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599
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;
600 601 602 603 604 605 606 607 608 609 610 611 612 613 614
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;
unknown's avatar
unknown committed
615 616 617 618 619 620 621 622 623 624
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	128	0	63
f2	group_concat(f1)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	1
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb	2
drop table t1;
625 626 627 628 629 630 631 632 633 634 635 636 637 638 639
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;
640 641 642 643 644
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;
645 646 647 648 649 650 651 652 653 654 655 656 657
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;
658 659 660 661 662 663 664 665 666
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;
667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726
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;
727 728 729 730 731
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
732
def					group_concat(f1)	252	1024	1	Y	128	0	63
733 734 735
f2	group_concat(f1)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	1
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb	2
736
drop table t1;
unknown's avatar
unknown committed
737 738 739 740 741 742 743 744 745 746
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;
End of 5.0 tests