~drizzle-trunk/drizzle/development : revision 759.1.5

5

--disable_warnings

6

drop table if exists t1,t2;

7

--enable_warnings

8

set names utf8;

9

8

10

select left(_utf8 0xD0B0D0B1D0B2,1);

11

select right(_utf8 0xD0B0D0B2D0B2,1);

9

select left(0xD0B0D0B1D0B2,1);

10

select right(0xD0B0D0B2D0B2,1);

12

11

13

12

select locate('he','hello');

14

13

select locate('he','hello',2);

20

19

select locate('HE','hello' collate utf8_bin,2);

21

20

select locate('LO','hello' collate utf8_bin,2);

22

21

23

select locate(_utf8 0xD0B1, _utf8 0xD0B0D0B1D0B2);

24

select locate(_utf8 0xD091, _utf8 0xD0B0D0B1D0B2);

25

select locate(_utf8 0xD0B1, _utf8 0xD0B0D091D0B2);

26

select locate(_utf8 0xD091, _utf8 0xD0B0D0B1D0B2 collate utf8_bin);

27

select locate(_utf8 0xD0B1, _utf8 0xD0B0D091D0B2 collate utf8_bin);

22

select locate(0xD0B1, 0xD0B0D0B1D0B2);

23

select locate(0xD091, 0xD0B0D0B1D0B2);

24

select locate(0xD0B1, 0xD0B0D091D0B2);

25

# TODO: Bug lp:314472

26

#select locate(0xD091, 0xD0B0D0B1D0B2 collate utf8_bin);

27

#select locate(0xD0B1, 0xD0B0D091D0B2 collate utf8_bin);

28

29

select length(_utf8 0xD0B1), bit_length(_utf8 0xD0B1), char_length(_utf8 0xD0B1);

29

select length(0xD0B1), bit_length(0xD0B1), char_length(0xD0B1);

30

31

select 'a' like 'a';

32

select 'A' like 'a';

33

select 'A' like 'a' collate utf8_bin;

34

select _utf8 0xD0B0D0B1D0B2 like concat(_utf8'%',_utf8 0xD0B1,_utf8 '%');

34

select 0xD0B0D0B1D0B2 like concat(_utf8'%',0xD0B1,'%');

35

36

# Bug #6040: can't retrieve records with umlaut

37

# characters in case insensitive manner.

38

# Case insensitive search LIKE comparison

39

# was broken for multibyte characters:

40

select convert(_latin1'G�nter Andr�' using utf8) like CONVERT(_latin1'G�NTER%' USING utf8);

41

select CONVERT(_koi8r'��' USING utf8) LIKE CONVERT(_koi8r'��' USING utf8);

42

select CONVERT(_koi8r'��' USING utf8) LIKE CONVERT(_koi8r'��' USING utf8);

40

select 'Günter André' like 'GÜNTER%';

43

41

44

42

#

45

43

# Check the following:

62

60

SELECT 'a\t' < 'a' collate utf8_bin;

63

61

SELECT 'a\t' < 'a ' collate utf8_bin;

64

62

65

CREATE TABLE t1 (a char(10) character set utf8 not null);

63

CREATE TABLE t1 (a char(10) not null);

66

64

INSERT INTO t1 VALUES ('a'),('a\0'),('a\t'),('a ');

67

65

SELECT hex(a),STRCMP(a,'a'), STRCMP(a,'a ') FROM t1;

68

66

DROP TABLE t1;

70

68

#

71

69

# Fix this, it should return 1:

72

70

#

73

#select _utf8 0xD0B0D0B1D0B2 like concat(_utf8'%',_utf8 0xD091,_utf8 '%');

71

#select 0xD0B0D0B1D0B2 like concat(_utf8'%',0xD091,'%');

74

72

#

75

73

76

74

#

82

80

#

83

81

# LELF() didn't work well with utf8 in some cases too.

84

82

#

85

select char_length(left(@a:='тест',5)), length(@a), @a;

83

select char_length(left(@a:='න ',5)), length(@a), @a;

86

84

87

85

88

86

#

93

91

select * from t1;

94

92

drop table t1;

95

93

96

#

97

# Bug#22646 LC_TIME_NAMES: Assignment to non-UTF8 target fails

98

#

99

set names utf8;

100

set LC_TIME_NAMES='fr_FR';

101

create table t1 (s1 char(20) character set latin1);

102

insert into t1 values (date_format('2004-02-02','%M'));

103

select hex(s1) from t1;

104

drop table t1;

105

create table t1 (s1 char(20) character set koi8r);

106

set LC_TIME_NAMES='ru_RU';

107

insert into t1 values (date_format('2004-02-02','%M'));

108

insert into t1 values (date_format('2004-02-02','%b'));

109

insert into t1 values (date_format('2004-02-02','%W'));

110

insert into t1 values (date_format('2004-02-02','%a'));

111

select hex(s1), s1 from t1;

112

drop table t1;

113

set LC_TIME_NAMES='en_US';

114

115

116

#

117

# Bug #2366 Wrong utf8 behaviour when data is truncated

118

#

119

set names koi8r;

120

create table t1 (s1 char(1) character set utf8);

121

insert into t1 values (_koi8r'��');

122

select s1,hex(s1),char_length(s1),octet_length(s1) from t1;

123

drop table t1;

124

125

create table t1 (s1 tinytext character set utf8);

126

insert into t1 select repeat('a',300);

127

insert into t1 select repeat('�',300);

128

insert into t1 select repeat('a�',300);

129

insert into t1 select repeat('�a',300);

130

insert into t1 select repeat('��',300);

131

select hex(s1) from t1;

132

select length(s1),char_length(s1) from t1;

133

drop table t1;

134

135

create table t1 (s1 text character set utf8);

136

insert into t1 select repeat('a',66000);

137

insert into t1 select repeat('�',66000);

138

insert into t1 select repeat('a�',66000);

139

insert into t1 select repeat('�a',66000);

140

insert into t1 select repeat('��',66000);

141

select length(s1),char_length(s1) from t1;

142

drop table t1;

94

95

96

# TODO: Bug lp:314474

97

#create table t1 (s1 tinytext);

98

#insert into t1 select repeat('a',300);

99

#insert into t1 select repeat('Ñ',300);

100

#insert into t1 select repeat('aÑ',300);

101

#insert into t1 select repeat('Ña',300);

102

#insert into t1 select repeat('ÑÑ',300);

103

#select hex(s1) from t1;

104

#select length(s1),char_length(s1) from t1;

105

#drop table t1;

106

107

#create table t1 (s1 text);

108

#insert into t1 select repeat('a',66000);

109

#insert into t1 select repeat('Ñ',66000);

110

#insert into t1 select repeat('aÑ',66000);

111

#insert into t1 select repeat('Ña',66000);

112

#insert into t1 select repeat('ÑÑ',66000);

113

#select length(s1),char_length(s1) from t1;

114

#drop table t1;

143

115

144

116

#

145

117

# Bug #2368 Multibyte charsets do not check that incoming data is well-formed

146

118

#

147

create table t1 (s1 char(10) character set utf8);

148

insert into t1 values (0x41FF);

149

select hex(s1) from t1;

150

drop table t1;

151

152

create table t1 (s1 varchar(10) character set utf8);

153

insert into t1 values (0x41FF);

154

select hex(s1) from t1;

155

drop table t1;

156

157

create table t1 (s1 text character set utf8);

119

create table t1 (s1 char(10));

120

--error 1366

121

insert into t1 values (0x41FF);

122

select hex(s1) from t1;

123

drop table t1;

124

125

create table t1 (s1 varchar(10));

126

--error 1366

127

insert into t1 values (0x41FF);

128

select hex(s1) from t1;

129

drop table t1;

130

131

create table t1 (s1 text);

132

--error 1366

158

133

insert into t1 values (0x41FF);

159

134

select hex(s1) from t1;

160

135

drop table t1;

164

139

# UTF8 breaks primary keys for cols > 333 characters

165

140

#

166

141

--error 1071

167

create table t1 (a text character set utf8, primary key(a(360)));

142

create table t1 (a text, primary key(a(360)));

168

143

169

144

170

145

#

171

146

# Bug 2959

172

147

# UTF8 charset breaks joins with mixed column/string constant

173

148

#

174

CREATE TABLE t1 ( a varchar(10) ) CHARACTER SET utf8;

149

CREATE TABLE t1 ( a varchar(10) );

175

150

INSERT INTO t1 VALUES ( 'test' );

176

151

SELECT a.a, b.a FROM t1 a, t1 b WHERE a.a = b.a;

177

152

SELECT a.a, b.a FROM t1 a, t1 b WHERE a.a = 'test' and b.a = 'test';

178

153

SELECT a.a, b.a FROM t1 a, t1 b WHERE a.a = b.a and a.a = 'test';

179

154

DROP TABLE t1;

180

155

181

create table t1 (a char(255) character set utf8);

156

create table t1 (a char(255));

182

157

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

183

158

select * from t1 where a = 'b';

184

159

select * from t1 where a = 'b' and a = 'b';

186

161

drop table t1;

187

162

188

