~linuxjedi/drizzle/trunk-bug-667053

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(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

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) 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

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

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

575

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

589

590

591

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

592

GROUP_CONCAT(a ORDER BY a)

593

594

595

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

608

609

610

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

732

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

828

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

829

group_concat(distinct a order by a)

830

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

946

NULL

947

DROP TABLE t1;

948

End of 5.0 tests

Older »