~drizzle-trunk/drizzle/development

1 by brian
clean slate
1
drop table if exists t1, t2;
2
create table t1 (grp int, a bigint unsigned, c char(10) not null, d char(10) not null);
3
insert into t1 values (1,1,"a","a");
4
insert into t1 values (2,2,"b","a");
5
insert into t1 values (2,3,"c","b");
6
insert into t1 values (3,4,"E","a");
7
insert into t1 values (3,5,"C","b");
8
insert into t1 values (3,6,"D","b");
9
insert into t1 values (3,7,"d","d");
10
insert into t1 values (3,8,"d","d");
11
insert into t1 values (3,9,"D","c");
12
select grp,group_concat(c) from t1 group by grp;
13
grp	group_concat(c)
14
1	a
15
2	b,c
16
3	E,C,D,d,d,D
17
explain extended select grp,group_concat(c) from t1 group by grp;
18
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
19
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	9	100.00	Using filesort
20
Warnings:
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
select grp,group_concat(a,c) from t1 group by grp;
23
grp	group_concat(a,c)
24
1	1a
25
2	2b,3c
26
3	4E,5C,6D,7d,8d,9D
27
select grp,group_concat("(",a,":",c,")") from t1 group by grp;
28
grp	group_concat("(",a,":",c,")")
29
1	(1:a)
30
2	(2:b),(3:c)
31
3	(4:E),(5:C),(6:D),(7:d),(8:d),(9:D)
32
select grp,group_concat(c separator ",") from t1 group by grp;
33
grp	group_concat(c separator ",")
34
1	a
35
2	b,c
36
3	E,C,D,d,d,D
37
select grp,group_concat(c separator "---->") from t1 group by grp;
38
grp	group_concat(c separator "---->")
39
1	a
40
2	b---->c
41
3	E---->C---->D---->d---->d---->D
42
select grp,group_concat(c order by c) from t1 group by grp;
43
grp	group_concat(c order by c)
44
1	a
45
2	b,c
46
3	C,D,d,d,D,E
47
select grp,group_concat(c order by c desc) from t1 group by grp;
48
grp	group_concat(c order by c desc)
49
1	a
50
2	c,b
51
3	E,D,d,d,D,C
52
select grp,group_concat(d order by a) from t1 group by grp;
53
grp	group_concat(d order by a)
54
1	a
55
2	a,b
56
3	a,b,b,d,d,c
57
select grp,group_concat(d order by a desc) from t1 group by grp;
58
grp	group_concat(d order by a desc)
59
1	a
60
2	b,a
61
3	c,d,d,b,b,a
62
select grp,group_concat(a order by a,d+c-ascii(c)-a) from t1 group by grp;
63
grp	group_concat(a order by a,d+c-ascii(c)-a)
64
1	1
65
2	2,3
66
3	4,5,6,7,8,9
67
select grp,group_concat(a order by d+c-ascii(c),a) from t1 group by grp;
68
grp	group_concat(a order by d+c-ascii(c),a)
69
1	1
70
2	3,2
71
3	7,8,4,6,9,5
72
select grp,group_concat(c order by 1) from t1 group by grp;
73
grp	group_concat(c order by 1)
74
1	a
75
2	b,c
76
3	C,D,d,d,D,E
77
select grp,group_concat(distinct c order by c) from t1 group by grp;
78
grp	group_concat(distinct c order by c)
79
1	a
80
2	b,c
81
3	C,D,E
82
select grp,group_concat(distinct c order by c desc) from t1 group by grp;
83
grp	group_concat(distinct c order by c desc)
84
1	a
85
2	c,b
86
3	E,D,C
87
explain extended select grp,group_concat(distinct c order by c desc) from t1 group by grp;
88
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
89
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	9	100.00	Using filesort
90
Warnings:
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
select grp,group_concat(c order by c separator ",") from t1 group by grp;
93
grp	group_concat(c order by c separator ",")
94
1	a
95
2	b,c
96
3	C,D,d,d,D,E
97
select grp,group_concat(c order by c desc separator ",") from t1 group by grp;
98
grp	group_concat(c order by c desc separator ",")
99
1	a
100
2	c,b
101
3	E,D,d,d,D,C
102
select grp,group_concat(distinct c order by c separator ",") from t1 group by grp;
103
grp	group_concat(distinct c order by c separator ",")
104
1	a
105
2	b,c
106
3	C,D,E
107
explain extended select grp,group_concat(distinct c order by c separator ",") from t1 group by grp;
108
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
109
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	9	100.00	Using filesort
110
Warnings:
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
select grp,group_concat(distinct c order by c desc separator ",") from t1 group by grp;
113
grp	group_concat(distinct c order by c desc separator ",")
114
1	a
115
2	c,b
116
3	E,D,C
117
select grp,group_concat(c order by grp desc) from t1 group by grp order by grp;
118
grp	group_concat(c order by grp desc)
119
1	a
120
2	c,b
121
3	D,d,d,D,C,E
122
select grp, group_concat(a separator "")+0 from t1 group by grp;
123
grp	group_concat(a separator "")+0
124
1	1
125
2	23
126
3	456789
127
select grp, group_concat(a separator "")+0.0 from t1 group by grp;
128
grp	group_concat(a separator "")+0.0
129
1	1.0
130
2	23.0
131
3	456789.0
132
select grp, ROUND(group_concat(a separator "")) from t1 group by grp;
133
grp	ROUND(group_concat(a separator ""))
134
1	1
135
2	23
136
3	456789
137
drop table t1;
138
create table t1 (grp int, c char(10));
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
select grp,group_concat(c order by c) from t1 group by grp;
141
grp	group_concat(c order by c)
142
1	NULL
143
2	b
144
3	D,D,E
145
4	
146
5	NULL
147
set group_concat_max_len = 4;
148
select grp,group_concat(c) from t1 group by grp;
149
grp	group_concat(c)
150
1	NULL
151
2	b
152
3	D,D,
153
4	
154
5	NULL
155
Warnings:
156
Warning	1260	1 line(s) were cut by GROUP_CONCAT()
157
show warnings;
158
Level	Code	Message
159
Warning	1260	1 line(s) were cut by GROUP_CONCAT()
160
set group_concat_max_len = 1024;
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;
164
ERROR 42S22: Unknown column '2' in 'order clause'
165
drop table t1;
166
create table t1 ( URL_ID int(11), URL varchar(80));
167
create table t2 ( REQ_ID int(11), URL_ID int(11));
168
insert into t1 values (4,'www.host.com'), (5,'www.google.com'),(5,'www.help.com');
169
insert into t2 values (1,4), (5,4), (5,5);
170
select REQ_ID, Group_Concat(URL) as URL from t1, t2 where
171
t2.URL_ID = t1.URL_ID group by REQ_ID;
172
REQ_ID	URL
173
1	X
174
5	X,X,X
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;
177
REQ_ID	URL	urll	urlg
178
1	X	4	4
179
5	X,X,X	4	5
180
drop table t1;
181
drop table t2;
182
create table t1 (id int, name varchar(16));
183
insert into t1 values (1,'longername'),(1,'evenlongername');
184
select ifnull(group_concat(concat(t1.id, ':', t1.name)), 'shortname') as 'without distinct: how it should be' from t1;
185
without distinct: how it should be
186
1:longername,1:evenlongername
187
select distinct ifnull(group_concat(concat(t1.id, ':', t1.name)), 'shortname') as 'with distinct: cutoff at length of shortname' from t1;
188
with distinct: cutoff at length of shortname
189
1:longername,1:evenlongername
190
drop table t1;
191
create table t1(id int);
192
create table t2(id int);
193
insert into t1 values(0),(1);
194
select group_concat(t1.id) FROM t1,t2;
195
group_concat(t1.id)
196
NULL
197
drop table t1;
198
drop table t2;
199
create table t1 (bar varchar(32));
200
insert into t1 values('test1'),('test2');
201
select group_concat(bar order by concat(bar,bar)) from t1;
202
group_concat(bar order by concat(bar,bar))
203
test1,test2
204
select group_concat(bar order by concat(bar,bar) desc) from t1;
205
group_concat(bar order by concat(bar,bar) desc)
206
test2,test1
207
select bar from t1 having group_concat(bar)='';
208
bar
209
select bar from t1 having instr(group_concat(bar), "test") > 0;
210
bar
211
test1
212
select bar from t1 having instr(group_concat(bar order by concat(bar,bar) desc), "test2,test1") > 0;
213
bar
214
test1
215
drop table t1;
216
create table t1 (a int, a1 varchar(10));
217
create table t2 (a0 int);
218
insert into t1 values (0,"a"),(0,"b"),(1,"c");
219
insert into t2 values (1),(2),(3);
220
select  group_concat(a1 order by (t1.a IN (select a0 from t2))) from t1;
221
group_concat(a1 order by (t1.a IN (select a0 from t2)))
222
b,a,c
223
select  group_concat(a1 order by (t1.a)) from t1;
224
group_concat(a1 order by (t1.a))
225
b,a,c
226
drop table t1, t2;
227
CREATE TABLE t1 (id1 tinyint(4) NOT NULL, id2 tinyint(4) NOT NULL);
228
INSERT INTO t1 VALUES (1, 1),(1, 2),(1, 3),(1, 4),(1, 5),(2, 1),(2, 2),(2, 3);
229
CREATE TABLE t2 (id1 tinyint(4) NOT NULL);
230
INSERT INTO t2 VALUES (1),(2),(3),(4),(5);
231
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;
232
id1	concat_id
233
1	1,2,3,4,5
234
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;
235
id1	concat_id
236
1	1,2,3,4,5
237
2	1,2,3
238
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;
239
id1	concat_id
240
1	5,4,3,2,1
241
2	3,2,1
242
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;
243
id1	concat_id
244
1	5,4,3,2,1
245
2	3,2,1
246
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;
247
id1	concat_id
248
1	51,42,33,24,15
249
2	33,24,15
250
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;
251
id1	concat_id
252
1	51,42,33,24,15
253
2	33,24,15
254
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;
255
id1	concat_id
256
1	5/1,4/2,3/3,2/4,1/5
257
2	3/3,2/4,1/5
258
drop table t1,t2;
259
create table t1 (s1 char(10), s2 int not null);
260
insert into t1 values ('a',2),('b',2),('c',1),('a',3),('b',4),('c',4);
261
select distinct s1 from t1 order by s2,s1;
262
s1
263
c
264
a
265
b
266
select group_concat(distinct s1) from t1;
267
group_concat(distinct s1)
268
a,b,c
269
select group_concat(distinct s1 order by s2) from t1 where s2 < 4;
270
group_concat(distinct s1 order by s2)
271
c,b,a
272
select group_concat(distinct s1 order by s2) from t1;
273
group_concat(distinct s1 order by s2)
274
c,b,a
275
drop table t1;
276
create table t1 (a int, c int);
277
insert into t1 values (1, 2), (2, 3), (2, 4), (3, 5);
278
create table t2 (a int, c int);
279
insert into t2 values (1, 5), (2, 4), (3, 3), (3,3);
280
select group_concat(c) from t1;
281
group_concat(c)
282
2,3,4,5
283
select group_concat(c order by (select c from t2 where t2.a=t1.a limit 1)) as grp from t1;
284
grp
285
5,4,3,2
286
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;
287
grp
288
5,4,3,2
289
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;
290
grp
291
2,4,3,5
292
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;
293
a	grp
294
1	2
295
2	4,3
296
3	5
297
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;
298
a	grp
299
1	2
300
2	4,3
301
3	5
302
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;
303
a	grp
304
1	2
305
2	4,3
306
3	5
307
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;
308
grp
309
5,4,3,2
310
select group_concat(c order by (select concat(t1.c,group_concat(c)) from t2 where a=t1.a)) as grp from t1;
311
grp
312
2,3,4,5
313
select a,c,(select group_concat(c order by a) from t2 where a=t1.a) as grp from t1 order by grp;
314
a	c	grp
315
3	5	3,3
316
2	3	4
317
2	4	4
318
1	2	5
319
drop table t1,t2;
320
CREATE TABLE t1 ( a int );
321
CREATE TABLE t2 ( a int );
322
INSERT INTO t1 VALUES (1), (2);
323
INSERT INTO t2 VALUES (1), (2);
324
SELECT GROUP_CONCAT(t1.a*t2.a ORDER BY t2.a) FROM t1, t2 GROUP BY t1.a;
325
GROUP_CONCAT(t1.a*t2.a ORDER BY t2.a)
326
1,2
327
2,4
328
DROP TABLE t1, t2;
329
CREATE TABLE t1 (a char(4));
330
INSERT INTO t1 VALUES ('John'), ('Anna'), ('Bill');
331
SELECT GROUP_CONCAT(a SEPARATOR '||') AS names FROM t1 
332
HAVING names LIKE '%An%';
333
names
334
John||Anna||Bill
335
SELECT GROUP_CONCAT(a SEPARATOR '###') AS names FROM t1 
336
HAVING LEFT(names, 1) ='J';
337
names
338
John###Anna###Bill
339
DROP TABLE t1;
340
CREATE TABLE t1 ( a int, b TEXT );
341
INSERT INTO t1 VALUES (1,'First Row'), (2,'Second Row');
342
SELECT GROUP_CONCAT(b ORDER BY b) FROM t1 GROUP BY a;
343
GROUP_CONCAT(b ORDER BY b)
344
First Row
345
Second Row
346
DROP TABLE t1;
347
CREATE TABLE t1 (A_ID INT NOT NULL,A_DESC CHAR(3) NOT NULL,PRIMARY KEY (A_ID));
348
INSERT INTO t1 VALUES (1,'ABC'), (2,'EFG'), (3,'HIJ');
349
CREATE TABLE t2 (A_ID INT NOT NULL,B_DESC CHAR(3) NOT NULL,PRIMARY KEY (A_ID,B_DESC));
350
INSERT INTO t2 VALUES (1,'A'),(1,'B'),(3,'F');
351
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;
352
A_ID	B_DESC
353
1	A,B
354
2	NULL
355
3	F
356
DROP TABLE t1;
357
DROP TABLE t2;
358
create table t1 (a int, b text);
359
insert into t1 values (1, 'bb'), (1, 'ccc'), (1, 'a'), (1, 'bb'), (1, 'ccc');
360
insert into t1 values (2, 'BB'), (2, 'CCC'), (2, 'A'), (2, 'BB'), (2, 'CCC');
361
select group_concat(b) from t1 group by a;
362
group_concat(b)
363
bb,ccc,a,bb,ccc
364
BB,CCC,A,BB,CCC
365
select group_concat(distinct b) from t1 group by a;
366
group_concat(distinct b)
367
bb,ccc,a
368
BB,CCC,A
369
select group_concat(b order by b) from t1 group by a;
370
group_concat(b order by b)
371
a,bb,bb,ccc,ccc
372
A,BB,BB,CCC,CCC
373
select group_concat(distinct b order by b) from t1 group by a;
374
group_concat(distinct b order by b)
375
a,bb,ccc
376
A,BB,CCC
377
set local group_concat_max_len=4;
378
select group_concat(b) from t1 group by a;
379
group_concat(b)
380
bb,c
381
BB,C
382
Warnings:
383
Warning	1260	2 line(s) were cut by GROUP_CONCAT()
384
select group_concat(distinct b) from t1 group by a;
385
group_concat(distinct b)
386
bb,c
387
BB,C
388
Warnings:
389
Warning	1260	2 line(s) were cut by GROUP_CONCAT()
390
select group_concat(b order by b) from t1 group by a;
391
group_concat(b order by b)
392
a,bb
393
A,BB
394
Warnings:
395
Warning	1260	2 line(s) were cut by GROUP_CONCAT()
396
select group_concat(distinct b order by b) from t1 group by a;
397
group_concat(distinct b order by b)
398
a,bb
399
A,BB
400
Warnings:
401
Warning	1260	2 line(s) were cut by GROUP_CONCAT()
402
insert into t1 values (1, concat(repeat('1', 300), '2')), 
403
(1, concat(repeat('1', 300), '2')), (1, concat(repeat('0', 300), '1')), 
404
(2, concat(repeat('1', 300), '2')), (2, concat(repeat('1', 300), '2')), 
405
(2, concat(repeat('0', 300), '1'));
406
set local group_concat_max_len=1024;
407
select group_concat(b) from t1 group by a;
408
group_concat(b)
409
bb,ccc,a,bb,ccc,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001
410
BB,CCC,A,BB,CCC,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001
411
select group_concat(distinct b) from t1 group by a;
412
group_concat(distinct b)
413
bb,ccc,a,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001
414
BB,CCC,A,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001
415
select group_concat(b order by b) from t1 group by a;
416
group_concat(b order by b)
417
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,a,bb,bb,ccc,ccc
418
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,A,BB,BB,CCC,CCC
419
select group_concat(distinct b order by b) from t1 group by a;
420
group_concat(distinct b order by b)
421
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,a,bb,ccc
422
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,A,BB,CCC
423
set local group_concat_max_len=400;
424
select group_concat(b) from t1 group by a;
425
group_concat(b)
426
bb,ccc,a,bb,ccc,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111
427
BB,CCC,A,BB,CCC,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,1111111111111111111111111111111111111111111111111111111111111111111111111111111111
428
Warnings:
429
Warning	1260	2 line(s) were cut by GROUP_CONCAT()
430
select group_concat(distinct b) from t1 group by a;
431
group_concat(distinct b)
432
bb,ccc,a,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
433
BB,CCC,A,1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112,00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
434
Warnings:
435
Warning	1260	2 line(s) were cut by GROUP_CONCAT()
436
select group_concat(b order by b) from t1 group by a;
437
group_concat(b order by b)
438
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111
439
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111
440
Warnings:
441
Warning	1260	2 line(s) were cut by GROUP_CONCAT()
442
select group_concat(distinct b order by b) from t1 group by a;
443
group_concat(distinct b order by b)
444
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111
445
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001,11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111
446
Warnings:
447
Warning	1260	2 line(s) were cut by GROUP_CONCAT()
448
drop table t1;
449
create table t1 (a varchar(255) character set cp1250 collate cp1250_general_ci,
450
b varchar(255) character set koi8r);
451
insert into t1 values ('xxx','yyy');
452
select collation(a) from t1;
453
collation(a)
454
cp1250_general_ci
455
select collation(group_concat(a)) from t1;
456
collation(group_concat(a))
457
cp1250_general_ci
458
create table t2 select group_concat(a) as a from t1;
459
show create table t2;
460
Table	Create Table
461
t2	CREATE TABLE "t2" (
462
  "a" varchar(400) CHARACTER SET cp1250
463
) ENGINE=MyISAM DEFAULT CHARSET=latin1
464
select collation(group_concat(a,_koi8r'test')) from t1;
465
collation(group_concat(a,_koi8r'test'))
466
cp1250_general_ci
467
select collation(group_concat(a,_koi8r 0xC1C2)) from t1;
468
ERROR HY000: Illegal mix of collations (cp1250_general_ci,IMPLICIT) and (koi8r_general_ci,COERCIBLE) for operation 'group_concat'
469
select collation(group_concat(a,b)) from t1;
470
ERROR HY000: Illegal mix of collations (cp1250_general_ci,IMPLICIT) and (koi8r_general_ci,IMPLICIT) for operation 'group_concat'
471
drop table t1;
472
drop table t2;
473
CREATE TABLE t1 (a CHAR(10) CHARACTER SET cp850);
474
INSERT INTO t1 VALUES ('À');
475
SELECT a FROM t1;
476
a
477
À
478
SELECT GROUP_CONCAT(a) FROM t1;
479
GROUP_CONCAT(a)
480
À
481
DROP TABLE t1;
482
CREATE TABLE t1 (id int);
483
SELECT GROUP_CONCAT(id) AS gc FROM t1 HAVING gc IS NULL;
484
gc
485
NULL
486
DROP TABLE t1;
487
create table t2 (a int, b int);
488
insert into t2 values (1,1), (2,2);
489
select  b x, (select group_concat(x) from t2) from  t2;
490
x	(select group_concat(x) from t2)
491
1	1,1
492
2	2,2
493
drop table t2;
494
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
select d,a,b from t1 order by a;
497
d	a	b
498
1	1	3
499
2	1	4
500
3	1	2
501
5	1	1
502
6	1	2
503
4	2	7
504
7	2	3
505
8	2	3
506
explain select a, group_concat(b) from t1 group by a with rollup;
507
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
508
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using filesort
509
select a, group_concat(b) from t1 group by a with rollup;
510
a	group_concat(b)
511
1	3,4,2,1,2
512
2	7,3,3
513
NULL	3,4,2,1,2,7,3,3
514
select a, group_concat(distinct b) from t1 group by a with rollup;
515
a	group_concat(distinct b)
516
1	3,4,2,1
517
2	7,3
518
NULL	3,4,2,1,7
519
select a, group_concat(b order by b) from t1 group by a with rollup;
520
a	group_concat(b order by b)
521
1	1,2,2,3,4
522
2	3,3,7
523
NULL	1,2,2,3,3,3,4,7
524
select a, group_concat(distinct b order by b) from t1 group by a with rollup;
525
a	group_concat(distinct b order by b)
526
1	1,2,3,4
527
2	3,7
528
NULL	1,2,3,4,7
529
drop table t1;
530
create table t1 (a char(3), b char(20), primary key (a, b));
531
insert into t1 values ('ABW', 'Dutch'), ('ABW', 'English');
532
select group_concat(a) from t1 group by b;
533
group_concat(a)
534
ABW
535
ABW
536
drop table t1;
537
CREATE TABLE t1 (
538
aID smallint(5) unsigned NOT NULL auto_increment,
539
sometitle varchar(255) NOT NULL default '',
540
bID smallint(5) unsigned NOT NULL,
541
PRIMARY KEY  (aID),
542
UNIQUE KEY sometitle (sometitle)
543
);
544
INSERT INTO t1 SET sometitle = 'title1', bID = 1;
545
INSERT INTO t1 SET sometitle = 'title2', bID = 1;
546
CREATE TABLE t2 (
547
bID smallint(5) unsigned NOT NULL auto_increment,
548
somename varchar(255) NOT NULL default '',
549
PRIMARY KEY  (bID),
550
UNIQUE KEY somename (somename)
551
);
552
INSERT INTO t2 SET somename = 'test';
553
SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')
554
FROM t1 JOIN t2 ON t1.bID = t2.bID;
555
COUNT(*)	GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')
556
2	test
557
INSERT INTO t2 SET somename = 'test2';
558
SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')
559
FROM t1 JOIN t2 ON t1.bID = t2.bID;
560
COUNT(*)	GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')
561
2	test
562
DELETE FROM t2 WHERE somename = 'test2';
563
SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')
564
FROM t1 JOIN t2 ON t1.bID = t2.bID;
565
COUNT(*)	GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')
566
2	test
567
DROP TABLE t1,t2;
568
create table t1 ( a int not null default 0);
569
select * from (select group_concat(a) from t1) t2;
570
group_concat(a)
571
NULL
572
select group_concat('x') UNION ALL select 1;
573
group_concat('x')
574
x
575
1
576
drop table t1;
577
CREATE TABLE t1 (id int, a varchar(9));
578
INSERT INTO t1 VALUES
579
(2, ''), (1, ''), (2, 'x'), (1, 'y'), (3, 'z'), (3, '');
580
SELECT GROUP_CONCAT(a) FROM t1;
581
GROUP_CONCAT(a)
582
,,x,y,z,
583
SELECT GROUP_CONCAT(a ORDER BY a) FROM t1;
584
GROUP_CONCAT(a ORDER BY a)
585
,,,x,y,z
586
SELECT GROUP_CONCAT(a) FROM t1 GROUP BY id;
587
GROUP_CONCAT(a)
588
,y
589
,x
590
z,
591
SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY id;
592
GROUP_CONCAT(a ORDER BY a)
593
,y
594
,x
595
,z
596
DROP TABLE t1;
597
create table t1(f1 int);
598
insert into t1 values(1),(2),(3);
599
select f1, group_concat(f1+1) from t1 group by f1 with rollup;
600
f1	group_concat(f1+1)
601
1	2
602
2	3
603
3	4
604
NULL	2,3,4
605
select count(distinct (f1+1)) from t1 group by f1 with rollup;
606
count(distinct (f1+1))
607
1
608
1
609
1
610
3
611
drop table t1;
612
create table t1 (f1 int unsigned, f2 varchar(255));
613
insert into t1 values (1,repeat('a',255)),(2,repeat('b',255));
614
select f2,group_concat(f1) from t1 group by f2;
615
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
616
def	test	t1	t1	f2	f2	253	255	255	Y	0	0	8
617
def					group_concat(f1)	253	400	1	Y	128	0	63
618
f2	group_concat(f1)
619
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	1
620
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb	2
621
drop table t1;
622
set names latin1;
623
create table t1 (a char, b char);
624
insert into t1 values ('a', 'a'), ('a', 'b'), ('b', 'a'), ('b', 'b');
625
create table t2 select group_concat(b) as a from t1 where a = 'a';
626
create table t3 (select group_concat(a) as a from t1 where a = 'a') union
627
(select group_concat(b) as a from t1 where a = 'b');
628
select charset(a) from t2;
629
charset(a)
630
latin1
631
select charset(a) from t3;
632
charset(a)
633
latin1
634
latin1
635
drop table t1, t2, t3;
636
set names default;
637
create table t1 (c1 varchar(10), c2 int);
638
select charset(group_concat(c1 order by c2)) from t1;
639
charset(group_concat(c1 order by c2))
640
latin1
641
drop table t1;
642
CREATE TABLE t1 (a INT(10), b LONGTEXT, PRIMARY KEY (a));
643
SET GROUP_CONCAT_MAX_LEN = 20000000;
644
INSERT INTO t1 VALUES (1,REPEAT(CONCAT('A',CAST(CHAR(0) AS BINARY),'B'), 40000));
645
INSERT INTO t1 SELECT a + 1, b FROM t1;
646
SELECT a, CHAR_LENGTH(b) FROM t1;
647
a	CHAR_LENGTH(b)
648
1	120000
649
2	120000
650
SELECT CHAR_LENGTH( GROUP_CONCAT(b) ) FROM t1;
651
CHAR_LENGTH( GROUP_CONCAT(b) )
652
240001
653
SET GROUP_CONCAT_MAX_LEN = 1024;
654
DROP TABLE t1;
655
CREATE TABLE t1 (a int, b int);
656
INSERT INTO t1 VALUES (2,1), (1,2), (2,2), (1,3);
657
SELECT GROUP_CONCAT(a), x 
658
FROM (SELECT a, GROUP_CONCAT(b) x FROM t1 GROUP BY a) AS s
659
GROUP BY x;
660
GROUP_CONCAT(a)	x
661
2	1,2
662
1	2,3
663
DROP TABLE t1;
664
set names utf8;
665
create table t1
666
(
667
x text character set utf8 not null,
668
y integer not null
669
);
670
insert into t1 values (repeat('a', 1022), 0), (repeat(_utf8 0xc3b7, 4), 0);
671
set group_concat_max_len= 1022 + 10;
672
select @x:=group_concat(x) from t1 group by y;
673
select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);
674
@@group_concat_max_len	length(@x)	char_length(@x)	right(@x,12)	right(HEX(@x),12)
675
1032	1031	1027	aaaaaaa,÷÷÷÷	C3B7C3B7C3B7
676
set group_concat_max_len= 1022 + 9;
677
select @x:=group_concat(x) from t1 group by y;
678
select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);
679
@@group_concat_max_len	length(@x)	char_length(@x)	right(@x,12)	right(HEX(@x),12)
680
1031	1031	1027	aaaaaaa,÷÷÷÷	C3B7C3B7C3B7
681
set group_concat_max_len= 1022 + 8;
682
select @x:=group_concat(x) from t1 group by y;
683
select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);
684
@@group_concat_max_len	length(@x)	char_length(@x)	right(@x,12)	right(HEX(@x),12)
685
1030	1029	1026	aaaaaaaa,÷÷÷	C3B7C3B7C3B7
686
set group_concat_max_len= 1022 + 7;
687
select @x:=group_concat(x) from t1 group by y;
688
select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);
689
@@group_concat_max_len	length(@x)	char_length(@x)	right(@x,12)	right(HEX(@x),12)
690
1029	1029	1026	aaaaaaaa,÷÷÷	C3B7C3B7C3B7
691
set group_concat_max_len= 1022 + 6;
692
select @x:=group_concat(x) from t1 group by y;
693
select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);
694
@@group_concat_max_len	length(@x)	char_length(@x)	right(@x,12)	right(HEX(@x),12)
695
1028	1027	1025	aaaaaaaaa,÷÷	612CC3B7C3B7
696
set group_concat_max_len= 1022 + 5;
697
select @x:=group_concat(x) from t1 group by y;
698
select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);
699
@@group_concat_max_len	length(@x)	char_length(@x)	right(@x,12)	right(HEX(@x),12)
700
1027	1027	1025	aaaaaaaaa,÷÷	612CC3B7C3B7
701
set group_concat_max_len= 1022 + 4;
702
select @x:=group_concat(x) from t1 group by y;
703
select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);
704
@@group_concat_max_len	length(@x)	char_length(@x)	right(@x,12)	right(HEX(@x),12)
705
1026	1025	1024	aaaaaaaaaa,÷	6161612CC3B7
706
set group_concat_max_len= 1022 + 3;
707
select @x:=group_concat(x) from t1 group by y;
708
select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);
709
@@group_concat_max_len	length(@x)	char_length(@x)	right(@x,12)	right(HEX(@x),12)
710
1025	1025	1024	aaaaaaaaaa,÷	6161612CC3B7
711
set group_concat_max_len= 1022 + 2;
712
select @x:=group_concat(x) from t1 group by y;
713
select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);
714
@@group_concat_max_len	length(@x)	char_length(@x)	right(@x,12)	right(HEX(@x),12)
715
1024	1023	1023	aaaaaaaaaaa,	61616161612C
716
set group_concat_max_len= 1022 + 1;
717
select @x:=group_concat(x) from t1 group by y;
718
select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);
719
@@group_concat_max_len	length(@x)	char_length(@x)	right(@x,12)	right(HEX(@x),12)
720
1023	1023	1023	aaaaaaaaaaa,	61616161612C
721
drop table t1;
722
set group_concat_max_len=1024;
723
set names latin1;
724
create table t1 (f1 int unsigned, f2 varchar(255));
725
insert into t1 values (1,repeat('a',255)),(2,repeat('b',255));
726
select f2,group_concat(f1) from t1 group by f2;
727
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
728
def	test	t1	t1	f2	f2	253	255	255	Y	0	0	8
729
def					group_concat(f1)	252	1024	1	Y	128	0	63
730
f2	group_concat(f1)
731
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa	1
732
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb	2
733
drop table t1;
734
CREATE TABLE t1(a TEXT, b CHAR(20));
735
INSERT INTO t1 VALUES ("one.1","one.1"),("two.2","two.2"),("one.3","one.3");
736
SELECT GROUP_CONCAT(DISTINCT UCASE(a)) FROM t1;
737
GROUP_CONCAT(DISTINCT UCASE(a))
738
ONE.1,TWO.2,ONE.3
739
SELECT GROUP_CONCAT(DISTINCT UCASE(b)) FROM t1;
740
GROUP_CONCAT(DISTINCT UCASE(b))
741
ONE.1,TWO.2,ONE.3
742
DROP TABLE t1;
743
CREATE TABLE t1( a VARCHAR( 10 ), b INT );
744
INSERT INTO t1 VALUES ( repeat( 'a', 10 ), 1), 
745
( repeat( 'b', 10 ), 2);
746
SET group_concat_max_len = 20;
747
SELECT GROUP_CONCAT( a ) FROM t1;
748
GROUP_CONCAT( a )
749
aaaaaaaaaa,bbbbbbbbb
750
Warnings:
751
Warning	1260	1 line(s) were cut by GROUP_CONCAT()
752
SELECT GROUP_CONCAT( DISTINCT a ) FROM t1;
753
GROUP_CONCAT( DISTINCT a )
754
aaaaaaaaaa,bbbbbbbbb
755
Warnings:
756
Warning	1260	1 line(s) were cut by GROUP_CONCAT()
757
SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1;
758
GROUP_CONCAT( a ORDER BY b )
759
aaaaaaaaaa,bbbbbbbbb
760
Warnings:
761
Warning	1260	1 line(s) were cut by GROUP_CONCAT()
762
SELECT GROUP_CONCAT( DISTINCT a ORDER BY b ) FROM t1;
763
GROUP_CONCAT( DISTINCT a ORDER BY b )
764
aaaaaaaaaa,bbbbbbbbb
765
Warnings:
766
Warning	1260	1 line(s) were cut by GROUP_CONCAT()
767
SET group_concat_max_len = DEFAULT;
768
DROP TABLE t1;
769
SET group_concat_max_len= 65535;
770
CREATE TABLE t1( a TEXT, b INTEGER );
771
INSERT INTO t1 VALUES ( 'a', 0 ), ( 'b', 1 );
772
SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1;
773
GROUP_CONCAT( a ORDER BY b )
774
a,b
775
SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1;
776
GROUP_CONCAT(DISTINCT a ORDER BY b)
777
a,b
778
SELECT GROUP_CONCAT(DISTINCT a) FROM t1;
779
GROUP_CONCAT(DISTINCT a)
780
a,b
781
SET group_concat_max_len= 10;
782
SELECT GROUP_CONCAT(a ORDER BY b) FROM t1;
783
GROUP_CONCAT(a ORDER BY b)
784
a,b
785
SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1;
786
GROUP_CONCAT(DISTINCT a ORDER BY b)
787
a,b
788
SELECT GROUP_CONCAT(DISTINCT a) FROM t1;
789
GROUP_CONCAT(DISTINCT a)
790
a,b
791
SET group_concat_max_len= 65535;
792
CREATE TABLE t2( a TEXT );
793
INSERT INTO t2 VALUES( REPEAT( 'a', 5000 ) );
794
INSERT INTO t2 VALUES( REPEAT( 'b', 5000 ) );
795
INSERT INTO t2 VALUES( REPEAT( 'a', 5000 ) );
796
SELECT LENGTH( GROUP_CONCAT( DISTINCT a ) ) FROM t2;
797
LENGTH( GROUP_CONCAT( DISTINCT a ) )
798
10001
799
CREATE TABLE t3( a TEXT, b INT  );
800
INSERT INTO t3 VALUES( REPEAT( 'a', 65534 ), 1 );
801
INSERT INTO t3 VALUES( REPEAT( 'a', 65535 ), 2 );
802
INSERT INTO t3 VALUES( REPEAT( 'a', 65533 ), 3 );
803
SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 1;
804
LENGTH( GROUP_CONCAT( a ) )
805
65534
806
SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 2;
807
LENGTH( GROUP_CONCAT( a ) )
808
65535
809
SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 3;
810
LENGTH( GROUP_CONCAT( a ) )
811
65533
812
SET group_concat_max_len= DEFAULT;
813
DROP TABLE t1, t2, t3;
814
set names latin1;
815
create table t1 (id int, name varchar(20)) DEFAULT CHARSET=utf8;
816
insert into t1 (id, name) values (1, "óra");
817
insert into t1 (id, name) values (2, "óra");
818
select b.id, group_concat(b.name) from t1 a, t1 b group by b.id;
819
id	group_concat(b.name)
820
1	óra,óra
821
2	óra,óra
822
drop table t1;
823
create table t1(a bit not null default 0);
824
insert into t1 values (), (), ();
825
select group_concat(distinct a) from t1;
826
group_concat(distinct a)
827
0
828
select group_concat(distinct a order by a) from t1;
829
group_concat(distinct a order by a)
830
0
831
drop table t1;
832
create table t1(a bit(2) not null);
833
insert into t1 values (1), (0), (0), (3), (1);
834
select group_concat(distinct a) from t1;
835
group_concat(distinct a)
836
1,0,3
837
select group_concat(distinct a order by a) from t1;
838
group_concat(distinct a order by a)
839
0,1,3
840
select group_concat(distinct a order by a desc) from t1;
841
group_concat(distinct a order by a desc)
842
3,1,0
843
drop table t1;
844
create table t1(a bit(2), b varchar(10), c bit);
845
insert into t1 values (1, 'a', 0), (0, 'b', 1), (0, 'c', 0), (3, 'd', 1),
846
(1, 'e', 1), (3, 'f', 1), (0, 'g', 1);
847
select group_concat(distinct a, c) from t1;
848
group_concat(distinct a, c)
849
10,01,00,31,11
850
select group_concat(distinct a, c order by a) from t1;
851
group_concat(distinct a, c order by a)
852
00,01,11,10,31
853
select group_concat(distinct a, c) from t1;
854
group_concat(distinct a, c)
855
10,01,00,31,11
856
select group_concat(distinct a, c order by a, c) from t1;
857
group_concat(distinct a, c order by a, c)
858
00,01,10,11,31
859
select group_concat(distinct a, c order by a desc, c desc) from t1;
860
group_concat(distinct a, c order by a desc, c desc)
861
31,11,10,01,00
862
drop table t1;
863
create table t1 (f1 char(20));
864
insert into t1 values (''),('');
865
select group_concat(distinct f1) from t1;
866
group_concat(distinct f1)
867
868
select group_concat(f1) from t1;
869
group_concat(f1)
870
,
871
drop table t1;
872
CREATE TABLE t1 (a INT, b INT);
873
INSERT INTO t1 VALUES (1, 1), (2, 2), (2, 3);
874
SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1;
875
GROUP_CONCAT(DISTINCT a ORDER BY b)
876
1,2
877
SELECT GROUP_CONCAT(DISTINCT a ORDER BY b DESC) FROM t1;
878
GROUP_CONCAT(DISTINCT a ORDER BY b DESC)
879
2,1
880
SELECT GROUP_CONCAT(DISTINCT a) FROM t1;
881
GROUP_CONCAT(DISTINCT a)
882
1,2
883
SELECT GROUP_CONCAT(DISTINCT a + 1 ORDER BY 3 - b) FROM t1;
884
GROUP_CONCAT(DISTINCT a + 1 ORDER BY 3 - b)
885
3,2
886
SELECT GROUP_CONCAT(DISTINCT a + 1 ORDER BY b) FROM t1;
887
GROUP_CONCAT(DISTINCT a + 1 ORDER BY b)
888
2,3
889
SELECT GROUP_CONCAT(a ORDER BY 3 - b) FROM t1;
890
GROUP_CONCAT(a ORDER BY 3 - b)
891
2,2,1
892
CREATE TABLE t2 (a INT, b INT, c INT, d INT);
893
INSERT INTO t2 VALUES (1,1, 1,1), (1,1, 2,2), (1,2, 2,1), (2,1, 1,2);
894
SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY c, d) FROM t2;
895
GROUP_CONCAT(DISTINCT a, b ORDER BY c, d)
896
11,21,12
897
SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY d, c) FROM t2;
898
GROUP_CONCAT(DISTINCT a, b ORDER BY d, c)
899
11,12,21
900
CREATE TABLE t3 (a INT, b INT, c INT);
901
INSERT INTO t3 VALUES (1, 1, 1), (2, 1, 2), (3, 2, 1);
902
SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY b, c) FROM t3;
903
GROUP_CONCAT(DISTINCT a, b ORDER BY b, c)
904
11,21,32
905
SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY c, b) FROM t3;
906
GROUP_CONCAT(DISTINCT a, b ORDER BY c, b)
907
11,32,21
908
SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY a, b) FROM t1;
909
GROUP_CONCAT(DISTINCT a, b ORDER BY a, b)
910
11,22,23
911
SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY a, b) FROM t1;
912
GROUP_CONCAT(DISTINCT b, a ORDER BY a, b)
913
11,22,32
914
SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY b, a) FROM t1;
915
GROUP_CONCAT(DISTINCT a, b ORDER BY b, a)
916
11,22,23
917
SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY a, b) FROM t1;
918
GROUP_CONCAT(DISTINCT b, a ORDER BY a, b)
919
11,22,32
920
SELECT GROUP_CONCAT(DISTINCT a ORDER BY a, b) FROM t1;
921
GROUP_CONCAT(DISTINCT a ORDER BY a, b)
922
1,2
923
SELECT GROUP_CONCAT(DISTINCT b ORDER BY b, a) FROM t1;
924
GROUP_CONCAT(DISTINCT b ORDER BY b, a)
925
1,2,3
926
SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY a) FROM t1;
927
GROUP_CONCAT(DISTINCT a, b ORDER BY a)
928
11,23,22
929
SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY b) FROM t1;
930
GROUP_CONCAT(DISTINCT b, a ORDER BY b)
931
11,22,32
932
DROP TABLE t1, t2, t3;
933
CREATE TABLE t1(a INT);
934
INSERT INTO t1 VALUES (),();
935
SELECT s1.d1 FROM
936
(
937
SELECT
938
t1.a as d1,
939
GROUP_CONCAT(DISTINCT t1.a) AS d2
940
FROM
941
t1 AS t1,
942
t1 AS t2
943
GROUP BY 1
944
) AS s1;
945
d1
946
NULL
947
DROP TABLE t1;
948
End of 5.0 tests