163

#

189

# Testing regexp

190

#

191

set collation_connection=utf8_general_ci;

192

--source include/ctype_regex.inc

193

set names utf8;

194

195

#

196

# Bug #3928 regexp [[:>:]] and UTF-8

197

#

198

set names utf8;

199

200

# This should return TRUE

201

select 'вася' rlike '[[:<:]]вася[[:>:]]';

202

select 'вася ' rlike '[[:<:]]вася[[:>:]]';

203

select ' вася' rlike '[[:<:]]вася[[:>:]]';

204

select ' вася ' rlike '[[:<:]]вася[[:>:]]';

205

206

# This should return FALSE

207

select 'васяz' rlike '[[:<:]]вася[[:>:]]';

208

select 'zвася' rlike '[[:<:]]вася[[:>:]]';

209

select 'zвасяz' rlike '[[:<:]]вася[[:>:]]';

210

211

#

212

164

# Bug #4555

213

165

# ALTER TABLE crashes mysqld with enum column collated utf8_unicode_ci

214

166

#

219

171

# Customer Support Center issue # 3299

220

172

# ENUM and SET multibyte fields computed their length wronly

221

173

# when converted into a char field

222

set names utf8;

223

create table t1 (a enum('aaaa','проба') character set utf8);

224

show create table t1;

225

insert into t1 values ('проба');

226

select * from t1;

227

create table t2 select ifnull(a,a) from t1;

228

show create table t2;

229

select * from t2;

230

drop table t1;

231

drop table t2;

174

# TODO: Bug lp:314502

175

#create table t1 (a enum('aaaa', '킱'));

176

#show create table t1;

177

#insert into t1 values ('킱');

178

#select * from t1;

179

#create table t2 select ifnull(a,a) from t1;

180

#show create table t2;

181

#select * from t2;

182

#drop table t1;

183

#drop table t2;

232

184

233

185

#

234

186

# Bug 4521: unique key prefix interacts poorly with utf8

235

187

# MYISAM: keys with prefix compression, case insensitive collation.

236

188

#

237

create table t1 (c varchar(30) character set utf8, unique(c(10)));

189

create table t1 (c varchar(30), unique(c(10)));

238

190

insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z');

239

191

insert into t1 values ('aaaaaaaaaa');

240

192

--error ER_DUP_ENTRY

257

209

# InnoDB: keys with prefix compression, case insensitive collation.

258

210

#

259

211

--disable_warnings

260

create table t1 (c varchar(30) character set utf8, unique(c(10))) engine=innodb;

212

create table t1 (c varchar(30), unique(c(10))) engine=innodb;

261

213

--enable_warnings

262

214

insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z');

263

215

insert into t1 values ('aaaaaaaaaa');

279

231

# Bug 4521: unique key prefix interacts poorly with utf8

280

232

# MYISAM: fixed length keys, case insensitive collation

281

233

#

282

create table t1 (c char(3) character set utf8, unique (c(2)));

234

create table t1 (c char(3), unique (c(2))) engine=myisam;

283

235

insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z');

284

236

insert into t1 values ('a');

285

237

insert into t1 values ('aa');

289

241

insert into t1 values ('bb');

290

242

--error ER_DUP_ENTRY

291

243

insert into t1 values ('bbb');

292

insert into t1 values ('а');

293

insert into t1 values ('аа');

294

--error ER_DUP_ENTRY

295

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

296

insert into t1 values ('б');

297

insert into t1 values ('бб');

298

--error ER_DUP_ENTRY

299

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

300

insert into t1 values ('ꪪ');

301

insert into t1 values ('ꪪꪪ');

302

--error ER_DUP_ENTRY

303

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

244

insert into t1 values ('킱');

245

insert into t1 values ('킱킱');

246

--error ER_DUP_ENTRY

247

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

304

248

drop table t1;

305

249

#

306

250

# Bug 4521: unique key prefix interacts poorly with utf8

307

251

# InnoDB: fixed length keys, case insensitive collation

308

252

#

309

--disable_warnings

310

create table t1 (c char(3) character set utf8, unique (c(2))) engine=innodb;

311

--enable_warnings

253

create table t1 (c char(3), unique (c(2))) engine=innodb;

312

254

insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z');

313

255

insert into t1 values ('a');

314

256

insert into t1 values ('aa');

318

260

insert into t1 values ('bb');

319

261

--error ER_DUP_ENTRY

320

262

insert into t1 values ('bbb');

321

insert into t1 values ('а');

322

insert into t1 values ('аа');

323

--error ER_DUP_ENTRY

324

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

325

insert into t1 values ('б');

326

insert into t1 values ('бб');

327

--error ER_DUP_ENTRY

328

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

329

insert into t1 values ('ꪪ');

330

insert into t1 values ('ꪪꪪ');

331

--error ER_DUP_ENTRY

332

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

263

insert into t1 values ('킱');

264

insert into t1 values ('킱킱');

265

--error ER_DUP_ENTRY

266

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

333

267

drop table t1;

334

268

#

335

269

# Bug 4531: unique key prefix interacts poorly with utf8

336

270

# Check HEAP+HASH, case insensitive collation

337

271

#

338

272

create table t1 (

339

c char(10) character set utf8,

340

unique key a using hash (c(1))

273

c char(10),

274

unique key a using hash (c(2))

341

275

) engine=heap;

342

show create table t1;

343

insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f');

344

--error ER_DUP_ENTRY

276

insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z');

277

insert into t1 values ('a');

345

278

insert into t1 values ('aa');

346

279

--error ER_DUP_ENTRY

347

280

insert into t1 values ('aaa');

348

insert into t1 values ('б');

349

--error ER_DUP_ENTRY

350

insert into t1 values ('бб');

351

--error ER_DUP_ENTRY

352

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

281

insert into t1 values ('b');

282

insert into t1 values ('bb');

283

--error ER_DUP_ENTRY

284

insert into t1 values ('bbb');

285

insert into t1 values ('킱');

286

insert into t1 values ('킱킱');

287

--error ER_DUP_ENTRY

288

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

353

289

select c as c_all from t1 order by c;

354

290

select c as c_a from t1 where c='a';

355

select c as c_a from t1 where c='б';

291

select c as c_a from t1 where c='킱';

356

292

drop table t1;

357

293

358

294

#

360

296

# Check HEAP+BTREE, case insensitive collation

361

297

#

362

298

create table t1 (

363

c char(10) character set utf8,

364

unique key a using btree (c(1))

299

c char(10),

300

unique key a using btree (c(2))

365

301

) engine=heap;

366

302

show create table t1;

367

insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f');

368

--error ER_DUP_ENTRY

369

insert into t1 values ('aa');

370

--error ER_DUP_ENTRY

371

insert into t1 values ('aaa');

372

insert into t1 values ('б');

373

--error ER_DUP_ENTRY

374

insert into t1 values ('бб');

375

--error ER_DUP_ENTRY

376

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

377

select c as c_all from t1 order by c;

378

select c as c_a from t1 where c='a';

379

select c as c_a from t1 where c='б';

380

drop table t1;

381

382

#

383

# Bug 4531: unique key prefix interacts poorly with utf8

384

# Check BDB, case insensitive collation

385

#

386

--disable_warnings

387

create table t1 (

388

c char(10) character set utf8,

389

unique key a (c(1))

390

) engine=innodb;

391

--enable_warnings

392

insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f');

393

--error ER_DUP_ENTRY

394

insert into t1 values ('aa');

395

--error ER_DUP_ENTRY

396

insert into t1 values ('aaa');

397

insert into t1 values ('б');

398

--error ER_DUP_ENTRY

399

insert into t1 values ('бб');

400

--error ER_DUP_ENTRY

401

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

402

select c as c_all from t1 order by c;

403

select c as c_a from t1 where c='a';

404

select c as c_a from t1 where c='б';

405

drop table t1;

406

407

#

408

# Bug 4521: unique key prefix interacts poorly with utf8

409

# MYISAM: keys with prefix compression, binary collation.

410

#

411

create table t1 (c varchar(30) character set utf8 collate utf8_bin, unique(c(10)));

412

insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z');

413

insert into t1 values ('aaaaaaaaaa');

414

--error ER_DUP_ENTRY

415

insert into t1 values ('aaaaaaaaaaa');

416

--error ER_DUP_ENTRY

417

insert into t1 values ('aaaaaaaaaaaa');

418

insert into t1 values (repeat('b',20));

419

select c c1 from t1 where c='1';

420

select c c2 from t1 where c='2';

421

select c c3 from t1 where c='3';

422

select c cx from t1 where c='x';

423

select c cy from t1 where c='y';

424

select c cz from t1 where c='z';

425

select c ca10 from t1 where c='aaaaaaaaaa';

426

select c cb20 from t1 where c=repeat('b',20);

427

drop table t1;

428

429

#

430

# Bug 4521: unique key prefix interacts poorly with utf8

431

# MYISAM: fixed length keys, binary collation

432

#

433

create table t1 (c char(3) character set utf8 collate utf8_bin, unique (c(2)));

434

303

insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z');

435

304

