~drizzle-trunk/drizzle/development

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

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

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

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, URL varchar(80));

167

create table t2 ( REQ_ID int, URL_ID int);

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 int NOT NULL, id2 int 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 int 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

263

264

265

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

419

select group_concat(distinct b order by b) from t1 group by a;

420

group_concat(distinct b order by b)

421

422

423

set local group_concat_max_len=400;

424

select group_concat(b) from t1 group by a;

425

group_concat(b)

426

427

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

433

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

439

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

445

446

Warnings:

447

Warning 1260 2 line(s) were cut by GROUP_CONCAT()

448

drop table t1;

449

create table t1 (a varchar(255), b varchar(255) );

450

insert into t1 values ('xxx','yyy');

451

select collation(a) from t1;

452

collation(a)

453

utf8_general_ci

454

select collation(group_concat(a)) from t1;

455

collation(group_concat(a))

456

utf8_general_ci

457

create table t2 select group_concat(a) as a from t1;

458

show create table t2;

459

Table Create Table

460

t2 CREATE TABLE `t2` (

461

`a` varchar(100)

462

) ENGINE=InnoDB

463

select collation(group_concat(a,b)) from t1;

464

collation(group_concat(a,b))

465

utf8_general_ci

466

drop table t1;

467

drop table t2;

468

CREATE TABLE t1 (id int);

469

SELECT GROUP_CONCAT(id) AS gc FROM t1 HAVING gc IS NULL;

470

471

NULL

472

DROP TABLE t1;

473

create table t2 (a int, b int);

474

insert into t2 values (1,1), (2,2);

475

select b x, (select group_concat(x) from t2) from t2;

476

x (select group_concat(x) from t2)

477

1 1,1

478

2 2,2

479

drop table t2;

480

create table t1 (d int not null auto_increment,primary key(d), a int, b int, c int);

481

insert into t1(a,b) values (1,3), (1,4), (1,2), (2,7), (1,1), (1,2), (2,3), (2,3);

482

select d,a,b from t1 order by a;

483

d a b

484

1 1 3

485

2 1 4

486

3 1 2

487

5 1 1

488

6 1 2

489

4 2 7

490

7 2 3

491

8 2 3

492

explain select a, group_concat(b) from t1 group by a with rollup;

493

id select_type table type possible_keys key key_len ref rows Extra

494

1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using filesort

495

select a, group_concat(b) from t1 group by a with rollup;

496

a group_concat(b)

497

1 3,4,2,1,2

498

2 7,3,3

499

NULL 3,4,2,1,2,7,3,3

500

select a, group_concat(distinct b) from t1 group by a with rollup;

501

a group_concat(distinct b)

502

1 3,4,2,1

503

2 7,3

504

NULL 3,4,2,1,7

505

select a, group_concat(b order by b) from t1 group by a with rollup;

506

a group_concat(b order by b)

507

1 1,2,2,3,4

508

2 3,3,7

509

NULL 1,2,2,3,3,3,4,7

510

select a, group_concat(distinct b order by b) from t1 group by a with rollup;

511

a group_concat(distinct b order by b)

512

1 1,2,3,4

513

2 3,7

514

NULL 1,2,3,4,7

515

drop table t1;

516

create table t1 (a char(3), b char(20), primary key (a, b));

517

insert into t1 values ('ABW', 'Dutch'), ('ABW', 'English');

518

select group_concat(a) from t1 group by b;

519

group_concat(a)

520

ABW

521

ABW

522

drop table t1;

523

CREATE TABLE t1 (

524

aID int NOT NULL auto_increment,

525

sometitle varchar(155) NOT NULL default '',

526

bID int NOT NULL,

527

PRIMARY KEY (aID),

528

UNIQUE KEY sometitle (sometitle)

529

);

530

INSERT INTO t1 SET sometitle = 'title1', bID = 1;

531

INSERT INTO t1 SET sometitle = 'title2', bID = 1;

532

CREATE TABLE t2 (

533

bID int NOT NULL auto_increment,

534

somename varchar(155) NOT NULL default '',

535

PRIMARY KEY (bID),

536

UNIQUE KEY somename (somename)

537

);

