~linuxjedi/drizzle/trunk-bug-667053

SELECT hex(LPAD(_utf32 X'000004200000042100000422000004230000042400000425000004260000042700000428000004290000042A0000042B',10,_utf32 X'000004210000042200000423'));

hex(LPAD(_utf32 X'000004200000042100000422000004230000042400000425000004260000042700000428000004290000042A0000042B',10,_utf32 X'000004210000042200000423'))

00000420000004210000042200000423000004240000042500000426000004270000042800000429

SELECT hex(RPAD(_utf32 X'0420',10,_utf32 X'0421'));

hex(RPAD(_utf32 X'0420',10,_utf32 X'0421'))

00000420000004210000042100000421000004210000042100000421000004210000042100000421

SELECT hex(RPAD(_utf32 X'0420',10,_utf32 X'0000042100000422'));

hex(RPAD(_utf32 X'0420',10,_utf32 X'0000042100000422'))

00000420000004210000042200000421000004220000042100000422000004210000042200000421

SELECT hex(RPAD(_utf32 X'0420',10,_utf32 X'000004210000042200000423'));

hex(RPAD(_utf32 X'0420',10,_utf32 X'000004210000042200000423'))

00000420000004210000042200000423000004210000042200000423000004210000042200000423

SELECT hex(RPAD(_utf32 X'000004200000042100000422000004230000042400000425000004260000042700000428000004290000042A0000042B',10,_utf32 X'000004210000042200000423'));

hex(RPAD(_utf32 X'000004200000042100000422000004230000042400000425000004260000042700000428000004290000042A0000042B',10,_utf32 X'000004210000042200000423'))

00000420000004210000042200000423000004240000042500000426000004270000042800000429

CREATE TABLE t1 SELECT

LPAD(_utf32 X'0420',10,_utf32 X'0421') l,

RPAD(_utf32 X'0420',10,_utf32 X'0421') r;

SHOW CREATE TABLE t1;

Table Create Table

t1 CREATE TABLE `t1` (

`l` varchar(10) CHARACTER SET utf32 NOT NULL DEFAULT '',

100

`r` varchar(10) CHARACTER SET utf32 NOT NULL DEFAULT ''

101

) ENGINE=MyISAM DEFAULT CHARSET=latin1

102

select hex(l), hex(r) from t1;

103

hex(l) hex(r)

104

00000421000004210000042100000421000004210000042100000421000004210000042100000420 00000420000004210000042100000421000004210000042100000421000004210000042100000421

105

DROP TABLE t1;

106

create table t1 (f1 char(30));

107

insert into t1 values ("103000"), ("22720000"), ("3401200"), ("78000");

108

select lpad(f1, 12, "-o-/") from t1;

109

lpad(f1, 12, "-o-/")

110

-o-/-o103000

111

-o-/22720000

112

-o-/-3401200

113

-o-/-o-78000

114

drop table t1;

115

SET NAMES latin1;

116

SET character_set_connection=utf32;

117

select @@collation_connection;

118

@@collation_connection

119

utf32_general_ci

120

create table t1 as select repeat(' ',10) as a union select null;

121

alter table t1 add key(a);

122

show create table t1;

123

Table Create Table

124

t1 CREATE TABLE `t1` (

125

`a` varchar(10) CHARACTER SET utf32 DEFAULT NULL,

126

KEY `a` (`a`)

127

) ENGINE=MyISAM DEFAULT CHARSET=latin1

128

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

129

explain select * from t1 where a like 'abc%';

130

id select_type table type possible_keys key key_len ref rows Extra

131

1 SIMPLE t1 range a a 43 NULL 1 Using where; Using index

132

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

133

id select_type table type possible_keys key key_len ref rows Extra

134

1 SIMPLE t1 range a a 43 NULL 1 Using where; Using index

135

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

136

137

abc

138

abcd

139

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

140

141

abc

142

abcd

143

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

144

145

abc

146

abcd

147

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

148

149

test

150

select * from t1 where a like "te_t";

151

152

test

153

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

154

155

156

abc

157

abcd

158

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

159

160

abcd

161

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

162

163

abcd

164

drop table t1;

165

select 'AA' like 'AA';

166

'AA' like 'AA'

167

168

select 'AA' like 'A%A';

169

'AA' like 'A%A'

170

171

select 'AA' like 'A%%A';

172

'AA' like 'A%%A'

173

174

select 'AA' like 'AA%';

175

'AA' like 'AA%'

176

177

select 'AA' like '%AA%';

178

'AA' like '%AA%'

179

180

select 'AA' like '%A';

181

'AA' like '%A'

182

183

select 'AA' like '%AA';

184

'AA' like '%AA'

185

186

select 'AA' like 'A%A%';