insert into t1 values ('a');

436

305

insert into t1 values ('aa');

440

309

insert into t1 values ('bb');

441

310

--error ER_DUP_ENTRY

442

311

insert into t1 values ('bbb');

443

insert into t1 values ('а');

444

insert into t1 values ('аа');

445

--error ER_DUP_ENTRY

446

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

447

insert into t1 values ('б');

448

insert into t1 values ('бб');

449

--error ER_DUP_ENTRY

450

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

451

insert into t1 values ('ꪪ');

452

insert into t1 values ('ꪪꪪ');

453

--error ER_DUP_ENTRY

454

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

455

drop table t1;

456

457

#

458

# Bug 4531: unique key prefix interacts poorly with utf8

459

# Check HEAP+HASH, binary collation

460

#

461

create table t1 (

462

c char(10) character set utf8 collate utf8_bin,

463

unique key a using hash (c(1))

464

) engine=heap;

465

show create table t1;

466

insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f');

467

--error ER_DUP_ENTRY

468

insert into t1 values ('aa');

469

--error ER_DUP_ENTRY

470

insert into t1 values ('aaa');

471

insert into t1 values ('б');

472

--error ER_DUP_ENTRY

473

insert into t1 values ('бб');

474

--error ER_DUP_ENTRY

475

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

476

select c as c_all from t1 order by c;

477

select c as c_a from t1 where c='a';

478

select c as c_a from t1 where c='б';

479

drop table t1;

480

481

#

482

# Bug 4531: unique key prefix interacts poorly with utf8

483

# Check HEAP+BTREE, binary collation

484

#

485

create table t1 (

486

c char(10) character set utf8 collate utf8_bin,

487

unique key a using btree (c(1))

488

) engine=heap;

489

show create table t1;

490

insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f');

491

--error ER_DUP_ENTRY

492

insert into t1 values ('aa');

493

--error ER_DUP_ENTRY

494

insert into t1 values ('aaa');

495

insert into t1 values ('б');

496

--error ER_DUP_ENTRY

497

insert into t1 values ('бб');

498

--error ER_DUP_ENTRY

499

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

500

select c as c_all from t1 order by c;

501

select c as c_a from t1 where c='a';

502

select c as c_a from t1 where c='б';

503

drop table t1;

504

505

#

506

# Bug 4531: unique key prefix interacts poorly with utf8

507

# Check BDB, binary collation

508

#

509

--disable_warnings

510

create table t1 (

511

c char(10) character set utf8 collate utf8_bin,

512

unique key a (c(1))

513

) engine=innodb;

514

--enable_warnings

515

insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f');

516

--error ER_DUP_ENTRY

517

insert into t1 values ('aa');

518

--error ER_DUP_ENTRY

519

insert into t1 values ('aaa');

520

insert into t1 values ('б');

521

--error ER_DUP_ENTRY

522

insert into t1 values ('бб');

523

--error ER_DUP_ENTRY

524

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

525

select c as c_all from t1 order by c;

526

select c as c_a from t1 where c='a';

527

select c as c_a from t1 where c='б';

312

insert into t1 values ('킱');

313

insert into t1 values ('킱킱');

314

--error ER_DUP_ENTRY

315

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

316

select c as c_all from t1 order by c;

317

select c as c_a from t1 where c='a';

318

select c as c_a from t1 where c='킱';

528

319

drop table t1;

529

320

530

321

532

323

# Check MYISAM

533

324

#

534

325

create table t1 (

535

str varchar(255) character set utf8 not null,

326

str varchar(255) not null,

536

327

key str (str(2))

537

328

) engine=myisam;

538

329

INSERT INTO t1 VALUES ('str');

545

336

#

546

337

--disable_warnings

547

338

create table t1 (

548

str varchar(255) character set utf8 not null,

339

str varchar(255) not null,

549

340

key str (str(2))

550

341

) engine=innodb;

551

342

--enable_warnings

558

349

#

559

350

560

351

create table t1 (

561

str varchar(255) character set utf8 not null,

352

str varchar(255) not null,

562

353

key str using btree (str(2))

563

354

) engine=heap;

564

355

INSERT INTO t1 VALUES ('str');

570

361

#

571

362

572

363

create table t1 (

573

str varchar(255) character set utf8 not null,

364

str varchar(255) not null,

574

365

key str using hash (str(2))

575

366

) engine=heap;

576

367

INSERT INTO t1 VALUES ('str');

583

374

584

375

--disable_warnings

585

376

create table t1 (

586

str varchar(255) character set utf8 not null,

377

str varchar(255) not null,

587

378

key str (str(2))

588

379

) engine=innodb;

589

380

--enable_warnings

595

386

#

596

387

# Bug #5397: Crash with varchar binary and LIKE

597

388

#

598

CREATE TABLE t1 (a varchar(32) BINARY) CHARACTER SET utf8;

389

CREATE TABLE t1 (a varchar(32) BINARY);

599

390

INSERT INTO t1 VALUES ('test');

600

391

SELECT a FROM t1 WHERE a LIKE '%te';

601

392

DROP TABLE t1;

604

395

# Bug #5723: length(<varchar utf8 field>) returns varying results

605

396

#

606

397

--disable_warnings

607

SET NAMES utf8;

608

--disable_warnings

609

398

CREATE TABLE t1 (

610

subject varchar(255) character set utf8 collate utf8_unicode_ci,

611

p varchar(15) character set utf8

612

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

399

subject varchar(255) collate utf8_unicode_ci,

400

p varchar(15)

401

) ENGINE=InnoDB;

613

402

--enable_warnings

614

INSERT INTO t1 VALUES ('谷川俊二と申しますが、インターネット予約の会員登録をしましたところ、メールアドレスを間違えてしまい会員ＩＤが受け取ることが出来ませんでした。間違えアドレスはtani-shun@n.vodafone.ne.jpを書き込みました。どうすればよいですか？その他、住所等は間違えありません。連絡ください。よろしくお願いします。m(__)m','040312-000057');

403

INSERT INTO t1 values ('킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱','abcdefghijklmno');

615

404

INSERT INTO t1 VALUES ('aaa','bbb');

616

405

SELECT length(subject) FROM t1;

617

406

SELECT length(subject) FROM t1 ORDER BY 1;

626

415

term TEXT NOT NULL,

627

416

PRIMARY KEY(id),

628

417

INDEX(list_id, term(4))

629

) ENGINE=MYISAM CHARSET=utf8;

418

) ENGINE=MYISAM;

630

419

INSERT INTO t1 SET list_id = 1, term = "letterc";

631

420

INSERT INTO t1 SET list_id = 1, term = "letterb";

632

421

INSERT INTO t1 SET list_id = 1, term = "lettera";

641

430

#

642

431

# Bug #6043 erratic searching for diacriticals in indexed MyISAM UTF-8 table

643

432

#

644

SET NAMES latin1;

645

433

CREATE TABLE t1 (

646

434

id int NOT NULL auto_increment,

647

435

list_id int NOT NULL,

648

436

term text NOT NULL,

649

437

PRIMARY KEY(id),

650

438

INDEX(list_id, term(19))

651

) ENGINE=MyISAM CHARSET=utf8;

652

INSERT INTO t1 set list_id = 1, term = "test�test";

439

) ENGINE=MyISAM;

440

INSERT INTO t1 set list_id = 1, term = "testétest";

653

441

INSERT INTO t1 set list_id = 1, term = "testetest";

654

INSERT INTO t1 set list_id = 1, term = "test�test";

655

SELECT id, term FROM t1 where (list_id = 1) AND (term = "test�test");

442

INSERT INTO t1 set list_id = 1, term = "testètest";

443

SELECT id, term FROM t1 where (list_id = 1) AND (term = "testétest");

656

444

SELECT id, term FROM t1 where (list_id = 1) AND (term = "testetest");

657

SELECT id, term FROM t1 where (list_id = 1) AND (term = "test�test");

445

SELECT id, term FROM t1 where (list_id = 1) AND (term = "testètest");

658

446

DROP TABLE t1;

659

447

660

448

#

661

449

# Bug #6019 SELECT tries to use too short prefix index on utf8 data

662

450

#

663

set names utf8;

664

451

--disable_warnings

665

452

create table t1 (

666

453

a int primary key,

667

454

b varchar(6),

668

455

index b3(b(3))

669

) engine=innodb character set=utf8;

456

) engine=innodb;

670

457

--enable_warnings

671

458

insert into t1 values(1,'foo'),(2,'foobar');

672

459

select * from t1 where b like 'foob%';

680

467

# Test for calculate_interval_lengths() function

681

468

#

682

469

create table t1 (

683

a enum('петя','вася','анюта') character set utf8 not null default 'анюта',

684

b set('петя','вася','анюта') character set utf8 not null default 'анюта'

470

a enum('☃','☄','킱') not null default '킱'

685

471

);

686

create table t2 select concat(a,_utf8'') as a, concat(b,_utf8'')as b from t1;

472

create table t2 select concat(a,_utf8'') as a from t1;

687

473

show create table t2;

688

474

drop table t2;

689

475

drop table t1;