538

INSERT INTO t2 SET somename = 'test';

539

SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')

540

FROM t1 JOIN t2 ON t1.bID = t2.bID;

541

COUNT(*) GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')

542

2 test

543

INSERT INTO t2 SET somename = 'test2';

544

SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')

545

FROM t1 JOIN t2 ON t1.bID = t2.bID;

546

COUNT(*) GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')

547

2 test

548

DELETE FROM t2 WHERE somename = 'test2';

549

SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')

550

FROM t1 JOIN t2 ON t1.bID = t2.bID;

551

COUNT(*) GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')

552

2 test

553

DROP TABLE t1,t2;

554

create table t1 ( a int not null default 0);

555

select * from (select group_concat(a) from t1) t2;

556

group_concat(a)

557

NULL

558

select group_concat('x') UNION ALL select 1;

559

group_concat('x')

560

561

562

drop table t1;

563

CREATE TABLE t1 (id int, a varchar(9));

564

INSERT INTO t1 VALUES

565

(2, ''), (1, ''), (2, 'x'), (1, 'y'), (3, 'z'), (3, '');

566

SELECT GROUP_CONCAT(a) FROM t1;

567

GROUP_CONCAT(a)

568

,,x,y,z,

569

SELECT GROUP_CONCAT(a ORDER BY a) FROM t1;

570

GROUP_CONCAT(a ORDER BY a)

571

,,,x,y,z

572

SELECT GROUP_CONCAT(a) FROM t1 GROUP BY id;

573

GROUP_CONCAT(a)

574

575

576

577

SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY id;

578

GROUP_CONCAT(a ORDER BY a)

579

580

581

582

DROP TABLE t1;

583

create table t1(f1 int);

584

insert into t1 values(1),(2),(3);

585

select f1, group_concat(f1+1) from t1 group by f1 with rollup;

586

f1 group_concat(f1+1)

587

1 2

588

2 3

589

3 4

590

NULL 2,3,4

591

select count(distinct (f1+1)) from t1 group by f1 with rollup;

592

count(distinct (f1+1))

593

594

595

596

597

drop table t1;

598

create table t1 (f1 int, f2 varchar(255));

599

insert into t1 values (1,repeat('a',255)),(2,repeat('b',255));

600

select f2,group_concat(f1) from t1 group by f2;

601

Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr

602

def test t1 t1 f2 f2 9 1020 255 Y 0 0 45

603

def group_concat(f1) 9 400 1 Y 128 0 63

604

f2 group_concat(f1)

605

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 1

606

bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb 2

607

drop table t1;

608

create table t1 (a char, b char);

609

insert into t1 values ('a', 'a'), ('a', 'b'), ('b', 'a'), ('b', 'b');

610

create table t2 select group_concat(b) as a from t1 where a = 'a';

611

create table t3 (select group_concat(a) as a from t1 where a = 'a') union

612

(select group_concat(b) as a from t1 where a = 'b');

613

drop table t1, t2, t3;

614

CREATE TABLE t1 (a INT, b LONGTEXT, PRIMARY KEY (a));

615

SET GROUP_CONCAT_MAX_LEN = 20000000;

616

INSERT INTO t1 VALUES (1,REPEAT(CONCAT('A',CAST(CHAR(0) AS BINARY),'B'), 40000));

617

INSERT INTO t1 SELECT a + 1, b FROM t1;

618

SELECT a, CHAR_LENGTH(b) FROM t1;

619

a CHAR_LENGTH(b)

620

1 120000

621

2 120000

622

SELECT CHAR_LENGTH( GROUP_CONCAT(b) ) FROM t1;

623

CHAR_LENGTH( GROUP_CONCAT(b) )

624

240001

625

SET GROUP_CONCAT_MAX_LEN = 1024;

626

DROP TABLE t1;

627

CREATE TABLE t1 (a int, b int);

628

INSERT INTO t1 VALUES (2,1), (1,2), (2,2), (1,3);

629

SELECT GROUP_CONCAT(a), x

630

FROM (SELECT a, GROUP_CONCAT(b) x FROM t1 GROUP BY a) AS s