187

'AA' like 'A%A%'

188

189

select 'AA' like '_%_%';

190

'AA' like '_%_%'

191

192

select 'AA' like '%A%A';

193

'AA' like '%A%A'

194

195

select 'AAA'like 'A%A%A';

196

'AAA'like 'A%A%A'

197

198

select 'AZ' like 'AZ';

199

'AZ' like 'AZ'

200

201

select 'AZ' like 'A%Z';

202

'AZ' like 'A%Z'

203

204

select 'AZ' like 'A%%Z';

205

'AZ' like 'A%%Z'

206

207

select 'AZ' like 'AZ%';

208

'AZ' like 'AZ%'

209

210

select 'AZ' like '%AZ%';

211

'AZ' like '%AZ%'

212

213

select 'AZ' like '%Z';

214

'AZ' like '%Z'

215

216

select 'AZ' like '%AZ';

217

'AZ' like '%AZ'

218

219

select 'AZ' like 'A%Z%';

220

'AZ' like 'A%Z%'

221

222

select 'AZ' like '_%_%';

223

'AZ' like '_%_%'

224

225

select 'AZ' like '%A%Z';

226

'AZ' like '%A%Z'

227

228

select 'AZ' like 'A_';

229

'AZ' like 'A_'

230

231

select 'AZ' like '_Z';

232

'AZ' like '_Z'

233

234

select 'AMZ'like 'A%M%Z';

235

'AMZ'like 'A%M%Z'

236

237

SET NAMES utf8;

238

SET character_set_connection=utf32;

239

CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf32);

240

INSERT INTO t1 VALUES ('фыва'),('Фыва'),('фЫва'),('фыВа'),('фывА'),('ФЫВА');

241

INSERT INTO t1 VALUES ('фывапролдж'),('Фывапролдж'),('фЫвапролдж'),('фыВапролдж');

242

INSERT INTO t1 VALUES ('фывАпролдж'),('фываПролдж'),('фывапРолдж'),('фывапрОлдж');

243

INSERT INTO t1 VALUES ('фывапроЛдж'),('фывапролДж'),('фывапролдЖ'),('ФЫВАПРОЛДЖ');

244

SELECT * FROM t1 WHERE a LIKE '%фЫва%' ORDER BY BINARY a;

245

246

ФЫВАПРОЛДЖ

247

ФЫВА

248

Фыва

249

Фывапролдж

250

фЫва

251

фЫвапролдж

252

фывапролдЖ

253

фывапролДж

254

фывапроЛдж

255

фывапрОлдж

256

фывапРолдж

257

фываПролдж

258

фывАпролдж

259

фыВапролдж

260

фывапролдж

261

фывА

262

фыВа

263

фыва

264

SELECT * FROM t1 WHERE a LIKE '%фЫв%' ORDER BY BINARY a;

265

266

ФЫВАПРОЛДЖ

267

ФЫВА

268

Фыва

269

Фывапролдж

270

фЫва

271

фЫвапролдж

272

фывапролдЖ

273

фывапролДж

274

фывапроЛдж

275

фывапрОлдж

276

фывапРолдж

277

фываПролдж

278

фывАпролдж

279

фыВапролдж

280

фывапролдж

281

фывА

282

фыВа

283

фыва

284

SELECT * FROM t1 WHERE a LIKE 'фЫва%' ORDER BY BINARY a;

285

286

ФЫВАПРОЛДЖ

287

ФЫВА

288

Фыва

289

Фывапролдж

290

фЫва

291

фЫвапролдж

292

фывапролдЖ

293

фывапролДж

294

фывапроЛдж

295

фывапрОлдж

296

фывапРолдж

297

фываПролдж

298

фывАпролдж

299

фыВапролдж

300

фывапролдж

301

фывА

302

фыВа

303

фыва

304

SELECT * FROM t1 WHERE a LIKE 'фЫва%' COLLATE utf32_bin ORDER BY BINARY a;

305

306

фЫва

307

фЫвапролдж

308

DROP TABLE t1;

309

CREATE TABLE t1 (word varchar(64) NOT NULL, PRIMARY KEY (word))

310

ENGINE=MyISAM CHARACTER SET utf32;

311

INSERT INTO t1 (word) VALUES ("cat");

312

SELECT * FROM t1 WHERE word LIKE "c%";

313

word

314

cat

315

SELECT * FROM t1 WHERE word LIKE "ca_";

316

word

317

cat

318

SELECT * FROM t1 WHERE word LIKE "cat";

319

word

320

cat

321

SELECT * FROM t1 WHERE word LIKE _utf32 x'0000006300000025';

322

word

323

cat

324

SELECT * FROM t1 WHERE word LIKE _utf32 x'00000063000000610000005F';