696

482

#

697

483

# SUBSTR with negative offset didn't work with multi-byte strings

698

484

#

699

SELECT SUBSTR('вася',-2);

700

701

702

#

703

# Bug #7730 Server crash using soundex on an utf8 table

704

#

705

create table t1 (id integer, a varchar(100) character set utf8 collate utf8_unicode_ci);

706

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

707

select * from t1 where soundex(a) = soundex('Test');

708

select * from t1 where soundex(a) = soundex('TEST');

709

select * from t1 where soundex(a) = soundex('test');

710

drop table t1;

711

712

#

713

# Bug#22638 SOUNDEX broken for international characters

714

#

715

select soundex(_utf8 0xE99885E8A788E99A8FE697B6E69BB4E696B0E79A84E696B0E997BB);

716

select hex(soundex(_utf8 0xE99885E8A788E99A8FE697B6E69BB4E696B0E79A84E696B0E997BB));

717

select soundex(_utf8 0xD091D092D093);

718

select hex(soundex(_utf8 0xD091D092D093));

719

720

721

SET collation_connection='utf8_general_ci';

485

SELECT SUBSTR('☃☄킱',-2);

486

487

722

488

-- source include/ctype_filesort.inc

723

489

-- source include/ctype_like_escape.inc

724

490

-- source include/ctype_german.inc

725

SET collation_connection='utf8_bin';

726

491

-- source include/ctype_filesort.inc

727

492

-- source include/ctype_like_escape.inc

728

493

729

#

730

# Bug #7874 CONCAT() gives wrong results mixing

731

# latin1 field and utf8 string literals

732

#

733

CREATE TABLE t1 (

734

user varchar(255) NOT NULL default ''

735

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

736

INSERT INTO t1 VALUES ('one'),('two');

737

SELECT CHARSET('a');

738

SELECT user, CONCAT('<', user, '>') AS c FROM t1;

739

DROP TABLE t1;

740

741

#

742

# Bug#8785

743

# the same problem with the above, but with nested CONCATs

744

#

745

create table t1 (f1 varchar(1) not null) default charset utf8;

746

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

747

select concat(concat(_latin1'->',f1),_latin1'<-') from t1;

748

drop table t1;

749

750

#

751

# Bug#8385: utf8_general_ci treats Cyrillic letters I and SHORT I as the same

752

#

753

select convert(_koi8r'�' using utf8) < convert(_koi8r'�' using utf8);

494

754

495

755

496

#

756

497

# Bugs#5980: NULL requires a characterset in a union

757

498

#

758

set names latin1;

759

create table t1 (a varchar(10)) character set utf8;

499

create table t1 (a varchar(10));

760

500

insert into t1 values ('test');

761

501

select ifnull(a,'') from t1;

762

502

drop table t1;

766

506

#

767

507

# Bug#9509 Optimizer: wrong result after AND with comparisons

768

508

#

769

set names utf8;

770

create table t1 (s1 char(5) character set utf8 collate utf8_lithuanian_ci);

509

create table t1 (s1 char(5) collate utf8_lithuanian_ci);

771

510

insert into t1 values ('I'),('K'),('Y');

772

511

select * from t1 where s1 < 'K' and s1 = 'Y';

773

512

select * from t1 where 'K' > s1 and s1 = 'Y';

774

513

drop table t1;

775

514

776

create table t1 (s1 char(5) character set utf8 collate utf8_czech_ci);

515

create table t1 (s1 char(5) collate utf8_czech_ci);

777

516

insert into t1 values ('c'),('d'),('h'),('ch'),('CH'),('cH'),('Ch'),('i');

778

517

select * from t1 where s1 > 'd' and s1 = 'CH';

779

518

select * from t1 where 'd' < s1 and s1 = 'CH';

784

523

#

785

524

# Bug#10714: Inserting double value into utf8 column crashes server

786

525

#

787

create table t1 (a varchar(255)) default character set utf8;

526

create table t1 (a varchar(255));

788

527

insert into t1 values (1.0);

789

528

drop table t1;

790

529

796

535

id int not null,

797

536

city varchar(20) not null,

798

537

key (city(7),id)

799

) character set=utf8;

538

);

800

539

insert into t1 values (1,'Durban North');

801

540

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

802

541

select * from t1 where city = 'Durban';

804

543

drop table t1;

805

544

806

545

#

807

# Bug #11819 CREATE TABLE with a SET DEFAULT 0 and UTF8 crashes server.

808

#

809

--error 1067

810

create table t1 (x set('A', 'B') default 0) character set utf8;

811

--error 1067

812

create table t1 (x enum('A', 'B') default 0) character set utf8;

813

814

815

546

#

816

547

# Test for bug #11167: join for utf8 varchar value longer than 255 bytes

817

548

#

818

549

819

SET NAMES UTF8;

820

821

550

CREATE TABLE t1 (

822

`id` int(20) NOT NULL auto_increment,

551

`id` int NOT NULL auto_increment,

823

552

`country` varchar(100) NOT NULL default '',

824

553

`shortcode` varchar(100) NOT NULL default '',

825

554

`operator` varchar(100) NOT NULL default '',

827

556

`keyword` varchar(160) NOT NULL default '',

828

557

`content` varchar(160) NOT NULL default '',

829

558

`second_token` varchar(160) default NULL,

830

`gateway_id` int(11) NOT NULL default '0',

559

`gateway_id` int NOT NULL default '0',

831

560

`created` datetime NOT NULL default '0000-00-00 00:00:00',

832

561

`msisdn` varchar(15) NOT NULL default '',

833

562

PRIMARY KEY (`id`),

835

564

KEY `IX_mobile_originated_message_keyword` (`keyword`),

836

565

KEY `IX_mobile_originated_message_created` (`created`),

837

566

KEY `IX_mobile_originated_message_support` (`msisdn`,`momid`,`keyword`,`gateway_id`,`created`)

838

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

567

) ENGINE=MyISAM;

839

568

840

569

INSERT INTO t1 VALUES

841

(1,'blah','464','aaa','fkc1c9ilc20x0hgae7lx6j09','ERR','ERR Имри.Афимим.Аеимимримдмримрмрирор имримримримр имридм ирбднримрфмририримрфмфмим.Ад.Д имдимримрад.Адимримримрмдиримримримр м.Дадимфшьмримд им.Адимимрн имадми','ИМРИ.АФИМИМ.АЕИМИМРИМДМРИМРМРИРОР',3,'2005-06-01 17:30:43','1234567890'),

570

(1,'blah','464','aaa','fkc1c9ilc20x0hgae7lx6j09','ERR','킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱킱','G', 3,'2005-06-01 17:30:43','1234567890'),

842

571

(2,'blah','464','aaa','haxpl2ilc20x00bj4tt2m5ti','11','11 g','G',3,'2005-06-02 22:43:10','1234567890');

843

572

844

573

--disable_warnings

845

574

CREATE TABLE t2 (

846

575

`msisdn` varchar(15) NOT NULL default '',

847

`operator_id` int(11) NOT NULL default '0',

576

`operator_id` int NOT NULL default '0',

848

577

`created` datetime NOT NULL default '0000-00-00 00:00:00',

849

578

UNIQUE KEY `PK_user` (`msisdn`)

850

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

579

) ENGINE=InnoDB;

851

580

--enable_warnings

852

581

853

582

INSERT INTO t2 VALUES ('1234567890',2,'2005-05-24 13:53:25');

860

589

# Bug#11591: CHAR column with utf8 does not work properly

861

590

# (more chars than expected)

862

591

#

863

create table t1 (a char(20) character set utf8);

864

insert into t1 values ('123456'),('андрей');

865

alter table t1 modify a char(2) character set utf8;

592

create table t1 (a char(20));

593

insert into t1 values ('12'),('킱킲');

594

alter table t1 modify a char(2);

866

595

select char_length(a), length(a), a from t1 order by a;

867

596

drop table t1;

868

597

870

599

# Bugs#12611

871

600

# ESCAPE + LIKE do not work when the escape char is a multibyte one

872

601

#

873

set names utf8;

874

select 'andre%' like 'andreñ%' escape 'ñ';

602

select 'andre%' like 'andre킱%' escape '킱';

875

603

876

604

#

877

605

# Bugs#11754: SET NAMES utf8 followed by SELECT "A\\" LIKE "A\\" returns 0

878

606

#

879

set names utf8;

880

607

select 'a\\' like 'a\\';

881

608

select 'aa\\' like 'a%\\';

882

609

883

create table t1 (a char(10), key(a)) character set utf8;

610

create table t1 (a char(10), key(a));

884

611

insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test");

885

612

select * from t1 where a like "abc%";

886

613

select * from t1 where a like concat("abc","%");

899

626

CREATE TABLE t1 (

900

627

a varchar(255) NOT NULL default '',

901

628

KEY a (a)

902

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

903

insert into t1 values (_utf8 0xe880bd);

904

insert into t1 values (_utf8 0x5b);

629

) ENGINE=MyISAM COLLATE utf8_general_ci;

630

insert into t1 values (0xe880bd);

631

insert into t1 values (0x5b);