631

GROUP BY x;

632

GROUP_CONCAT(a) x

633

2 1,2

634

1 2,3

635

DROP TABLE t1;

636

create table t1

637

(

638

x text not null,

639

y integer not null

640

);

641

insert into t1 values (repeat('a', 1022), 0), (repeat(_utf8 0xc3b7, 4), 0);

642

set group_concat_max_len= 1022 + 10;

643

select @x:=group_concat(x) from t1 group by y;

644

select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);

645

@@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12)

646

1032 1031 1027 aaaaaaa,÷÷÷÷ C3B7C3B7C3B7

647

set group_concat_max_len= 1022 + 9;

648

select @x:=group_concat(x) from t1 group by y;

649

select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);

650

@@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12)

651

1031 1031 1027 aaaaaaa,÷÷÷÷ C3B7C3B7C3B7

652

set group_concat_max_len= 1022 + 8;

653

select @x:=group_concat(x) from t1 group by y;

654

select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);

655

@@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12)

656

1030 1029 1026 aaaaaaaa,÷÷÷ C3B7C3B7C3B7

657

set group_concat_max_len= 1022 + 7;

658

select @x:=group_concat(x) from t1 group by y;

659

select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);

660

@@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12)

661

1029 1029 1026 aaaaaaaa,÷÷÷ C3B7C3B7C3B7

662

set group_concat_max_len= 1022 + 6;

663

select @x:=group_concat(x) from t1 group by y;

664

select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);

665

@@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12)

666

1028 1027 1025 aaaaaaaaa,÷÷ 612CC3B7C3B7

667

set group_concat_max_len= 1022 + 5;

668

select @x:=group_concat(x) from t1 group by y;

669

select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);

670

@@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12)

671

1027 1027 1025 aaaaaaaaa,÷÷ 612CC3B7C3B7

672

set group_concat_max_len= 1022 + 4;

673

select @x:=group_concat(x) from t1 group by y;

674

select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);

675

@@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12)

676

1026 1025 1024 aaaaaaaaaa,÷ 6161612CC3B7

677

set group_concat_max_len= 1022 + 3;

678

select @x:=group_concat(x) from t1 group by y;

679

select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);

680

@@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12)

681

1025 1025 1024 aaaaaaaaaa,÷ 6161612CC3B7

682

set group_concat_max_len= 1022 + 2;

683

select @x:=group_concat(x) from t1 group by y;

684

select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);

685

@@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12)

686

1024 1023 1023 aaaaaaaaaaa, 61616161612C

687

set group_concat_max_len= 1022 + 1;

688

select @x:=group_concat(x) from t1 group by y;

689

select @@group_concat_max_len, length(@x), char_length(@x), right(@x,12), right(HEX(@x),12);

690

@@group_concat_max_len length(@x) char_length(@x) right(@x,12) right(HEX(@x),12)

691

1023 1023 1023 aaaaaaaaaaa, 61616161612C

692

drop table t1;

693

set group_concat_max_len=1024;

694

create table t1 (f1 int, f2 varchar(255));

695

insert into t1 values (1,repeat('a',255)),(2,repeat('b',255));

696

select f2,group_concat(f1) from t1 group by f2;

697

Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr

698

def test t1 t1 f2 f2 9 1020 255 Y 0 0 45

699

def group_concat(f1) 13 1024 1 Y 128 0 63

700

f2 group_concat(f1)

701

702

703

drop table t1;

704

CREATE TABLE t1(a TEXT, b CHAR(20));

705

INSERT INTO t1 VALUES ("one.1","one.1"),("two.2","two.2"),("one.3","one.3");

706

SELECT GROUP_CONCAT(DISTINCT UCASE(a)) FROM t1;

707

GROUP_CONCAT(DISTINCT UCASE(a))

708

ONE.1,TWO.2,ONE.3

709

SELECT GROUP_CONCAT(DISTINCT UCASE(b)) FROM t1;

710

GROUP_CONCAT(DISTINCT UCASE(b))

711

ONE.1,TWO.2,ONE.3

712

DROP TABLE t1;

713

CREATE TABLE t1( a VARCHAR( 10 ), b INT );