325

word

326

cat

327

DROP TABLE t1;

328

select insert(_utf32 0x000000610000006200000063,10,2,_utf32 0x000000640000006500000066);

329

insert(_utf32 0x000000610000006200000063,10,2,_utf32 0x000000640000006500000066)

330

abc

331

select insert(_utf32 0x000000610000006200000063,1,2,_utf32 0x000000640000006500000066);

332

insert(_utf32 0x000000610000006200000063,1,2,_utf32 0x000000640000006500000066)

333

defc

334

SET NAMES latin1;

335

CREATE TABLE t1 (

336

word VARCHAR(64),

337

bar INT(11) default 0,

338

PRIMARY KEY (word))

339

ENGINE=MyISAM

340

CHARSET utf32

341

COLLATE utf32_general_ci ;

342

INSERT INTO t1 (word) VALUES ("aar");

343

INSERT INTO t1 (word) VALUES ("a");

344

INSERT INTO t1 (word) VALUES ("aardvar");

345

INSERT INTO t1 (word) VALUES ("aardvark");

346

INSERT INTO t1 (word) VALUES ("aardvara");

347

INSERT INTO t1 (word) VALUES ("aardvarz");

348

EXPLAIN SELECT * FROM t1 ORDER BY word;

349

id select_type table type possible_keys key key_len ref rows Extra

350

1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort

351

SELECT * FROM t1 ORDER BY word;

352

word bar

353

a 0

354

aar 0

355

aardvar 0

356

aardvara 0

357

aardvark 0

358

aardvarz 0

359

EXPLAIN SELECT word FROM t1 ORDER BY word;

360

id select_type table type possible_keys key key_len ref rows Extra

361

1 SIMPLE t1 index NULL PRIMARY 258 NULL 6 Using index

362

SELECT word FROM t1 ORDER by word;

363

word

364

365

aar

366

aardvar

367

aardvara

368

aardvark

369

aardvarz

370

DROP TABLE t1;

371

CREATE TABLE t1 (

372

word VARCHAR(64) ,

373

PRIMARY KEY (word))

374

ENGINE=MyISAM

375

CHARSET utf32

376

COLLATE utf32_general_ci;

377

INSERT INTO t1 (word) VALUES ("aar");

378

INSERT INTO t1 (word) VALUES ("a");

379

INSERT INTO t1 (word) VALUES ("aardvar");

380

INSERT INTO t1 (word) VALUES ("aardvark");

381

INSERT INTO t1 (word) VALUES ("aardvara");

382

INSERT INTO t1 (word) VALUES ("aardvarz");

383

EXPLAIN SELECT * FROM t1 ORDER BY WORD;

384

id select_type table type possible_keys key key_len ref rows Extra

385

1 SIMPLE t1 index NULL PRIMARY 258 NULL 6 Using index

386

SELECT * FROM t1 ORDER BY word;

387

word

388

389

aar

390

aardvar

391

aardvara

392

aardvark

393

aardvarz

394

DROP TABLE t1;

395

CREATE TABLE t1 (

396

word TEXT,

397

bar INT(11) AUTO_INCREMENT,

398

PRIMARY KEY (bar))

399

ENGINE=MyISAM

400

CHARSET utf32

401

COLLATE utf32_general_ci ;

402

INSERT INTO t1 (word) VALUES ("aar");

403

INSERT INTO t1 (word) VALUES ("a" );

404

INSERT INTO t1 (word) VALUES ("aardvar");

405

INSERT INTO t1 (word) VALUES ("aardvark");

406

INSERT INTO t1 (word) VALUES ("aardvara");

407

INSERT INTO t1 (word) VALUES ("aardvarz");

408

EXPLAIN SELECT * FROM t1 ORDER BY word;

409

id select_type table type possible_keys key key_len ref rows Extra

410

1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort

411

SELECT * FROM t1 ORDER BY word;

412

word bar

413

a 2

414

aar 1

415

aardvar 3

416

aardvara 5

417

aardvark 4

418

aardvarz 6

419

EXPLAIN SELECT word FROM t1 ORDER BY word;

420

id select_type table type possible_keys key key_len ref rows Extra

421

1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort

422

SELECT word FROM t1 ORDER BY word;

423

word

424

425

aar

426

aardvar

427

aardvara

428

aardvark

429

aardvarz

430

DROP TABLE t1;

431

SELECT hex(cast(0xAA as char character set utf32));

432

hex(cast(0xAA as char character set utf32))

433

000000AA

434

SELECT hex(convert(0xAA using utf32));

435

hex(convert(0xAA using utf32))

436

000000AA

437

CREATE TABLE t1 (a char(10) character set utf32);

438

INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111);

439

SELECT HEX(a) FROM t1;