905

632

select hex(a) from t1;

906

633

drop table t1;

907

634

908

635

#

909

636

# Bug#13751 find_in_set: Illegal mix of collations

910

637

#

911

set names 'latin1';

912

create table t1 (a varchar(255)) default charset=utf8;

638

create table t1 (a varchar(255));

913

639

select * from t1 where find_in_set('-1', a);

914

640

drop table t1;

915

641

918

644

#

919

645

create table t1 (a int);

920

646

insert into t1 values (48),(49),(50);

921

set names utf8;

922

647

select distinct char(a) from t1;

923

648

drop table t1;

924

649

925

650

#

926

651

# Bug#15581: COALESCE function truncates mutli-byte TINYTEXT values

927

652

#

928

CREATE TABLE t1 (t TINYTEXT CHARACTER SET utf8);

653

CREATE TABLE t1 (t TINYTEXT);

929

654

INSERT INTO t1 VALUES(REPEAT('a', 100));

930

655

CREATE TEMPORARY TABLE t2 SELECT COALESCE(t) AS bug FROM t1;

931

656

SELECT LENGTH(bug) FROM t2;

935

660

#

936

661

# Bug#17313: N'xxx' and _utf8'xxx' are not equivalent

937

662

#

938

CREATE TABLE t1 (item varchar(255)) default character set utf8;

939

INSERT INTO t1 VALUES (N'\\');

663

CREATE TABLE t1 (item varchar(255));

664

INSERT INTO t1 VALUES ('\\');

940

665

INSERT INTO t1 VALUES (_utf8'\\');

941

INSERT INTO t1 VALUES (N'Cote d\'Ivoire');

666

INSERT INTO t1 VALUES ('Cote d\'Ivoire');

942

667

INSERT INTO t1 VALUES (_utf8'Cote d\'Ivoire');

943

668

SELECT item FROM t1 ORDER BY item;

944

669

DROP TABLE t1;

948

673

# 254 and 256

949

674

#

950

675

951

SET NAMES utf8;

952

676

DROP TABLE IF EXISTS t1;

953

CREATE TABLE t1(a VARCHAR(255), KEY(a)) ENGINE=MyISAM DEFAULT CHARSET=utf8;

954

INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');

677

CREATE TABLE t1(a VARCHAR(255), KEY(a)) ENGINE=MyISAM;

678

INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');

955

679

INSERT INTO t1 VALUES('uu');

956

680

check table t1;

957

681

INSERT INTO t1 VALUES('uU');

965

689

INSERT INTO t1 VALUES('uuABC');

966

690

check table t1;

967

691

alter table t1 add b int;

968

INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',1);

969

INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',2);

692

INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZÌbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',1);

693

INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZÌbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',2);

970

694

delete from t1 where b=1;

971

INSERT INTO t1 VALUES('UUABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',1);

695

INSERT INTO t1 VALUES('UUABCDEFGHIGKLMNOPRSTUVWXYZÌbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',1);

972

696

check table t1;

973

INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',3);

974

INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',4);

697

INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZÌbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',3);

698

INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZÌbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',4);

975

699

delete from t1 where b=3;

976

INSERT INTO t1 VALUES('uUABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',3);

700

INSERT INTO t1 VALUES('uUABCDEFGHIGKLMNOPRSTUVWXYZÌbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',3);

977

701

check table t1;

978

702

drop table t1;

979

703

980

704

#

981

705

# Bug#20471 LIKE search fails with indexed utf8 char column

982

706

#

983

set names utf8;

984

create table t1 (s1 char(5) character set utf8);

985

insert into t1 values

986

('a'),('b'),(null),('ペテルグル'),('ü'),('Y');

987

create index it1 on t1 (s1);

988

select s1 as before_delete_general_ci from t1 where s1 like 'ペテ%';

989

delete from t1 where s1 = 'Y';

990

select s1 as after_delete_general_ci from t1 where s1 like 'ペテ%';

991

drop table t1;

992

993

set names utf8;

994

create table t1 (s1 char(5) character set utf8 collate utf8_unicode_ci);

995

insert into t1 values

996

('a'),('b'),(null),('ペテルグル'),('ü'),('Y');

997

create index it1 on t1 (s1);

998

select s1 as before_delete_unicode_ci from t1 where s1 like 'ペテ%';

999

delete from t1 where s1 = 'Y';

1000

select s1 as after_delete_unicode_ci from t1 where s1 like 'ペテ%';

1001

drop table t1;

1002

1003

set names utf8;

1004

create table t1 (s1 char(5) character set utf8 collate utf8_bin);

1005

insert into t1 values

1006

('a'),('b'),(null),('ペテルグル'),('ü'),('Y');

1007

create index it1 on t1 (s1);

1008

select s1 as before_delete_bin from t1 where s1 like 'ペテ%';

1009

delete from t1 where s1 = 'Y';

1010

select s1 as after_delete_bin from t1 where s1 like 'ペテ%';

1011

drop table t1;

1012

1013

# additional tests from duplicate bug#20744 MySQL return no result

1014

1015

set names utf8;

1016

--disable_warnings

1017

create table t1 (a varchar(30) not null primary key)

1018

engine=innodb default character set utf8 collate utf8_general_ci;

1019

--enable_warnings

1020

insert into t1 values ('あいうえおかきくけこさしすせそ');

1021

insert into t1 values ('さしすせそかきくけこあいうえお');

1022

select a as gci1 from t1 where a like 'さしすせそかきくけこあいうえお%';

1023

select a as gci2 from t1 where a like 'あいうえおかきくけこさしすせそ';

1024

drop table t1;

1025

1026

set names utf8;

1027

--disable_warnings

1028

create table t1 (a varchar(30) not null primary key)

1029

engine=innodb default character set utf8 collate utf8_unicode_ci;

1030

--enable_warnings

1031

insert into t1 values ('あいうえおかきくけこさしすせそ');

1032

insert into t1 values ('さしすせそかきくけこあいうえお');

1033

select a as uci1 from t1 where a like 'さしすせそかきくけこあいうえお%';

1034

select a as uci2 from t1 where a like 'あいうえおかきくけこさしすせそ';

1035

drop table t1;

1036

1037

set names utf8;

1038

--disable_warnings

1039

create table t1 (a varchar(30) not null primary key)

1040

engine=innodb default character set utf8 collate utf8_bin;

1041

--enable_warnings

1042

insert into t1 values ('あいうえおかきくけこさしすせそ');

1043

insert into t1 values ('さしすせそかきくけこあいうえお');

1044

select a as bin1 from t1 where a like 'さしすせそかきくけこあいうえお%';

1045

select a as bin2 from t1 where a like 'あいうえおかきくけこさしすせそ';

1046

drop table t1;

707

create table t1 (s1 char(5));

708

insert into t1 values

709

('a'),('b'),(null),('킁aaaa'),('Ã¼'),('Y');

710

create index it1 on t1 (s1);

711

select s1 as before_delete_general_ci from t1 where s1 like '킁aaa%';

712

delete from t1 where s1 = 'Y';

713

select s1 as after_delete_general_ci from t1 where s1 like '킁aaa%';

714

drop table t1;

715

716

create table t1 (s1 char(5) collate utf8_unicode_ci);

717

insert into t1 values

718

('a'),('b'),(null),('킁aaaa'),('Ã¼'),('Y');

719

create index it1 on t1 (s1);

720

select s1 as before_delete_unicode_ci from t1 where s1 like '킁aaa%';

721

delete from t1 where s1 = 'Y';

722

select s1 as after_delete_unicode_ci from t1 where s1 like '킁aaa%';

723

drop table t1;

724

725

create table t1 (s1 char(5) collate utf8_bin);

726

insert into t1 values

727

('a'),('b'),(null),('킁aaaa'),('Ã¼'),('Y');

728

create index it1 on t1 (s1);

729

select s1 as before_delete_bin from t1 where s1 like '킁aaa%';

730

delete from t1 where s1 = 'Y';

731

select s1 as after_delete_bin from t1 where s1 like '킁aaa%';

732

drop table t1;

733

1047

734

1048

735

1049

736

1051

738

# Bug#14896: Comparison with a key in a partial index over mb chararacter field

1052

739

#

1053

740

1054

SET NAMES utf8;

1055

741

CREATE TABLE t1 (id int PRIMARY KEY,

1056

742

a varchar(16) collate utf8_unicode_ci NOT NULL default '',

1057

743

b int,

1058

744

f varchar(128) default 'XXX',

1059

745

INDEX (a(4))

1060

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

746

) ENGINE=MyISAM COLLATE=utf8_unicode_ci;

1061

747

INSERT INTO t1(id, a, b) VALUES

1062

748

(1, 'cccc', 50), (2, 'cccc', 70), (3, 'cccc', 30),

1063

749

(4, 'cccc', 30), (5, 'cccc', 20), (6, 'bbbbbb', 40),

1077

763

# Bug#16674: LIKE predicate for a utf8 character set column

1078

764

#

1079

765

1080

SET NAMES utf8;

1081

1082

766