714

INSERT INTO t1 VALUES ( repeat( 'a', 10 ), 1),

715

( repeat( 'b', 10 ), 2);

716

SET group_concat_max_len = 20;

717

SELECT GROUP_CONCAT( a ) FROM t1;

718

GROUP_CONCAT( a )

719

aaaaaaaaaa,bbbbbbbbb

720

Warnings:

721

Warning 1260 1 line(s) were cut by GROUP_CONCAT()

722

SELECT GROUP_CONCAT( DISTINCT a ) FROM t1;

723

GROUP_CONCAT( DISTINCT a )

724

aaaaaaaaaa,bbbbbbbbb

725

Warnings:

726

Warning 1260 1 line(s) were cut by GROUP_CONCAT()

727

SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1;

728

GROUP_CONCAT( a ORDER BY b )

729

aaaaaaaaaa,bbbbbbbbb

730

Warnings:

731

Warning 1260 1 line(s) were cut by GROUP_CONCAT()

732

SELECT GROUP_CONCAT( DISTINCT a ORDER BY b ) FROM t1;

733

GROUP_CONCAT( DISTINCT a ORDER BY b )

734

aaaaaaaaaa,bbbbbbbbb

735

Warnings:

736

Warning 1260 1 line(s) were cut by GROUP_CONCAT()

737

SET group_concat_max_len = DEFAULT;

738

DROP TABLE t1;

739

SET group_concat_max_len= 65535;

740

CREATE TABLE t1( a TEXT, b INTEGER );

741

INSERT INTO t1 VALUES ( 'a', 0 ), ( 'b', 1 );

742

SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1;

743

GROUP_CONCAT( a ORDER BY b )

744

a,b

745

SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1;

746

GROUP_CONCAT(DISTINCT a ORDER BY b)

747

a,b

748

SELECT GROUP_CONCAT(DISTINCT a) FROM t1;

749

GROUP_CONCAT(DISTINCT a)

750

a,b

751

SET group_concat_max_len= 10;

752

SELECT GROUP_CONCAT(a ORDER BY b) FROM t1;

753

GROUP_CONCAT(a ORDER BY b)

754

a,b

755

SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1;

756

GROUP_CONCAT(DISTINCT a ORDER BY b)

757

a,b

758

SELECT GROUP_CONCAT(DISTINCT a) FROM t1;

759

GROUP_CONCAT(DISTINCT a)

760

a,b

761

SET group_concat_max_len= 65535;

762

CREATE TABLE t2( a TEXT );

763

INSERT INTO t2 VALUES( REPEAT( 'a', 5000 ) );

764

INSERT INTO t2 VALUES( REPEAT( 'b', 5000 ) );

765

INSERT INTO t2 VALUES( REPEAT( 'a', 5000 ) );

766

SELECT LENGTH( GROUP_CONCAT( DISTINCT a ) ) FROM t2;

767

LENGTH( GROUP_CONCAT( DISTINCT a ) )

768

10001

769

CREATE TABLE t3( a TEXT, b INT );

770

INSERT INTO t3 VALUES( REPEAT( 'a', 65534 ), 1 );

771

INSERT INTO t3 VALUES( REPEAT( 'a', 65535 ), 2 );

772

INSERT INTO t3 VALUES( REPEAT( 'a', 65533 ), 3 );

773

SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 1;

774

LENGTH( GROUP_CONCAT( a ) )

775

65534

776

SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 2;

777

LENGTH( GROUP_CONCAT( a ) )

778

65535

779

SELECT LENGTH( GROUP_CONCAT( a ) ) FROM t3 WHERE b = 3;

780

LENGTH( GROUP_CONCAT( a ) )

781

65533

782

SET group_concat_max_len= DEFAULT;

783

DROP TABLE t1, t2, t3;

784

create table t1 (f1 char(20));

785

insert into t1 values (''),('');

786

select group_concat(distinct f1) from t1;

787

group_concat(distinct f1)

788

789

select group_concat(f1) from t1;

790

group_concat(f1)

791

792

drop table t1;

793

CREATE TABLE t1 (a INT, b INT);

794