440

HEX(a)

441

00000001

442

00000011

443

00000111

444

00001111

445

00011111

446

DROP TABLE t1;

447

CREATE TABLE t1 (a varchar(10) character set utf32);

448

INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111);

449

SELECT HEX(a) FROM t1;

450

HEX(a)

451

00000001

452

00000011

453

00000111

454

00001111

455

00011111

456

DROP TABLE t1;

457

CREATE TABLE t1 (a text character set utf32);

458

INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111);

459

SELECT HEX(a) FROM t1;

460

HEX(a)

461

00000001

462

00000011

463

00000111

464

00001111

465

00011111

466

DROP TABLE t1;

467

CREATE TABLE t1 (a mediumtext character set utf32);

468

INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111);

469

SELECT HEX(a) FROM t1;

470

HEX(a)

471

00000001

472

00000011

473

00000111

474

00001111

475

00011111

476

DROP TABLE t1;

477

CREATE TABLE t1 (a longtext character set utf32);

478

INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111);

479

SELECT HEX(a) FROM t1;

480

HEX(a)

481

00000001

482

00000011

483

00000111

484

00001111

485

00011111

486

DROP TABLE t1;

487

create table t1(a char(1)) default charset utf32;

488

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

489

alter table t1 modify a char(5);

490

select a, hex(a) from t1;

491

a hex(a)

492

a 00000061

493

b 00000062

494

c 00000063

495

drop table t1;

496

set @ivar= 1234;

497

set @str1 = 'select ?';

498

set @str2 = convert(@str1 using utf32);

499

prepare stmt1 from @str2;

500

execute stmt1 using @ivar;

501

502

1234

503

set names utf8;

504

create table t1 (a enum('x','y','z') character set utf32);

505

show create table t1;

506

Table Create Table

507

t1 CREATE TABLE `t1` (

508

`a` enum('x','y','z') CHARACTER SET utf32 DEFAULT NULL

509

) ENGINE=MyISAM DEFAULT CHARSET=latin1

510

insert into t1 values ('x');

511

insert into t1 values ('y');

512

insert into t1 values ('z');

513

select a, hex(a) from t1 order by a;

514

a hex(a)

515

x 00000078

516

y 00000079

517

z 0000007A

518

alter table t1 change a a enum('x','y','z','d','e','ä','ö','ü') character set utf32;

519

show create table t1;

520

Table Create Table

521

t1 CREATE TABLE `t1` (

522

`a` enum('x','y','z','d','e','ä','ö','ü') CHARACTER SET utf32 DEFAULT NULL

523

) ENGINE=MyISAM DEFAULT CHARSET=latin1

524

insert into t1 values ('D');

525

insert into t1 values ('E ');

526

insert into t1 values ('ä');

527

insert into t1 values ('ö');

528

insert into t1 values ('ü');

529

select a, hex(a) from t1 order by a;

530

a hex(a)

531

x 00000078

532

y 00000079

533

z 0000007A

534

d 00000064

535

e 00000065

536

ä 000000E4

537

ö 000000F6

538

ü 000000FC

539

drop table t1;

540

create table t1 (a set ('x','y','z','ä','ö','ü') character set utf32);

541

show create table t1;

542

Table Create Table

543

t1 CREATE TABLE `t1` (

544

`a` set('x','y','z','ä','ö','ü') CHARACTER SET utf32 DEFAULT NULL

545

) ENGINE=MyISAM DEFAULT CHARSET=latin1

546

insert into t1 values ('x');

547

insert into t1 values ('y');

548

insert into t1 values ('z');

549

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

550

insert into t1 values ('x,y,z,ä,ö,ü');

551

select a, hex(a) from t1 order by a;

552

a hex(a)

553

x 00000078

554

y 00000079

555

x,y 000000780000002C00000079

556

z 0000007A

557

x,y,z,ä,ö,ü 000000780000002C000000790000002C0000007A0000002C000000E40000002C000000F60000002C000000FC

558

drop table t1;

559

create table t1(a enum('a','b','c')) default character set utf32;

560

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

561

alter table t1 add b char(1);

562

show warnings;

563

Level Code Message

564

select * from t1 order by a;

565

a b

566

a NULL

567

b NULL

568

c NULL

569

drop table t1;

570

SET NAMES latin1;

571

SET collation_connection='utf32_general_ci';

572

create table t1 select repeat('a',4000) a;

573

delete from t1;

574

insert into t1 values ('a'), ('a '), ('a\t');

575

select collation(a),hex(a) from t1 order by a;

576

collation(a) hex(a)

577

utf32_general_ci 0000006100000009

578

utf32_general_ci 00000061

579

utf32_general_ci 0000006100000020

580

drop table t1;