CREATE TABLE t1 (

1083

767

a CHAR(13) DEFAULT '',

1084

768

INDEX(a)

1085

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

769

) ENGINE=MyISAM COLLATE=utf8_general_ci;

1086

770

INSERT INTO t1 VALUES

1087

('Käli Käli 2-4'), ('Käli Käli 2-4'),

1088

('Käli Käli 2+4'), ('Käli Käli 2+4'),

1089

('Käli Käli 2-6'), ('Käli Käli 2-6');

771

('KÃli KÃli 2-4'), ('KÃli KÃli 2-4'),

772

('KÃli KÃli 2+4'), ('KÃli KÃli 2+4'),

773

('KÃli KÃli 2-6'), ('KÃli KÃli 2-6');

1090

774

INSERT INTO t1 SELECT * FROM t1;

1091

775

1092

CREATE TABLE t2 (

1093

a CHAR(13) DEFAULT '',

1094

INDEX(a)

1095

) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

1096

1097

INSERT INTO t2 VALUES

1098

('Kali Kali 2-4'), ('Kali Kali 2-4'),

1099

('Kali Kali 2+4'), ('Kali Kali 2+4'),

1100

('Kali Kali 2-6'), ('Kali Kali 2-6');

1101

INSERT INTO t2 SELECT * FROM t2;

1102

1103

SELECT a FROM t1 WHERE a LIKE 'Käli Käli 2+4';

1104

SELECT a FROM t2 WHERE a LIKE 'Kali Kali 2+4';

1105

1106

EXPLAIN SELECT a FROM t1 WHERE a LIKE 'Käli Käli 2+4';

1107

EXPLAIN SELECT a FROM t1 WHERE a = 'Käli Käli 2+4';

1108

EXPLAIN SELECT a FROM t2 WHERE a LIKE 'Kali Kali 2+4';

1109

EXPLAIN SELECT a FROM t2 WHERE a = 'Kali Kali 2+4';

1110

1111

DROP TABLE t1,t2;

776

SELECT a FROM t1 WHERE a LIKE 'KÃli KÃli 2+4';

777

778

EXPLAIN SELECT a FROM t1 WHERE a LIKE 'KÃli KÃli 2+4';

779

EXPLAIN SELECT a FROM t1 WHERE a = 'KÃli KÃli 2+4';

780

781

DROP TABLE t1;

1112

782

1113

783

CREATE TABLE t1 (

1114

784

a char(255) DEFAULT '',

1115

785

KEY(a(10))

1116

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

1117

INSERT INTO t1 VALUES ('Käli Käli 2-4');

1118

SELECT * FROM t1 WHERE a LIKE 'Käli Käli 2%';

1119

INSERT INTO t1 VALUES ('Käli Käli 2-4');

1120

SELECT * FROM t1 WHERE a LIKE 'Käli Käli 2%';

786

) ENGINE=MyISAM COLLATE=utf8_general_ci;

787

INSERT INTO t1 VALUES ('KÃli KÃli 2-4');

788

SELECT * FROM t1 WHERE a LIKE 'KÃli KÃli 2%';

789

INSERT INTO t1 VALUES ('KÃli KÃli 2-4');

790

SELECT * FROM t1 WHERE a LIKE 'KÃli KÃli 2%';

1121

791

DROP TABLE t1;

1122

792

1123

793

CREATE TABLE t1 (

1124

794

a char(255) DEFAULT ''

1125

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

1126

INSERT INTO t1 VALUES ('Käli Käli 2-4');

1127

INSERT INTO t1 VALUES ('Käli Käli 2-4');

1128

SELECT * FROM t1 WHERE a LIKE 'Käli Käli 2%';

795

) ENGINE=MyISAM COLLATE=utf8_general_ci;

796

INSERT INTO t1 VALUES ('KÃli KÃli 2-4');

797

INSERT INTO t1 VALUES ('KÃli KÃli 2-4');

798

SELECT * FROM t1 WHERE a LIKE 'KÃli KÃli 2%';

1129

799

ALTER TABLE t1 ADD KEY (a(10));

1130

SELECT * FROM t1 WHERE a LIKE 'Käli Käli 2%';

800

SELECT * FROM t1 WHERE a LIKE 'KÃli KÃli 2%';

1131

801

DROP TABLE t1;

1132

802

1133

803

#

1135

805

# (see bug #16674 as well)

1136

806

#

1137

807

1138

SET NAMES latin2;

1139

1140

808

CREATE TABLE t1 (

1141

id int(11) NOT NULL default '0',

1142

tid int(11) NOT NULL default '0',

809

id int NOT NULL default '0',

810

tid int NOT NULL default '0',

1143

811

val text NOT NULL,

1144

812

INDEX idx(tid, val(10))

1145

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

813

) ENGINE=MyISAM;

1146

814

1147

815

INSERT INTO t1 VALUES

1148

(40988,72,'VOLN� ADSL'),(41009,72,'VOLN� ADSL'),

1149

(41032,72,'VOLN� ADSL'),(41038,72,'VOLN� ADSL'),

1150

(41063,72,'VOLN� ADSL'),(41537,72,'VOLN� ADSL Office'),

1151

(42141,72,'VOLN� ADSL'),(42565,72,'VOLN� ADSL Combi'),

1152

(42749,72,'VOLN� ADSL'),(44205,72,'VOLN� ADSL');

816

(40988,72,'VOLNÝ ADSL'),(41009,72,'VOLNÝ ADSL'),

817

(41032,72,'VOLNÝ ADSL'),(41038,72,'VOLNÝ ADSL'),

818

(41063,72,'VOLNÝ ADSL'),(41537,72,'VOLNÝ ADSL Office'),

819

(42141,72,'VOLNÝ ADSL'),(42565,72,'VOLNÝ ADSL Combi'),

820

(42749,72,'VOLNÝ ADSL'),(44205,72,'VOLNÝ ADSL');

1153

821

1154

822

SELECT * FROM t1 WHERE tid=72 and val LIKE 'VOLNY ADSL';

1155

SELECT * FROM t1 WHERE tid=72 and val LIKE 'VOLN� ADSL';

1156

SELECT * FROM t1 WHERE tid=72 and val LIKE '%VOLN� ADSL';

823

SELECT * FROM t1 WHERE tid=72 and val LIKE 'VOLNÝ ADSL';

824

SELECT * FROM t1 WHERE tid=72 and val LIKE '%VOLNÝ ADSL';

1157

825

1158

826

ALTER TABLE t1 DROP KEY idx;

1159

827

ALTER TABLE t1 ADD KEY idx (tid,val(11));

1160

828

1161

SELECT * FROM t1 WHERE tid=72 and val LIKE 'VOLN� ADSL';

829

SELECT * FROM t1 WHERE tid=72 and val LIKE 'VOLNÝ ADSL';

1162

830

1163

831

DROP TABLE t1;

1164

832

1167

835

#

1168

836

1169

837

create table t1(a char(200) collate utf8_unicode_ci NOT NULL default '')

1170

default charset=utf8 collate=utf8_unicode_ci;

838

collate=utf8_unicode_ci;

1171

839

insert into t1 values (unhex('65')), (unhex('C3A9')), (unhex('65'));

1172

840

explain select distinct a from t1;

1173

841

select distinct a from t1;

1179

847

# Bug #20204: "order by" changes the results returned

1180

848

#

1181

849

1182

create table t1(a char(10)) default charset utf8;

850

create table t1(a char(10));

1183

851

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

1184

852

explain

1185

853

select substr(Z.a,-1), Z.a from t1 as Y join t1 as Z on Y.a=Z.a order by 1;

1192

860

# Test for bug #11484: wrong results for a DISTINCT varchar column in uft8.

1193

861

#

1194

862

1195

CREATE TABLE t1(id varchar(20) NOT NULL) DEFAULT CHARSET=utf8;

863

CREATE TABLE t1(id varchar(20) NOT NULL);

1196

864

INSERT INTO t1 VALUES ('xxx'), ('aa'), ('yyy'), ('aa');

1197

865

1198

866

SELECT id FROM t1;

1201

869

1202

870

DROP TABLE t1;

1203

871

1204

#

1205

# Bug#20095 Changing length of VARCHAR field with UTF8

1206

# collation does not truncate values

1207

#

1208

create table t1 (

1209

a varchar(26) not null

1210

) default character set utf8;

1211

insert into t1 (a) values ('abcdefghijklmnopqrstuvwxyz');

1212

select * from t1;

1213

# varchar to varchar

1214

alter table t1 change a a varchar(20) character set utf8 not null;

1215

select * from t1;

1216

# varchar to char

1217

alter table t1 change a a char(15) character set utf8 not null;

1218

select * from t1;

1219

# char to char

1220

alter table t1 change a a char(10) character set utf8 not null;

1221

select * from t1;

1222

# char to varchar

1223

alter table t1 change a a varchar(5) character set utf8 not null;

1224

select * from t1;

1225

drop table t1;

1226

1227

#

1228

# Check that do_varstring2_mb produces a warning

1229

#

1230

create table t1 (

1231

a varchar(4000) not null

1232

) default character set utf8;