INSERT INTO t1 VALUES (1, 1), (2, 2), (2, 3);

795

SELECT GROUP_CONCAT(DISTINCT a ORDER BY b) FROM t1;

796

GROUP_CONCAT(DISTINCT a ORDER BY b)

797

1,2

798

SELECT GROUP_CONCAT(DISTINCT a ORDER BY b DESC) FROM t1;

799

GROUP_CONCAT(DISTINCT a ORDER BY b DESC)

800

2,1

801

SELECT GROUP_CONCAT(DISTINCT a) FROM t1;

802

GROUP_CONCAT(DISTINCT a)

803

1,2

804

SELECT GROUP_CONCAT(DISTINCT a + 1 ORDER BY 3 - b) FROM t1;

805

GROUP_CONCAT(DISTINCT a + 1 ORDER BY 3 - b)

806

3,2

807

SELECT GROUP_CONCAT(DISTINCT a + 1 ORDER BY b) FROM t1;

808

GROUP_CONCAT(DISTINCT a + 1 ORDER BY b)

809

2,3

810

SELECT GROUP_CONCAT(a ORDER BY 3 - b) FROM t1;

811

GROUP_CONCAT(a ORDER BY 3 - b)

812

2,2,1

813

CREATE TABLE t2 (a INT, b INT, c INT, d INT);

814

INSERT INTO t2 VALUES (1,1, 1,1), (1,1, 2,2), (1,2, 2,1), (2,1, 1,2);

815

SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY c, d) FROM t2;

816

GROUP_CONCAT(DISTINCT a, b ORDER BY c, d)

817

11,21,12

818

SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY d, c) FROM t2;

819

GROUP_CONCAT(DISTINCT a, b ORDER BY d, c)

820

11,12,21

821

CREATE TABLE t3 (a INT, b INT, c INT);

822

INSERT INTO t3 VALUES (1, 1, 1), (2, 1, 2), (3, 2, 1);

823

SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY b, c) FROM t3;

824

GROUP_CONCAT(DISTINCT a, b ORDER BY b, c)

825

11,21,32

826

SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY c, b) FROM t3;

827

GROUP_CONCAT(DISTINCT a, b ORDER BY c, b)

828

11,32,21

829

SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY a, b) FROM t1;

830

GROUP_CONCAT(DISTINCT a, b ORDER BY a, b)

831

11,22,23

832

SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY a, b) FROM t1;

833

GROUP_CONCAT(DISTINCT b, a ORDER BY a, b)

834

11,22,32

835

SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY b, a) FROM t1;

836

GROUP_CONCAT(DISTINCT a, b ORDER BY b, a)

837

11,22,23

838

SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY a, b) FROM t1;

839

GROUP_CONCAT(DISTINCT b, a ORDER BY a, b)

840

11,22,32

841

SELECT GROUP_CONCAT(DISTINCT a ORDER BY a, b) FROM t1;

842

GROUP_CONCAT(DISTINCT a ORDER BY a, b)

843

1,2

844

SELECT GROUP_CONCAT(DISTINCT b ORDER BY b, a) FROM t1;

845

GROUP_CONCAT(DISTINCT b ORDER BY b, a)

846

1,2,3

847

SELECT GROUP_CONCAT(DISTINCT a, b ORDER BY a) FROM t1;

848

GROUP_CONCAT(DISTINCT a, b ORDER BY a)

849

11,23,22

850

SELECT GROUP_CONCAT(DISTINCT b, a ORDER BY b) FROM t1;

851

GROUP_CONCAT(DISTINCT b, a ORDER BY b)

852

11,22,32

853

DROP TABLE t1, t2, t3;

854

CREATE TABLE t1(a INT);

855

INSERT INTO t1 VALUES (),();

856

SELECT s1.d1 FROM

857

(

858

SELECT

859

t1.a as d1,

860

GROUP_CONCAT(DISTINCT t1.a) AS d2

861

FROM

862

t1 AS t1,

863

t1 AS t2

864

GROUP BY 1

865

) AS s1;

866

867

NULL

868

DROP TABLE t1;

869

End of 5.0 tests

Older »