581

select @@collation_connection;

582

@@collation_connection

583

utf32_general_ci

584

create table t1 ROW_FORMAT=DYNAMIC select repeat('a',50) as c1 ;

585

insert into t1 values('abcdef');

586

insert into t1 values('_bcdef');

587

insert into t1 values('a_cdef');

588

insert into t1 values('ab_def');

589

insert into t1 values('abc_ef');

590

insert into t1 values('abcd_f');

591

insert into t1 values('abcde_');

592

select c1 as c1u from t1 where c1 like 'ab\_def';

593

c1u

594

ab_def

595

select c1 as c2h from t1 where c1 like 'ab#_def' escape '#';

596

c2h

597

ab_def

598

drop table t1;

599

SET NAMES latin1;

600

SET collation_connection='utf32_bin';

601

create table t1 select repeat('a',4000) a;

602

delete from t1;

603

insert into t1 values ('a'), ('a '), ('a\t');

604

select collation(a),hex(a) from t1 order by a;

605

collation(a) hex(a)

606

utf32_bin 0000006100000009

607

utf32_bin 00000061

608

utf32_bin 0000006100000020

609

drop table t1;

610

select @@collation_connection;

611

@@collation_connection

612

utf32_bin

613

create table t1 ROW_FORMAT=DYNAMIC select repeat('a',50) as c1 ;

614

insert into t1 values('abcdef');

615

insert into t1 values('_bcdef');

616

insert into t1 values('a_cdef');

617

insert into t1 values('ab_def');

618

insert into t1 values('abc_ef');

619

insert into t1 values('abcd_f');

620

insert into t1 values('abcde_');

621

select c1 as c1u from t1 where c1 like 'ab\_def';

622

c1u

623

ab_def

624

select c1 as c2h from t1 where c1 like 'ab#_def' escape '#';

625

c2h

626

ab_def

627

drop table t1;

628

select hex(substr(_utf32 0x000000e4000000e500000068,1));

629

hex(substr(_utf32 0x000000e4000000e500000068,1))

630

000000E4000000E500000068

631

select hex(substr(_utf32 0x000000e4000000e500000068,2));

632

hex(substr(_utf32 0x000000e4000000e500000068,2))

633

000000E500000068

634

select hex(substr(_utf32 0x000000e4000000e500000068,3));

635

hex(substr(_utf32 0x000000e4000000e500000068,3))

636

00000068

637

select hex(substr(_utf32 0x000000e4000000e500000068,-1));

638

hex(substr(_utf32 0x000000e4000000e500000068,-1))

639

00000068

640

select hex(substr(_utf32 0x000000e4000000e500000068,-2));

641

hex(substr(_utf32 0x000000e4000000e500000068,-2))

642

000000E500000068

643

select hex(substr(_utf32 0x000000e4000000e500000068,-3));

644

hex(substr(_utf32 0x000000e4000000e500000068,-3))

645

000000E4000000E500000068

646

CREATE TABLE t1 (

647

a varchar(250) NOT NULL default '',

648

KEY a (a)

649

) ENGINE=MyISAM DEFAULT CHARSET=utf32 COLLATE utf32_general_ci;

650

insert into t1 values (0x803d);

651

insert into t1 values (0x005b);

652

select hex(a) from t1;

653

hex(a)

654

0000005B

655

0000803D

656

drop table t1;

657

create table t1 (utext varchar(20) character set utf32);

658

insert into t1 values ("lily");

659

insert into t1 values ("river");

660

prepare stmt from 'select utext from t1 where utext like ?';

661

set @param1='%%';

662

execute stmt using @param1;

663

utext

664

lily

665

river

666

execute stmt using @param1;

667

utext

668

lily

669

river

670

select utext from t1 where utext like '%%';

671

utext

672

lily

673

river

674

drop table t1;

675

deallocate prepare stmt;

676

create table t1 (

677

a char(10) character set utf32 not null,

678

index a (a)

679

) engine=myisam;

680

insert into t1 values (repeat(0x0000201f, 10));

681

insert into t1 values (repeat(0x00002020, 10));

682

insert into t1 values (repeat(0x00002021, 10));

683

explain select hex(a) from t1 order by a;

684

id select_type table type possible_keys key key_len ref rows Extra

685

1 SIMPLE t1 index NULL a 40 NULL 3 Using index

686

select hex(a) from t1 order by a;

687

hex(a)

688

0000201F0000201F0000201F0000201F0000201F0000201F0000201F0000201F0000201F0000201F

689

00002020000020200000202000002020000020200000202000002020000020200000202000002020

690

00002021000020210000202100002021000020210000202100002021000020210000202100002021

691

alter table t1 drop index a;

692

select hex(a) from t1 order by a;