1233

insert into t1 values (repeat('a',4000));

1234

alter table t1 change a a varchar(3000) character set utf8 not null;

1235

select length(a) from t1;

1236

drop table t1;

1237

1238

1239

#

1240

# Bug#10504: Character set does not support traditional mode

1241

# Bug#14146: CHAR(...USING ...) and CONVERT(CHAR(...) USING...)

1242

# produce different results

1243

#

1244

set names utf8;

1245

# correct value

1246

select hex(char(1 using utf8));

1247

select char(0xd1,0x8f using utf8);

1248

select char(0xd18f using utf8);

1249

select char(53647 using utf8);

1250

# incorrect value: return with warning

1251

select char(0xff,0x8f using utf8);

1252

select convert(char(0xff,0x8f) using utf8);

1253

# incorrect value in strict mode: return NULL with "Error" level warning

1254

set sql_mode=traditional;

1255

select char(0xff,0x8f using utf8);

1256

select char(195 using utf8);

1257

select char(196 using utf8);

1258

select char(2557 using utf8);

1259

select convert(char(0xff,0x8f) using utf8);

1260

1261

#

1262

# Check convert + char + using

1263

#

1264

select hex(convert(char(2557 using latin1) using utf8));

872

1265

873

1266

874

#

1267

875

# char() without USING returns "binary" by default, any argument is ok

1275

883

#

1276

884

# Bug#12891: UNION doesn't return DISTINCT result for multi-byte characters

1277

885

#

1278

set names utf8;

1279

create table t1 (a char(1)) default character set utf8;

1280

create table t2 (a char(1)) default character set utf8;

886

create table t1 (a char(1));

887

create table t2 (a char(1));

1281

888

insert into t1 values('a'),('a'),(0xE38182),(0xE38182);

1282

889

insert into t1 values('i'),('i'),(0xE38184),(0xE38184);

1283

890

select * from t1 union distinct select * from t2;

1284

891

drop table t1,t2;

1285

892

1286

893

1287

#

1288

# Bug#12371: executing prepared statement fails (illegal mix of collations)

1289

#

1290

set names utf8;

1291

create table t1 (a char(10), b varchar(10));

1292

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

1293

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

1294

prepare my_stmt from "select * from t1 where a=?";

1295

set @a:='bar';

1296

execute my_stmt using @a;

1297

set @a:='kostja';

1298

execute my_stmt using @a;

1299

set @a:=null;

1300

execute my_stmt using @a;

1301

drop table if exists t1;

1302

1303

1304

#

1305

# Bug#21505 Create view - illegal mix of collation for operation 'UNION'

1306

#

1307

--disable_warnings

1308

drop table if exists t1;

1309

drop view if exists v1, v2;

1310

--enable_warnings

1311

set names utf8;

1312

create table t1(col1 varchar(12) character set utf8 collate utf8_unicode_ci);

1313

insert into t1 values('t1_val');

1314

create view v1 as select 'v1_val' as col1;

1315

select coercibility(col1), collation(col1) from v1;

1316

create view v2 as select col1 from v1 union select col1 from t1;

1317

select coercibility(col1), collation(col1)from v2;

1318

drop view v1, v2;

1319

create view v1 as select 'v1_val' collate utf8_swedish_ci as col1;

1320

select coercibility(col1), collation(col1) from v1;

1321

create view v2 as select col1 from v1 union select col1 from t1;

1322

select coercibility(col1), collation(col1) from v2;

1323

drop view v1, v2;

1324

drop table t1;

1325

1326

#

1327

# Check conversion of NCHAR strings to subset (e.g. latin1).

1328

# Conversion is possible if string repertoire is ASCII.

1329

# Conversion is not possible if the string have extended characters

1330

#

1331

set names utf8;

1332

create table t1 (a varchar(10) character set latin1, b int);

1333

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

1334

select concat(a, if(b>10, N'x', N'y')) from t1;

1335

--error 1267

1336

select concat(a, if(b>10, N'æ', N'ß')) from t1;

1337

drop table t1;

1338

1339

# Conversion tests for character set introducers

1340

set names utf8;

1341

create table t1 (a varchar(10) character set latin1, b int);

1342

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

1343

select concat(a, if(b>10, _utf8'x', _utf8'y')) from t1;

1344

--error 1267

1345

select concat(a, if(b>10, _utf8'æ', _utf8'ß')) from t1;

1346

drop table t1;

1347

1348

# Conversion tests for introducer + HEX string

1349

set names utf8;

1350

create table t1 (a varchar(10) character set latin1, b int);

1351

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

1352

select concat(a, if(b>10, _utf8 0x78, _utf8 0x79)) from t1;

1353

--error 1267

1354

select concat(a, if(b>10, _utf8 0xC3A6, _utf8 0xC3AF)) from t1;

1355

drop table t1;

1356

894

1357

895

# Conversion tests for "text_literal TEXT_STRING_literal" syntax structure

1358

set names utf8;

1359

create table t1 (a varchar(10) character set latin1, b int);

896

create table t1 (a varchar(10), b int);

1360

897

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

1361

898

select concat(a, if(b>10, 'x' 'x', 'y' 'y')) from t1;

1362

--error 1267

1363

select concat(a, if(b>10, 'x' 'æ', 'y' 'ß')) from t1;

899

select concat(a, if(b>10, 'x' 'Ã¦', 'y' '킁')) from t1;

1364

900

drop table t1;

1365

901

1366

902

1370

906

#

1371

907

--disable_warnings

1372

908

CREATE TABLE t1 (

1373

colA int(11) NOT NULL,

1374

colB varchar(255) character set utf8 NOT NULL,

909

colA int NOT NULL,

910

colB varchar(255) NOT NULL,

1375

911

PRIMARY KEY (colA)

1376

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

912

) ENGINE=InnoDB;

1377

913

--enable_warnings

1378

914

INSERT INTO t1 (colA, colB) VALUES (1, 'foo'), (2, 'foo bar');

1379

915

--disable_warnings

1380

916

CREATE TABLE t2 (

1381

colA int(11) NOT NULL,

1382

colB varchar(255) character set utf8 NOT NULL,

917

colA int NOT NULL,

918

colB varchar(255) NOT NULL,

1383

919

KEY bad (colA,colB(3))

1384

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

920

) ENGINE=InnoDB;

1385

921

--enable_warnings

1386

922

INSERT INTO t2 (colA, colB) VALUES (1, 'foo'),(2, 'foo bar');

1387

923

SELECT * FROM t1 JOIN t2 ON t1.colA=t2.colA AND t1.colB=t2.colB

1393

929

# forces collation to the binary charset

1394

930

#

1395

931

1396

SELECT 'н1234567890' UNION SELECT _binary '1';

1397

SELECT 'н1234567890' UNION SELECT 1;

1398

1399

SELECT '1' UNION SELECT 'н1234567890';

1400

SELECT 1 UNION SELECT 'н1234567890';

1401

1402

CREATE TABLE t1 (c VARCHAR(11)) CHARACTER SET utf8;

1403

CREATE TABLE t2 (b CHAR(1) CHARACTER SET binary, i INT);

1404

1405

INSERT INTO t1 (c) VALUES ('н1234567890');

932

SELECT '½1234567890' UNION SELECT _binary '1';

933

SELECT '½1234567890' UNION SELECT 1;

934

935

SELECT '1' UNION SELECT '킱1234567890';

936

SELECT 1 UNION SELECT '킱1234567890';

937

938

CREATE TABLE t1 (c VARCHAR(11));

939

CREATE TABLE t2 (b CHAR(1), i INT);

940

941

INSERT INTO t1 (c) VALUES ('킱1234567890');

1406

942

INSERT INTO t2 (b, i) VALUES ('1', 1);

1407

943

1408

944

SELECT c FROM t1 UNION SELECT b FROM t2;

1413

949

1414

950

DROP TABLE t1, t2;

1415

951

1416

#

1417

# Bug#30982: CHAR(..USING..) can return a not-well-formed string

1418

# Bug #30986: Character set introducer followed by a HEX string can return bad result

1419

#

1420

set sql_mode=traditional;

1421

select hex(char(0xFF using utf8));

1422

select hex(convert(0xFF using utf8));

1423

--error ER_INVALID_CHARACTER_STRING

1424

select hex(_utf8 0x616263FF);

1425

--error ER_INVALID_CHARACTER_STRING

1426

select hex(_utf8 X'616263FF');

1427

--error ER_INVALID_CHARACTER_STRING

1428

select hex(_utf8 B'001111111111');

1429

--error ER_INVALID_CHARACTER_STRING

1430

select (_utf8 X'616263FF');

1431

set sql_mode=default;

1432

select hex(char(0xFF using utf8));

1433

select hex(convert(0xFF using utf8));

1434

--error ER_INVALID_CHARACTER_STRING

1435

select hex(_utf8 0x616263FF);

1436

--error ER_INVALID_CHARACTER_STRING

1437

select hex(_utf8 X'616263FF');

1438

--error ER_INVALID_CHARACTER_STRING

1439

select hex(_utf8 B'001111111111');

1440