693

hex(a)

694

0000201F0000201F0000201F0000201F0000201F0000201F0000201F0000201F0000201F0000201F

695

00002020000020200000202000002020000020200000202000002020000020200000202000002020

696

00002021000020210000202100002021000020210000202100002021000020210000202100002021

697

drop table t1;

698

CREATE TABLE t1 (

699

status enum('active','passive') character set utf32 collate utf32_general_ci

700

NOT NULL default 'passive'

701

);

702

SHOW CREATE TABLE t1;

703

Table Create Table

704

t1 CREATE TABLE `t1` (

705

`status` enum('active','passive') CHARACTER SET utf32 NOT NULL DEFAULT 'passive'

706

) ENGINE=MyISAM DEFAULT CHARSET=latin1

707

ALTER TABLE t1 ADD a int NOT NULL AFTER status;

708

SHOW CREATE TABLE t1;

709

Table Create Table

710

t1 CREATE TABLE `t1` (

711

`status` enum('active','passive') CHARACTER SET utf32 NOT NULL DEFAULT 'passive',

712

`a` int(11) NOT NULL

713

) ENGINE=MyISAM DEFAULT CHARSET=latin1

714

DROP TABLE t1;

715

End of 4.1 tests

716

CREATE TABLE t1 (a varchar(64) character set utf32, b decimal(10,3));

717

INSERT INTO t1 VALUES ("1.1", 0), ("2.1", 0);

718

update t1 set b=a;

719

SELECT *, hex(a) FROM t1;

720

a b hex(a)

721

1.1 1.100 000000310000002E00000031

722

2.1 2.100 000000320000002E00000031

723

DROP TABLE t1;

724

create table t1 (utext varchar(20) character set utf32);

725

insert into t1 values ("lily");

726

insert into t1 values ("river");

727

prepare stmt from 'select utext from t1 where utext like ?';

728

set @param1='%%';

729

execute stmt using @param1;

730

utext

731

lily

732

river

733

execute stmt using @param1;

734

utext

735

lily

736

river

737

select utext from t1 where utext like '%%';

738

utext

739

lily

740

river

741

drop table t1;

742

deallocate prepare stmt;

743

set names latin1;

744

set character_set_connection=utf32;

745

select soundex(''),soundex('he'),soundex('hello all folks'),soundex('#3556 in bugdb');

746

soundex('') soundex('he') soundex('hello all folks') soundex('#3556 in bugdb')

747

H000 H4142 I51231

748

select hex(soundex('')),hex(soundex('he')),hex(soundex('hello all folks')),hex(soundex('#3556 in bugdb'));

749

hex(soundex('')) hex(soundex('he')) hex(soundex('hello all folks')) hex(soundex('#3556 in bugdb'))

750

00000048000000300000003000000030 0000004800000034000000310000003400000032 000000490000003500000031000000320000003300000031

751

select 'mood' sounds like 'mud';

752

'mood' sounds like 'mud'

753

754

select hex(soundex(_utf32 0x000004100000041100000412));

755

hex(soundex(_utf32 0x000004100000041100000412))

756

00000410000000300000003000000030

757

select hex(soundex(_utf32 0x000000BF000000C0));

758

hex(soundex(_utf32 0x000000BF000000C0))

759

000000C0000000300000003000000030

760

set names latin1;

761

create table t1(a blob, b text charset utf32);

762

select data_type, character_octet_length, character_maximum_length

763

from information_schema.columns where table_name='t1';

764

data_type character_octet_length character_maximum_length

765

blob 65535 65535

766

text 65535 16383

767

drop table t1;

768

End of 5.0 tests

769

set collation_connection=utf32_general_ci;

770

select @@collation_connection;

771

@@collation_connection

772

utf32_general_ci

773

select hex(weight_string('a'));

774

hex(weight_string('a'))

775

0041

776

select hex(weight_string('A'));

777

hex(weight_string('A'))

778

0041

779

select hex(weight_string('abc'));

780

hex(weight_string('abc'))

781

004100420043

782

select hex(weight_string('abc' as char(2)));

783

hex(weight_string('abc' as char(2)))

784

00410042

785

select hex(weight_string('abc' as char(3)));

786

hex(weight_string('abc' as char(3)))

787

004100420043

788

select hex(weight_string('abc' as char(5)));

789

hex(weight_string('abc' as char(5)))

790

00410042004300200020

791

select hex(weight_string(_utf32 0x10000));

792

hex(weight_string(_utf32 0x10000))

793

FFFD

794

select hex(weight_string(_utf32 0x10001));

795

hex(weight_string(_utf32 0x10001))

796

FFFD

797

select @@collation_connection;

798

@@collation_connection

799

utf32_general_ci

800

select hex(weight_string('a' LEVEL 1));

801

hex(weight_string('a' LEVEL 1))

802

0041

803

select hex(weight_string('A' LEVEL 1));

804

hex(weight_string('A' LEVEL 1))

805

0041

806

select hex(weight_string('abc' LEVEL 1));

807

hex(weight_string('abc' LEVEL 1))

808

004100420043

809

select hex(weight_string('abc' as char(2) LEVEL 1));

810

hex(weight_string('abc' as char(2) LEVEL 1))

811

00410042

812

select hex(weight_string('abc' as char(3) LEVEL 1));

813

hex(weight_string('abc' as char(3) LEVEL 1))

814

004100420043

815

select hex(weight_string('abc' as char(5) LEVEL 1));

816

hex(weight_string('abc' as char(5) LEVEL 1))

817

00410042004300200020

818

select hex(weight_string('abc' as char(5) LEVEL 1 REVERSE));

819

hex(weight_string('abc' as char(5) LEVEL 1 REVERSE))

820

20002000430042004100

821

select hex(weight_string('abc' as char(5) LEVEL 1 DESC));

822

hex(weight_string('abc' as char(5) LEVEL 1 DESC))

823

FFBEFFBDFFBCFFDFFFDF

824

select hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE));

825

hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE))

826

DFFFDFFFBCFFBDFFBEFF

827

set collation_connection=utf32_bin;

828

select @@collation_connection;

829

@@collation_connection

830

utf32_bin

831

select hex(weight_string('a'));

832

hex(weight_string('a'))

833

0061

834

select hex(weight_string('A'));

835

hex(weight_string('A'))

836

0041

837

select hex(weight_string('abc'));

838

hex(weight_string('abc'))

839

006100620063

840

select hex(weight_string('abc' as char(2)));

841

hex(weight_string('abc' as char(2)))

842

00610062

843

select hex(weight_string('abc' as char(3)));

844

hex(weight_string('abc' as char(3)))

845

006100620063

846

select hex(weight_string('abc' as char(5)));

847

hex(weight_string('abc' as char(5)))

848

00610062006300200020

849

select @@collation_connection;

850

@@collation_connection

851

utf32_bin

852

select hex(weight_string('a' LEVEL 1));

853

hex(weight_string('a' LEVEL 1))

854

0061

855

select hex(weight_string('A' LEVEL 1));

856

hex(weight_string('A' LEVEL 1))

857

0041

858

select hex(weight_string('abc' LEVEL 1));

859

hex(weight_string('abc' LEVEL 1))

860

006100620063

861

select hex(weight_string('abc' as char(2) LEVEL 1));

862

hex(weight_string('abc' as char(2) LEVEL 1))

863

00610062

864

select hex(weight_string('abc' as char(3) LEVEL 1));

865

hex(weight_string('abc' as char(3) LEVEL 1))

866

006100620063

867

select hex(weight_string('abc' as char(5) LEVEL 1));

868

hex(weight_string('abc' as char(5) LEVEL 1))

869

00610062006300200020

870

select hex(weight_string('abc' as char(5) LEVEL 1 REVERSE));

871

hex(weight_string('abc' as char(5) LEVEL 1 REVERSE))

872

20002000630062006100

873

select hex(weight_string('abc' as char(5) LEVEL 1 DESC));

874

hex(weight_string('abc' as char(5) LEVEL 1 DESC))

875

FF9EFF9DFF9CFFDFFFDF

876

select hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE));

877

hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE))

878

DFFFDFFF9CFF9DFF9EFF

879

set names latin1;

880

set collation_connection=utf32_general_ci;

881

select position('bb' in 'abba');

882

position('bb' in 'abba')

883

884

create table t1 (a varchar(10) character set utf32) engine=heap;

885

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

886

select * from t1 where a='a' order by binary a;

887

888

889

890

select hex(min(binary a)),count(*) from t1 group by a;

891

hex(min(binary a)) count(*)

892

00000041 2

893

00000042 2

894

drop table t1;

895

select char_length('abcd'), octet_length('abcd');

896

char_length('abcd') octet_length('abcd')

897

4 16

898

select left('abcd',2);

899

left('abcd',2)

900

901

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

902

insert into t1 values (_utf32 0x0010FFFF);

903

insert into t1 values (_utf32 0x00110000);

904

ERROR HY000: Invalid utf32 character string: '001100'

905

insert into t1 values (_utf32 0x00110101);

906

ERROR HY000: Invalid utf32 character string: '001101'

907

insert into t1 values (_utf32 0x01000101);

908

ERROR HY000: Invalid utf32 character string: '010001'

909

insert into t1 values (_utf32 0x11000101);

910

ERROR HY000: Invalid utf32 character string: '110001'