--error ER_INVALID_CHARACTER_STRING

1441

select (_utf8 X'616263FF');

1442

952

1443

953

#

1444

954

# Bug#26474: Add Sinhala script (Sri Lanka) collation to MySQL

1449

959

CREATE TABLE t1 (

1450

960

predicted_order int NOT NULL,

1451

961

utf8_encoding VARCHAR(10) NOT NULL

1452

) CHARACTER SET utf8;

962

);

1453

963

INSERT INTO t1 VALUES (19, x'E0B696'), (30, x'E0B69AE0B798'), (61, x'E0B6AF'), (93, x'E0B799'), (52, x'E0B6A6'), (73, x'E0B6BBE0B78AE2808D'), (3, x'E0B686'), (56, x'E0B6AA'), (55, x'E0B6A9'), (70, x'E0B6B9'), (94, x'E0B79A'), (80, x'E0B785'), (25, x'E0B69AE0B791'), (48, x'E0B6A2'), (13, x'E0B690'), (86, x'E0B793'), (91, x'E0B79F'), (81, x'E0B786'), (79, x'E0B784'), (14, x'E0B691'), (99, x'E0B78A'), (8, x'E0B68B'), (68, x'E0B6B7'), (22, x'E0B69A'), (16, x'E0B693'), (33, x'E0B69AE0B7B3'), (38, x'E0B69AE0B79D'), (21, x'E0B683'), (11, x'E0B68E'), (77, x'E0B782'), (40, x'E0B69AE0B78A'), (101, x'E0B78AE2808DE0B6BB'), (35, x'E0B69AE0B79A'), (1, x'E0B7B4'), (9, x'E0B68C'), (96, x'E0B79C'), (6, x'E0B689'), (95, x'E0B79B'), (88, x'E0B796'), (64, x'E0B6B3'), (26, x'E0B69AE0B792'), (82, x'E0B78F'), (28, x'E0B69AE0B794'), (39, x'E0B69AE0B79E'), (97, x'E0B79D'), (2, x'E0B685'), (75, x'E0B780'), (34, x'E0B69AE0B799'), (69, x'E0B6B8'), (83, x'E0B790'), (18, x'E0B695'), (90, x'E0B7B2'), (17, x'E0B694'), (72, x'E0B6BB'), (66, x'E0B6B5'), (59, x'E0B6AD'), (44, x'E0B69E'), (15, x'E0B692'), (23, x'E0B69AE0B78F'), (65, x'E0B6B4'), (42, x'E0B69C'), (63, x'E0B6B1'), (85, x'E0B792'), (47, x'E0B6A1'), (49, x'E0B6A3'), (92, x'E0B7B3'), (78, x'E0B783'), (36, x'E0B69AE0B79B'), (4, x'E0B687'), (24, x'E0B69AE0B790'), (87, x'E0B794'), (37, x'E0B69AE0B79C'), (32, x'E0B69AE0B79F'), (29, x'E0B69AE0B796'), (43, x'E0B69D'), (62, x'E0B6B0'), (100, x'E0B78AE2808DE0B6BA'), (60, x'E0B6AE'), (45, x'E0B69F'), (12, x'E0B68F'), (46, x'E0B6A0'), (50, x'E0B6A5'), (51, x'E0B6A4'), (5, x'E0B688'), (76, x'E0B781'), (89, x'E0B798'), (74, x'E0B6BD'), (10, x'E0B68D'), (57, x'E0B6AB'), (71, x'E0B6BA'), (58, x'E0B6AC'), (27, x'E0B69AE0B793'), (54, x'E0B6A8'), (84, x'E0B791'), (31, x'E0B69AE0B7B2'), (98, x'E0B79E'), (53, x'E0B6A7'), (41, x'E0B69B'), (67, x'E0B6B6'), (7, x'E0B68A'), (20, x'E0B682');

1454

964

SELECT predicted_order, hex(utf8_encoding) FROM t1 ORDER BY utf8_encoding COLLATE utf8_sinhala_ci;

1455

965

DROP TABLE t1;

1456

966

1457

967

--echo End of 5.1 tests

1458

968

1459

set names utf8;

1460

969

--source include/weight_string.inc

1461

970

--source include/weight_string_l1.inc

1462

971

1463

set @@collation_connection=utf8_bin;

1464

972

--source include/weight_string.inc

1465

973

--source include/weight_string_l1.inc

1466

974

1469

977

#

1470

978

set max_sort_length=5;

1471

979

select @@max_sort_length;

1472

create table t1 (a varchar(128) character set utf8 collate utf8_general_ci);

980

create table t1 (a varchar(128) collate utf8_general_ci);

1473

981

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

1474

982

select * from t1 order by a;

1475

alter table t1 modify a varchar(128) character set utf8 collate utf8_bin;

983

alter table t1 modify a varchar(128) collate utf8_bin;

1476

984

select * from t1 order by a;

1477

985

drop table t1;

1478

986

set max_sort_length=default;

1486

994

CREATE TABLE t1 (

1487

995

predicted_order int NOT NULL,

1488

996

utf8_encoding VARCHAR(10) NOT NULL

1489

) CHARACTER SET utf8;

997

);

1490

998

INSERT INTO t1 VALUES (19, x'E0B696'), (30, x'E0B69AE0B798'), (61, x'E0B6AF'), (93, x'E0B799'), (52, x'E0B6A6'), (73, x'E0B6BBE0B78AE2808D'), (3, x'E0B686'), (56, x'E0B6AA'), (55, x'E0B6A9'), (70, x'E0B6B9'), (94, x'E0B79A'), (80, x'E0B785'), (25, x'E0B69AE0B791'), (48, x'E0B6A2'), (13, x'E0B690'), (86, x'E0B793'), (91, x'E0B79F'), (81, x'E0B786'), (79, x'E0B784'), (14, x'E0B691'), (99, x'E0B78A'), (8, x'E0B68B'), (68, x'E0B6B7'), (22, x'E0B69A'), (16, x'E0B693'), (33, x'E0B69AE0B7B3'), (38, x'E0B69AE0B79D'), (21, x'E0B683'), (11, x'E0B68E'), (77, x'E0B782'), (40, x'E0B69AE0B78A'), (101, x'E0B78AE2808DE0B6BB'), (35, x'E0B69AE0B79A'), (1, x'E0B7B4'), (9, x'E0B68C'), (96, x'E0B79C'), (6, x'E0B689'), (95, x'E0B79B'), (88, x'E0B796'), (64, x'E0B6B3'), (26, x'E0B69AE0B792'), (82, x'E0B78F'), (28, x'E0B69AE0B794'), (39, x'E0B69AE0B79E'), (97, x'E0B79D'), (2, x'E0B685'), (75, x'E0B780'), (34, x'E0B69AE0B799'), (69, x'E0B6B8'), (83, x'E0B790'), (18, x'E0B695'), (90, x'E0B7B2'), (17, x'E0B694'), (72, x'E0B6BB'), (66, x'E0B6B5'), (59, x'E0B6AD'), (44, x'E0B69E'), (15, x'E0B692'), (23, x'E0B69AE0B78F'), (65, x'E0B6B4'), (42, x'E0B69C'), (63, x'E0B6B1'), (85, x'E0B792'), (47, x'E0B6A1'), (49, x'E0B6A3'), (92, x'E0B7B3'), (78, x'E0B783'), (36, x'E0B69AE0B79B'), (4, x'E0B687'), (24, x'E0B69AE0B790'), (87, x'E0B794'), (37, x'E0B69AE0B79C'), (32, x'E0B69AE0B79F'), (29, x'E0B69AE0B796'), (43, x'E0B69D'), (62, x'E0B6B0'), (100, x'E0B78AE2808DE0B6BA'), (60, x'E0B6AE'), (45, x'E0B69F'), (12, x'E0B68F'), (46, x'E0B6A0'), (50, x'E0B6A5'), (51, x'E0B6A4'), (5, x'E0B688'), (76, x'E0B781'), (89, x'E0B798'), (74, x'E0B6BD'), (10, x'E0B68D'), (57, x'E0B6AB'), (71, x'E0B6BA'), (58, x'E0B6AC'), (27, x'E0B69AE0B793'), (54, x'E0B6A8'), (84, x'E0B791'), (31, x'E0B69AE0B7B2'), (98, x'E0B79E'), (53, x'E0B6A7'), (41, x'E0B69B'), (67, x'E0B6B6'), (7, x'E0B68A'), (20, x'E0B682');

1491

999

SELECT predicted_order, hex(utf8_encoding) FROM t1 ORDER BY utf8_encoding COLLATE utf8_sinhala_ci;

1492

1000

DROP TABLE t1;

1493

1001

1494

#

1495

# Check that supplementary characters are not allowed in identifiers

1496

#

1497

--error 1300

1498

CREATE DATABASE `𐀀`;

1499

--error 1300

1500

CREATE TABLE `𐀀` (a int);

1501

--error 1166

1502

CREATE TABLE test.t1 SELECT '𐀀';

1503

--error 1300

1504

CREATE USER `𐀀`;

1505

1506

--echo End of tests

1002

1003