911

select hex(a) from t1;

912

hex(a)

913

0010FFFF

914

drop table t1;

915

create table t1 (a char(10)) character set utf32;

916

insert into t1 values ('a ');

917

select hex(a) from t1;

918

hex(a)

919

00000061

920

drop table t1;

921

select upper('abcd'), lower('ABCD');

922

upper('abcd') lower('ABCD')

923

ABCD abcd

924

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

925

insert into t1 values (123456);

926

select a, hex(a) from t1;

927

a hex(a)

928

123456 000000310000003200000033000000340000003500000036

929

drop table t1;

930

select hex(soundex('a'));

931

hex(soundex('a'))

932

00000041000000300000003000000030

933

create table t1 (a enum ('a','b','c')) character set utf32;

934

insert into t1 values ('1');

935

select * from t1;

936

937

938

drop table t1;

939

set names latin1;

940

select hex(conv(convert('123' using utf32), -10, 16));

941

hex(conv(convert('123' using utf32), -10, 16))

942

3742

943

select hex(conv(convert('123' using utf32), 10, 16));

944

hex(conv(convert('123' using utf32), 10, 16))

945

3742

946

set names latin1;

947

set character_set_connection=utf32;

948

select 1.1 + '1.2';

949

1.1 + '1.2'

950

2.3

951

select 1.1 + '1.2xxx';

952

1.1 + '1.2xxx'

953

2.3

954

Warnings:

955

Warning 1292 Truncated incorrect DOUBLE value: ''

956

select left('aaa','1');

957

left('aaa','1')

958

959

create table t1 (a int);

960

insert into t1 values ('-1234.1e2');

961

insert into t1 values ('-1234.1e2xxxx');

962

Warnings:

963

Warning 1265 Data truncated for column 'a' at row 1

964

insert into t1 values ('-1234.1e2 ');

965

select * from t1;

966

967

-123410

968

-123410

969

-123410

970

drop table t1;

971

create table t1 (a int);

972

insert into t1 values ('1 ');

973

insert into t1 values ('1 x');

974

Warnings:

975

Warning 1265 Data truncated for column 'a' at row 1

976

select * from t1;

977

978

979

980

drop table t1;

981

create table t1 (a varchar(17000) character set utf32);

982

Warnings:

983

Note 1246 Converting column 'a' from VARCHAR to TEXT

984

show create table t1;

985

Table Create Table

986

t1 CREATE TABLE `t1` (

987

`a` mediumtext CHARACTER SET utf32

988

) ENGINE=MyISAM DEFAULT CHARSET=latin1

989

drop table t1;

990

create table t1 (a varchar(250) character set utf32 primary key);

991

show create table t1;

992

Table Create Table

993

t1 CREATE TABLE `t1` (

994

`a` varchar(250) CHARACTER SET utf32 NOT NULL,

995

PRIMARY KEY (`a`)

996

) ENGINE=MyISAM DEFAULT CHARSET=latin1

997

drop table t1;

998

create table t1 (a varchar(334) character set utf32 primary key);

999

ERROR 42000: Specified key was too long; max key length is 1332 bytes

1000

create table t1 (a varchar(333) character set utf32, key(a));

1001

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

1002

flush tables;

1003

check table t1;

1004

Table Op Msg_type Msg_text

1005

test.t1 check status OK

1006

drop table t1;

1007

set collation_connection=utf32_general_ci;

1008

drop table if exists t1;

1009

create table t1 as

1010

select repeat(' ', 64) as s1, repeat(' ',64) as s2

1011

union

1012

select null, null;

1013

show create table t1;

1014

Table Create Table

1015

t1 CREATE TABLE `t1` (

1016

`s1` varchar(64) CHARACTER SET utf32 DEFAULT NULL,

1017

`s2` varchar(64) CHARACTER SET utf32 DEFAULT NULL

1018

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1019

delete from t1;

1020

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

1021

insert into t1 values('aaa|qqq','qqq');

1022

insert into t1 values('gheis','^[^a-dXYZ]+$');

1023

insert into t1 values('aab','^aa?b');

1024

insert into t1 values('Baaan','^Ba*n');

1025

insert into t1 values('aaa','qqq|aaa');

1026

insert into t1 values('qqq','qqq|aaa');

1027

insert into t1 values('bbb','qqq|aaa');

1028

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

1029

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

1030

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

1031

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

1032

insert into t1 values(null,null);

1033

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

1034

select HIGH_PRIORITY s1 regexp s2 from t1;

1035

s1 regexp s2

1036

1037

1038

1039

1040

1041

1042

1043

1044

1045

1046

NULL

1047

NULL

1048

NULL

1049

NULL

1050

drop table t1;

1051

set names latin1;

Older »