~drizzle-trunk/drizzle/development

create table `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` (aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa int);

--error 1059

create table a (`aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` int);

# Some wrong defaults, so these creates should fail too (Bug #5902)

--error 1067

create table t1 (a datetime default now());

--error 1294

create table t1 (a datetime on update now());

--error 1067

create table t1 (a int default 100 auto_increment);

# TODO: Should this really fail? What's wrong with default 1000 ???

#--error 1067

#create table t1 (a int default 1000);

--error 1067

create table t1 (a varchar(5) default 'abcdef');

create table t1 (a varchar(5) default 'abcde');

insert into t1 values();

select * from t1;

--error 1067

alter table t1 alter column a set default 'abcdef';

drop table t1;

# test of dummy table names

create table 1ea10 (1a20 int,1e int);

insert into 1ea10 values(1,1);

select 1ea10.1a20,1e+ 1e+10 from 1ea10;

drop table 1ea10;

create table t1 (t1.index int);

drop table t1;

# Test that we get warning for this

drop database if exists mysqltest;

create database mysqltest;

create table mysqltest.$test1 (a$1 int, $b int, c$ int);

insert into mysqltest.$test1 values (1,2,3);

select a$1, $b, c$ from mysqltest.$test1;

create table mysqltest.test2$ (a int);

drop table mysqltest.test2$;

drop database mysqltest;

--error 1103

create table `` (a int);

--error 1103

100

drop table if exists ``;

101

--error 1166

102

create table t1 (`` int);

103

--error 1280

104

create table t1 (i int, index `` (i));

105

106

107

# Test of CREATE ... SELECT with indexes

108

109

110

create table t1 (a int auto_increment not null primary key, B CHAR(20));

111

insert into t1 (b) values ("hello"),("my"),("world");

112

create table t2 (key (b)) select * from t1;

113

explain select * from t2 where b="world";

114

select * from t2 where b="world";

115

drop table t1,t2;

116

117

118

# Test types after CREATE ... SELECT

119

120

121

create table t1(x varchar(50) );

122

create table t2 select x from t1 where 1=2;

123

describe t1;

124

describe t2;

125

drop table t2;

126

create table t2 select now() as a , curdate() as c , 1+1 as d , 1.0 + 1 as e , 33333333333333333 + 3 as f;

127

describe t2;

128

drop table t2;

129

create table t2 select CAST("2001-12-29" AS DATE) as d, CAST("2001-12-29 20:45:11" AS DATETIME) as dt;

130

describe t2;

131

drop table t1,t2;

132

133

134

# Test of CREATE ... SELECT with duplicate fields

135

136

137

create table t1 (a int);

138

create table t2 (a int) select * from t1;

139

describe t1;

140

describe t2;

141

drop table if exists t2;

142

--error 1060

143

create table t2 (a int, a float) select * from t1;

144

drop table if exists t2;

145

--error 1060

146

create table t2 (a int) select a as b, a+1 as b from t1;

147

drop table if exists t2;

148

--error 1060

149

create table t2 (b int) select a as b, a+1 as b from t1;

150

drop table if exists t1,t2;

151

152

153

# Test CREATE ... SELECT when insert fails

154

155

156

CREATE TABLE t1 (a int not null);

157

INSERT INTO t1 values (1),(2),(1);

158

--error ER_DUP_ENTRY

159

CREATE TABLE t2 (primary key(a)) SELECT * FROM t1;

160

--error 1146

161

SELECT * from t2;

162

DROP TABLE t1;

163

DROP TABLE IF EXISTS t2;

164

165

166

# Test of primary key with 32 index

167

168

169

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

170

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

171

show create table t1;

172

drop table t1;

173

create table t1 select if(1,'1','0'), month("2002-08-02");

174

drop table t1;

175

create table t1 select if('2002'='2002','Y','N');

176

select * from t1;

177

drop table if exists t1;

178

179

180

# Test default table type

181

182

SET SESSION storage_engine="MEMORY";

183

SELECT @@storage_engine;

184

CREATE TEMPORARY TABLE t1 (a int not null);

185

show create table t1;

186

drop table t1;

187

--error 1286

188

SET SESSION storage_engine="gemini";

189

SELECT @@storage_engine;

190

CREATE TEMPORARY TABLE t1 (a int not null);

191

show create table t1;

192

SET SESSION storage_engine=default;

193

drop table t1;

194

195

196

197

# ISO requires that primary keys are implicitly NOT NULL

198

199

create table t1 ( k1 varchar(2), k2 int, primary key(k1,k2));

200

insert into t1 values ("a", 1), ("b", 2);

201

--error 1048

202

insert into t1 values ("c", NULL);

203

--error 1048

204

insert into t1 values (NULL, 3);

205

--error 1048

206

insert into t1 values (NULL, NULL);

207

drop table t1;

208

209

210

# Bug # 801

211

212

213

create table t1 select x'4132';

214

drop table t1;

215

216

217

# bug #1434

218

219

220

create table t1 select 1,2,3;

221

--error 1364

222

create table if not exists t1 select 1,2;

223

--error 1136

224

create table if not exists t1 select 1,2,3,4;

225

--error 1364

226

create table if not exists t1 select 1;

227

select * from t1;

228

drop table t1;

229

230

231

# Test create table if not exists with duplicate key error

232

233

234

flush status;

235

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

236

insert into t1 values (1,1);

237

# TODO: BUG here, this is filling in right to left for some reason

238

#create table if not exists t1 select 2;

239

select * from t1;

240

create table if not exists t1 select 3 as 'a',4 as 'b';

241

--error ER_DUP_ENTRY

242

create table if not exists t1 select 3 as 'a',3 as 'b';

243

show warnings;

244

show status like "Opened_tables";

245

select * from t1;

246

drop table t1;

247

248

249

# Test for Bug #2985

250

# "Table truncated when creating another table name with Spaces"

251

252

253

--error 1103

254

create table `t1 `(a int);

255

--error 1102

256

create database `db1 `;

257

--error 1166

258

create table t1(`a ` int);

259

260

261

# Test for Bug #3481

262

# "Parser permits multiple commas without syntax error"

263

264

265

--error 1064

266

create table t1 (a int,);

267

--error 1064

268

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

269

--error 1064

270

create table t1 (,b int);

271

272

273

# Test create with foreign keys

274

275

276

create table t1 (a int, key(a));

277

create table t2 (b int, foreign key(b) references t1(a), key(b));

278

--error 1217

279

drop table if exists t1,t2;

280

drop table if exists t2,t1;

281

282

283

# Test for CREATE TABLE .. LIKE ..

284

285

286

create table t1(id int not null, name char(20));

287

insert into t1 values(10,'mysql'),(20,'monty- the creator');

288

create table t2(id int not null);

289

insert into t2 values(10),(20);

290

create table t3 like t1;

291

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

292

show create table t3;

293

select * from t3;

294

# Disable PS becasue of @@warning_count

295

create table if not exists t3 like t1;

296

--disable_ps_protocol

297

select @@warning_count;

298

--enable_ps_protocol

299

create temporary table t3 like t2;

300

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

301

show create table t3;

302

select * from t3;

303

drop table t3;

304

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

305

show create table t3;

306

select * from t3;

307

drop table t2, t3;

308

create database mysqltest;

309

create table mysqltest.t3 like t1;

310

create temporary table t3 like mysqltest.t3;

311

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

312

show create table t3;

313

create table t2 like t3;

314

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

315

show create table t2;

316

select * from t2;

317

create table t3 like t1;

318

--error 1050

319

create table t3 like mysqltest.t3;

320

--error 1049

321

create table non_existing_database.t1 like t1;

322

--error ER_NO_SUCH_TABLE

323

create table t3 like non_existing_table;

324

--error 1050

325

create temporary table t3 like t1;

326

drop table t1, t2, t3;

327

drop table t3;

328

drop database mysqltest;

329

330

331

# Test default table type

332

333

SET SESSION storage_engine="MEMORY";

334

SELECT @@storage_engine;

335

CREATE TEMPORARY TABLE t1 (a int not null);

336

show create table t1;

337

drop table t1;

338

--error 1286

339

SET SESSION storage_engine="gemini";

340

SELECT @@storage_engine;

341

CREATE TEMPORARY TABLE t1 (a int not null);

342

show create table t1;

343

SET SESSION storage_engine=default;

344

drop table t1;

345

346

347

# Test types of data for create select with functions

348

349

350

create table t1(a int,b int,c int,d date,e char,f datetime,h blob);

351

insert into t1(a)values(1);

352

insert into t1(a,b,c,d,e,f,h)

353

values(2,-2,2,'1825-12-14','a','2003-01-01 03:02:01','binary data');

354

select * from t1;

355

select a,

356

ifnull(b,-7) as b,

357

ifnull(c,7) as c,

358

ifnull(d,cast('2000-01-01' as date)) as d,

359

ifnull(e,cast('b' as char)) as e,

360

ifnull(f,cast('2000-01-01' as datetime)) as f,

361

ifnull(h,cast('yet another binary data' as binary)) as h

362

from t1;

363

364

create table t2

365

select

366

367

ifnull(b,-7) as b,

368

ifnull(c,7) as c,

369

ifnull(d,cast('2000-01-01' as date)) as d,

370

ifnull(e,cast('b' as char)) as e,

371

ifnull(f,cast('2000-01-01' as datetime)) as f,

372

ifnull(h,cast('yet another binary data' as binary)) as h

373

from t1;

374

explain t2;

375

select * from t2;

376

drop table t1, t2;

377

378

create table t1 (a int, b int, d int, e bigint, f float(3,2), g double(4,3), h decimal(5,4), j date, k timestamp, l datetime, m enum('a','b'), o char(10));

379

create table t2 select ifnull(a,a), ifnull(b,b), ifnull(d,d), ifnull(e,e), ifnull(f,f), ifnull(g,g), ifnull(h,h), ifnull(j,j), ifnull(k,k), ifnull(l,l), ifnull(m,m), ifnull(o,o) from t1;

380

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

381

show create table t2;

382

drop table t1,t2;

383

384

385

# Test of default()

386

387

create table t1(str varchar(10) default 'def',strnull varchar(10),intg int default '10',rel double default '3.14');

388

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

389

describe t1;

390

create table t2 select default(str) as str, default(strnull) as strnull, default(intg) as intg, default(rel) as rel from t1;

391

describe t2;

392

drop table t1, t2;

393

394

395

# Bug #2075

396

397

398

create table t1(name varchar(10), age int default -1);

399

describe t1;

400

create table t2(name varchar(10), age int default - 1);

401

describe t2;

402

drop table t1, t2;

403

404

405

# test for bug #1427 "enum allows duplicate values in the list"

406

407

408

create table t1(cenum enum('a'));

409

--error 1291

410

create table t2(cenum enum('a','a'));

411

--error 1291

412

create table t3(cenum enum('a','A','a','c','c'));

413

drop table t1;

414

415

416

# Bug #1209

417

418

419

create database mysqltest;

420

use mysqltest;

421

select database();

422

drop database mysqltest;

423

select database();

424

use test;

425

426

427

# Test for Bug 856 'Naming a key "Primary" causes trouble'

428

429

430

## TODO: Is this really a bug? It works in Drizzle. Should it?

431

#--error 1280

432

#create table t1 (a int, index `primary` (a));

433

#--error 1280

434

#create table t1 (a int, index `PRIMARY` (a));

435

436

#create table t1 (`primary` int, index(`primary`));

437

#--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

438

#show create table t1;

439

#create table t2 (`PRIMARY` int, index(`PRIMARY`));

440

#--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

441

#show create table t2;

442

443

#create table t3 (a int);

444

#--error 1280

445

#alter table t3 add index `primary` (a);

446

#--error 1280

447

#alter table t3 add index `PRIMARY` (a);

448

449

#create table t4 (`primary` int);

450

#alter table t4 add index(`primary`);

451

#--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

452

#show create table t4;

453

#create table t5 (`PRIMARY` int);

454

#alter table t5 add index(`PRIMARY`);

455

#--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

456

#show create table t5;

457

458

#drop table t1, t2, t3, t4, t5;

459

460

461

# bug #3266 TEXT in CREATE TABLE SELECT

462

463

464

CREATE TABLE t1(id varchar(10) NOT NULL PRIMARY KEY, dsc longtext);

465

INSERT INTO t1 VALUES ('5000000001', NULL),('5000000003', 'Test'),('5000000004', NULL);

466

CREATE TABLE t2(id varchar(15) NOT NULL, proc varchar(100) NOT NULL, runID varchar(16) NOT NULL, start datetime NOT NULL, PRIMARY KEY (id,proc,runID,start));

467

468

INSERT INTO t2 VALUES ('5000000001', 'proc01', '20031029090650', '2003-10-29 13:38:40'),('5000000001', 'proc02', '20031029090650', '2003-10-29 13:38:51'),('5000000001', 'proc03', '20031029090650', '2003-10-29 13:38:11'),('5000000002', 'proc09', '20031024013310', '2003-10-24 01:33:11'),('5000000002', 'proc09', '20031024153537', '2003-10-24 15:36:04'),('5000000004', 'proc01', '20031024013641', '2003-10-24 01:37:29'),('5000000004', 'proc02', '20031024013641', '2003-10-24 01:37:39');

469

470

CREATE TABLE t3 SELECT t1.dsc,COUNT(DISTINCT t2.id) AS countOfRuns FROM t1 LEFT JOIN t2 ON (t1.id=t2.id) GROUP BY t1.id;

471

SELECT * FROM t3;

472

drop table t1, t2, t3;

473

474

475

476

# Bug#10224 - ANALYZE TABLE crashing with simultaneous

477

# CREATE ... SELECT statement.

478

# This tests two additional possible errors and a hang if

479

# an improper fix is present.

480

481

create table t1 (a int);

482

--error 1093

483

create table t1 select * from t1;

484

## TODO: Huh? --error ER_WRONG_OBJECT

485

#create table t2 union = (t1) select * from t1;

486

flush tables with read lock;

487

unlock tables;

488

drop table t1;

489

490

491

# Bug#10413: Invalid column name is not rejected

492

493

--error 1103

494

create table t1(column.name int);

495

--error 1103

496

create table t1(test.column.name int);

497

--error 1102

498

create table t1(xyz.t1.name int);

499

create table t1(t1.name int);

500

create table t2(test.t2.name int);

501

drop table t1,t2;

502

503

504

# Bug #12537: UNION produces longtext instead of varchar

505

506

CREATE TABLE t1 (f1 VARCHAR(255));

507

CREATE TABLE t2 AS SELECT LEFT(f1,171) AS f2 FROM t1 UNION SELECT LEFT(f1,171) AS f2 FROM t1;

508

DESC t2;

509

DROP TABLE t1,t2;

510

511

512

# Bug#12913 Simple SQL can crash server or connection

513

514

CREATE TABLE t12913 (f1 ENUM ('a','b')) AS SELECT 'a' AS f1;

515

SELECT * FROM t12913;

516

DROP TABLE t12913;

517

518

519

# Bug#11028: Crash on create table like

520

521

create database mysqltest;

522

use mysqltest;

523

drop database mysqltest;

524

--error ER_NO_DB_ERROR

525

create table test.t1 like x;

526

--disable_warnings

527

drop table if exists test.t1;

528

--enable_warnings

529

530

# Bug #6008 MySQL does not create warnings when

531

# creating database and using IF NOT EXISTS

532

533

create database mysqltest;

534

create database if not exists mysqltest;

535

show create database mysqltest;

536

drop database mysqltest;

537

use test;

538

create table t1 (a int);

539

create table if not exists t1 (a int);

540

drop table t1;

541

542

# BUG#14139

543

create table t1 (

544

a varchar(112) collate utf8_bin not null,

545

primary key (a)

546

) select 'test' as a ;

547

#--warning 1364

548

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

549

show create table t1;

550

drop table t1;

551

552

553

# BUG#14480: assert failure in CREATE ... SELECT because of wrong

554

# calculation of number of NULLs.

555

556

CREATE TABLE t2 (

557

a int default NULL

558

);

559

insert into t2 values(111);

560

561

#--warning 1364

562

create table t1 (

563

a varchar(12) collate utf8_bin not null,

564

b int not null, primary key (a)

565

) select a, 1 as b from t2 ;

566

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

567

show create table t1;

568

drop table t1;

569

570

--error 1364

571

create table t1 (

572

a varchar(12) collate utf8_bin not null,

573

b int not null, primary key (a)

574

) select a, 1 as c from t2 ;

575

576

create table t1 (

577

a varchar(12) collate utf8_bin not null,

578

b int null, primary key (a)

579

) select a, 1 as c from t2 ;

580

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

581

show create table t1;

582

drop table t1;

583

584

create table t1 (

585

a varchar(12) collate utf8_bin not null,

586

b int not null, primary key (a)

587

) select 'a' as a , 1 as b from t2 ;

588

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

589

show create table t1;

590

drop table t1;

591

592

create table t1 (

593

a varchar(12) collate utf8_bin,

594

b int not null, primary key (a)

595

) select 'a' as a , 1 as b from t2 ;

596

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

597

show create table t1;

598

drop table t1, t2;

599

600

create table t1 (

601

a1 int not null,

602

a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int

603

);

604

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

605

606

#--warning 1364

607

create table t2 (

608

a1 varchar(12) collate utf8_bin not null,

609

a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int,

610

primary key (a1)

611

) select a1,a2,a3,a4,a5,a6,a7,a8,a9 from t1 ;

612

drop table t2;

613

614

#--warning 1364

615

create table t2 (

616

a1 varchar(12) collate utf8_bin,

617

a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int

618

) select a1,a2,a3,a4,a5,a6,a7,a8,a9 from t1;

619

620

drop table t1, t2;

621

#--warning 1364

622

create table t1 (

623

a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int

624

);

625

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

626

627

#--warning 1364

628

create table t2 (

629

a1 varchar(12) collate utf8_bin not null,

630

a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int,

631

primary key (a1)

632

) select a1,a2,a3,a4,a5,a6,a7,a8,a9 from t1 ;

633

634

# Test the default value

635

drop table t2;

636

637

create table t2 ( a int default 3, b int default 3)

638

select a1,a2 from t1;

639

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

640

show create table t2;

641

642

drop table t1, t2;

643

644

645

646

# Tests for errors happening at various stages of CREATE TABLES ... SELECT

647

648

# (Also checks that it behaves atomically in the sense that in case

649

# of error it is automatically dropped if it has not existed before.)

650

651

# Error during open_and_lock_tables() of tables

652

--error ER_NO_SUCH_TABLE

653

create table t1 select * from t2;

654

# Rather special error which also caught during open tables pahse

655

--error ER_UPDATE_TABLE_USED

656

create table t1 select * from t1;

657

# Error which happens before select_create::prepare()

658

--error ER_CANT_AGGREGATE_2COLLATIONS

659

create table t1 select coalesce('a' collate utf8_swedish_ci,'b' collate utf8_bin);

660

# Error during table creation

661

--error ER_KEY_COLUMN_DOES_NOT_EXITS

662

create table t1 (primary key(a)) select "b" as b;

663

# Error in select_create::prepare() which is not related to table creation

664

# TODO: This really should be failing...

665

# create table t1 (a int);

666

# --error ER_WRONG_VALUE_COUNT_ON_ROW

667

# create table if not exists t1 select 1 as a, 2 as b;

668

# drop table t1;

669

# Finally error which happens during insert

670

--error ER_DUP_ENTRY

671

create table t1 (primary key (a)) (select 1 as a) union all (select 1 as a);

672

# What happens if table already exists ?

673

create table t1 (i int);

674

# TODO: BUG lp:311045

675

#--error ER_TABLE_EXISTS_ERROR

676

#create table t1 select 1 as i;

677

create table if not exists t1 select 1 as i;

678

select * from t1;

679

drop table t1;

680

# Error before select_create::prepare()

681

--error ER_CANT_AGGREGATE_2COLLATIONS

682

create table t1 select coalesce('a' collate utf8_swedish_ci,'b' collate utf8_bin);

683

# Error which happens during insertion of rows

684

# TODO: Bug lp:311072

685

# create table t1 (i int);

686

# alter table t1 add primary key (i);

687

# --error ER_DUP_ENTRY

688

# create table if not exists t1 (select 2 as i) union all (select 2 as i);

689

# select * from t1;

690

# drop table t1;

691

692

693

# Base vs temporary tables dillema (a.k.a. bug#24508 "Inconsistent

694

# results of CREATE TABLE ... SELECT when temporary table exists").

695

# In this situation we either have to create non-temporary table and

696

# insert data in it or insert data in temporary table without creation

697

# of permanent table. Since currently temporary tables always shadow

698

# permanent tables we adopt second approach.

699

create temporary table t1 (j int);

700

create table if not exists t1 select 1;

701

select * from t1;

702

drop temporary table t1;

703

--error ER_NO_SUCH_TABLE

704

select * from t1;

705

--error ER_BAD_TABLE_ERROR

706

drop table t1;

707

708

709

710

# Bug#21772: can not name a column 'upgrade' when create a table

711

712

create table t1 (upgrade int);

713

drop table t1;

714

715

716

717

# Bug #26642: create index corrupts table definition in .frm

718

719

# Problem with creating keys with maximum key-parts and maximum name length

720

# This test is made for a mysql server supporting names up to 64 bytes

721

# and a maximum of 16 key segements per Key

722

723

724

create table t1 (

725

c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int,

726

c9 int, c10 int, c11 int, c12 int, c13 int, c14 int, c15 int, c16 int,

727

728

key a001_long_123456789_123456789_123456789_123456789_123456789_1234 (

729

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

730

key a002_long_123456789_123456789_123456789_123456789_123456789_1234 (

731

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

732

key a003_long_123456789_123456789_123456789_123456789_123456789_1234 (

733

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

734

key a004_long_123456789_123456789_123456789_123456789_123456789_1234 (

735

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

736

key a005_long_123456789_123456789_123456789_123456789_123456789_1234 (

737

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

738

key a006_long_123456789_123456789_123456789_123456789_123456789_1234 (

739

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

740

key a007_long_123456789_123456789_123456789_123456789_123456789_1234 (

741

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

742

key a008_long_123456789_123456789_123456789_123456789_123456789_1234 (

743

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

744

key a009_long_123456789_123456789_123456789_123456789_123456789_1234 (

745

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

746

747

key a010_long_123456789_123456789_123456789_123456789_123456789_1234 (

748

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

749

key a011_long_123456789_123456789_123456789_123456789_123456789_1234 (

750

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

751

key a012_long_123456789_123456789_123456789_123456789_123456789_1234 (

752

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

753

key a013_long_123456789_123456789_123456789_123456789_123456789_1234 (

754

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

755

key a014_long_123456789_123456789_123456789_123456789_123456789_1234 (

756

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

757

key a015_long_123456789_123456789_123456789_123456789_123456789_1234 (

758

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

759

key a016_long_123456789_123456789_123456789_123456789_123456789_1234 (

760

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

761

key a017_long_123456789_123456789_123456789_123456789_123456789_1234 (

762

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

763

key a018_long_123456789_123456789_123456789_123456789_123456789_1234 (

764

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

765

key a019_long_123456789_123456789_123456789_123456789_123456789_1234 (

766

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

767

768

key a020_long_123456789_123456789_123456789_123456789_123456789_1234 (

769

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

770

key a021_long_123456789_123456789_123456789_123456789_123456789_1234 (

771

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

772

key a022_long_123456789_123456789_123456789_123456789_123456789_1234 (

773

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

774

key a023_long_123456789_123456789_123456789_123456789_123456789_1234 (

775

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

776

key a024_long_123456789_123456789_123456789_123456789_123456789_1234 (

777

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

778

key a025_long_123456789_123456789_123456789_123456789_123456789_1234 (

779

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

780

key a026_long_123456789_123456789_123456789_123456789_123456789_1234 (

781

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

782

key a027_long_123456789_123456789_123456789_123456789_123456789_1234 (

783

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

784

key a028_long_123456789_123456789_123456789_123456789_123456789_1234 (

785

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

786

key a029_long_123456789_123456789_123456789_123456789_123456789_1234 (

787

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

788

789

key a030_long_123456789_123456789_123456789_123456789_123456789_1234 (

790

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

791

key a031_long_123456789_123456789_123456789_123456789_123456789_1234 (

792

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

793

key a032_long_123456789_123456789_123456789_123456789_123456789_1234 (

794

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

795

key a033_long_123456789_123456789_123456789_123456789_123456789_1234 (

796

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

797

key a034_long_123456789_123456789_123456789_123456789_123456789_1234 (

798

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

799

key a035_long_123456789_123456789_123456789_123456789_123456789_1234 (

800

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

801

key a036_long_123456789_123456789_123456789_123456789_123456789_1234 (

802

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

803

key a037_long_123456789_123456789_123456789_123456789_123456789_1234 (

804

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

805

key a038_long_123456789_123456789_123456789_123456789_123456789_1234 (

806

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

807

key a039_long_123456789_123456789_123456789_123456789_123456789_1234 (

808

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

809

810

key a040_long_123456789_123456789_123456789_123456789_123456789_1234 (

811

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

812

key a041_long_123456789_123456789_123456789_123456789_123456789_1234 (

813

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

814

key a042_long_123456789_123456789_123456789_123456789_123456789_1234 (

815

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

816

key a043_long_123456789_123456789_123456789_123456789_123456789_1234 (

817

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

818

key a044_long_123456789_123456789_123456789_123456789_123456789_1234 (

819

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

820

key a045_long_123456789_123456789_123456789_123456789_123456789_1234 (

821

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

822

key a046_long_123456789_123456789_123456789_123456789_123456789_1234 (

823

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

824

key a047_long_123456789_123456789_123456789_123456789_123456789_1234 (

825

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

826

key a048_long_123456789_123456789_123456789_123456789_123456789_1234 (

827

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

828

key a049_long_123456789_123456789_123456789_123456789_123456789_1234 (

829

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

830

831

key a050_long_123456789_123456789_123456789_123456789_123456789_1234 (

832

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

833

key a051_long_123456789_123456789_123456789_123456789_123456789_1234 (

834

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

835

key a052_long_123456789_123456789_123456789_123456789_123456789_1234 (

836

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

837

key a053_long_123456789_123456789_123456789_123456789_123456789_1234 (

838

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

839

key a054_long_123456789_123456789_123456789_123456789_123456789_1234 (

840

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

841

key a055_long_123456789_123456789_123456789_123456789_123456789_1234 (

842

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

843

key a056_long_123456789_123456789_123456789_123456789_123456789_1234 (

844

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

845

key a057_long_123456789_123456789_123456789_123456789_123456789_1234 (

846

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

847

key a058_long_123456789_123456789_123456789_123456789_123456789_1234 (

848

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

849

key a059_long_123456789_123456789_123456789_123456789_123456789_1234 (

850

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

851

852

key a060_long_123456789_123456789_123456789_123456789_123456789_1234 (

853

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

854

key a061_long_123456789_123456789_123456789_123456789_123456789_1234 (

855

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

856

key a062_long_123456789_123456789_123456789_123456789_123456789_1234 (

857

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

858

key a063_long_123456789_123456789_123456789_123456789_123456789_1234 (

859

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

860

key a064_long_123456789_123456789_123456789_123456789_123456789_1234 (

861

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16)

862

);

863

864

# Check that the table is not corrupted

865

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

866

show create table t1;

867

flush tables;

868

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

869

show create table t1;

870

871

# Repeat test using ALTER to add indexes

872

873

drop table t1;

874

create table t1 (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int,

875

c8 int, c9 int, c10 int, c11 int, c12 int, c13 int, c14 int, c15 int, c16 int);

876

877

alter table t1

878

879

add key a001_long_123456789_123456789_123456789_123456789_123456789_1234 (

880

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

881

add key a002_long_123456789_123456789_123456789_123456789_123456789_1234 (

882

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

883

add key a003_long_123456789_123456789_123456789_123456789_123456789_1234 (

884

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

885

add key a004_long_123456789_123456789_123456789_123456789_123456789_1234 (

886

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

887

add key a005_long_123456789_123456789_123456789_123456789_123456789_1234 (

888

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

889

add key a006_long_123456789_123456789_123456789_123456789_123456789_1234 (

890

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

891

add key a007_long_123456789_123456789_123456789_123456789_123456789_1234 (

892

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

893

add key a008_long_123456789_123456789_123456789_123456789_123456789_1234 (

894

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

895

add key a009_long_123456789_123456789_123456789_123456789_123456789_1234 (

896

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

897

898

add key a010_long_123456789_123456789_123456789_123456789_123456789_1234 (

899

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

900

add key a011_long_123456789_123456789_123456789_123456789_123456789_1234 (

901

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

902

add key a012_long_123456789_123456789_123456789_123456789_123456789_1234 (

903

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

904

add key a013_long_123456789_123456789_123456789_123456789_123456789_1234 (

905

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

906

add key a014_long_123456789_123456789_123456789_123456789_123456789_1234 (

907

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

908

add key a015_long_123456789_123456789_123456789_123456789_123456789_1234 (

909

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

910

add key a016_long_123456789_123456789_123456789_123456789_123456789_1234 (

911

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

912

add key a017_long_123456789_123456789_123456789_123456789_123456789_1234 (

913

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

914

add key a018_long_123456789_123456789_123456789_123456789_123456789_1234 (

915

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

916

add key a019_long_123456789_123456789_123456789_123456789_123456789_1234 (

917

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

918

919

add key a020_long_123456789_123456789_123456789_123456789_123456789_1234 (

920

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

921

add key a021_long_123456789_123456789_123456789_123456789_123456789_1234 (

922

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

923

add key a022_long_123456789_123456789_123456789_123456789_123456789_1234 (

924

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

925

add key a023_long_123456789_123456789_123456789_123456789_123456789_1234 (

926

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

927

add key a024_long_123456789_123456789_123456789_123456789_123456789_1234 (

928

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

929

add key a025_long_123456789_123456789_123456789_123456789_123456789_1234 (

930

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

931

add key a026_long_123456789_123456789_123456789_123456789_123456789_1234 (

932

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

933

add key a027_long_123456789_123456789_123456789_123456789_123456789_1234 (

934

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

935

add key a028_long_123456789_123456789_123456789_123456789_123456789_1234 (

936

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

937

add key a029_long_123456789_123456789_123456789_123456789_123456789_1234 (

938

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

939

940

add key a030_long_123456789_123456789_123456789_123456789_123456789_1234 (

941

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

942

add key a031_long_123456789_123456789_123456789_123456789_123456789_1234 (

943

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

944

add key a032_long_123456789_123456789_123456789_123456789_123456789_1234 (

945

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

946

add key a033_long_123456789_123456789_123456789_123456789_123456789_1234 (

947

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

948

add key a034_long_123456789_123456789_123456789_123456789_123456789_1234 (

949

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

950

add key a035_long_123456789_123456789_123456789_123456789_123456789_1234 (

951

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

952

add key a036_long_123456789_123456789_123456789_123456789_123456789_1234 (

953

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

954

add key a037_long_123456789_123456789_123456789_123456789_123456789_1234 (

955

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

956

add key a038_long_123456789_123456789_123456789_123456789_123456789_1234 (

957

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

958

add key a039_long_123456789_123456789_123456789_123456789_123456789_1234 (

959

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

960

961

add key a040_long_123456789_123456789_123456789_123456789_123456789_1234 (

962

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

963

add key a041_long_123456789_123456789_123456789_123456789_123456789_1234 (

964

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

965

add key a042_long_123456789_123456789_123456789_123456789_123456789_1234 (

966

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

967

add key a043_long_123456789_123456789_123456789_123456789_123456789_1234 (

968

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

969

add key a044_long_123456789_123456789_123456789_123456789_123456789_1234 (

970

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

971

add key a045_long_123456789_123456789_123456789_123456789_123456789_1234 (

972

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

973

add key a046_long_123456789_123456789_123456789_123456789_123456789_1234 (

974

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

975

add key a047_long_123456789_123456789_123456789_123456789_123456789_1234 (

976

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

977

add key a048_long_123456789_123456789_123456789_123456789_123456789_1234 (

978

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

979

add key a049_long_123456789_123456789_123456789_123456789_123456789_1234 (

980

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

981

982

add key a050_long_123456789_123456789_123456789_123456789_123456789_1234 (

983

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

984

add key a051_long_123456789_123456789_123456789_123456789_123456789_1234 (

985

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

986

add key a052_long_123456789_123456789_123456789_123456789_123456789_1234 (

987

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

988

add key a053_long_123456789_123456789_123456789_123456789_123456789_1234 (

989

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

990

add key a054_long_123456789_123456789_123456789_123456789_123456789_1234 (

991

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

992

add key a055_long_123456789_123456789_123456789_123456789_123456789_1234 (

993

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

994

add key a056_long_123456789_123456789_123456789_123456789_123456789_1234 (

995

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

996

add key a057_long_123456789_123456789_123456789_123456789_123456789_1234 (

997

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

998

add key a058_long_123456789_123456789_123456789_123456789_123456789_1234 (

999

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

1000

add key a059_long_123456789_123456789_123456789_123456789_123456789_1234 (

1001

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

1002

1003

add key a060_long_123456789_123456789_123456789_123456789_123456789_1234 (

1004

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

1005

add key a061_long_123456789_123456789_123456789_123456789_123456789_1234 (

1006

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

1007

add key a062_long_123456789_123456789_123456789_123456789_123456789_1234 (

1008

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

1009

add key a063_long_123456789_123456789_123456789_123456789_123456789_1234 (

1010

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),

1011

add key a064_long_123456789_123456789_123456789_123456789_123456789_1234 (

1012

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16);

1013

1014

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

1015

show create table t1;

1016

flush tables;

1017

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

1018

show create table t1;

1019

1020

# Test the server limits; if any of these pass, all above tests need

1021

# to be rewritten to hit the limit

1022

1023

# Ensure limit is really 64 keys

1024

--error 1069

1025

alter table t1 add key

1026

a065_long_123456789_123456789_123456789_123456789_123456789_1234 (

1027

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16);

1028

1029

drop table t1;

1030

1031

# Ensure limit is really 16 key parts per key

1032

1033

create table t1 (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int,

1034

c8 int, c9 int, c10 int, c11 int, c12 int, c13 int, c14 int, c15 int,

1035

c16 int, c17 int);

1036

1037

# Get error for max key parts

1038

--error 1070

1039

alter table t1 add key i1 (

1040

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16, c17);

1041

1042

# Get error for max key-name length

1043

--error 1059

1044

alter table t1 add key

1045

a001_long_123456789_123456789_123456789_123456789_123456789_12345 (c1);

1046

1047

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

1048

show create table t1;

1049

1050

drop table t1;

1051

1052

--echo

1053

--echo Bug #26104 Bug on foreign key class constructor

1054

--echo

1055

--echo Check that ref_columns is initalized correctly in the constructor

1056

--echo and semantic checks in mysql_prepare_table work.

1057

--echo

1058

--echo We do not need a storage engine that supports foreign keys

1059

--echo for this test, as the checks are purely syntax-based, and the

1060

--echo syntax is supported for all engines.

1061

--echo

1062

--disable_warnings

1063

drop table if exists t1,t2;

1064

--enable_warnings

1065

1066

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

1067

--error ER_WRONG_FK_DEF

1068

create table t2(a int not null, b int not null, c int not null, primary key (a),

1069

foreign key fk_bug26104 (b,c) references t1(a));

1070

drop table t1;

1071

1072

1073

# Bug#15130:CREATE .. SELECT was denied to use advantages of the SQL_BIG_RESULT.

1074

1075

create table t1(f1 int,f2 int);

1076

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

1077

flush status;

1078

create table t2 select sql_big_result f1,count(f2) from t1 group by f1;

1079

show status like 'handler_read%';

1080

drop table t1,t2;

1081

1082

1083

# Bug #25162: Backing up DB from 5.1 adds 'USING BTREE' to KEYs on table creates

1084

1085

1086

# Show that the old syntax for index type is supported

1087

CREATE TABLE t1(c1 VARCHAR(33), KEY USING BTREE (c1));

1088

DROP TABLE t1;

1089

1090

# Show that the new syntax for index type is supported

1091

CREATE TABLE t1(c1 VARCHAR(33), KEY (c1) USING BTREE);

1092

DROP TABLE t1;

1093

1094

# Show that in case of multiple index type definitions, the last one takes

1095

# precedence

1096

1097

CREATE TEMPORARY TABLE t1(c1 VARCHAR(33), KEY USING BTREE (c1) USING HASH) ENGINE=MEMORY;

1098

SHOW INDEX FROM t1;

1099

DROP TABLE t1;

1100

1101

CREATE TEMPORARY TABLE t1(c1 VARCHAR(33), KEY USING HASH (c1) USING BTREE) ENGINE=MEMORY;

1102

SHOW INDEX FROM t1;

1103

DROP TABLE t1;

1104

1105

1106

--echo End of 5.0 tests

1107

1108

1109

# Test of behaviour with CREATE ... SELECT

1110

1111

1112

CREATE TABLE t1 (a int, b int);

1113

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

1114

--error ER_DUP_ENTRY

1115

CREATE TABLE t2 (primary key (a)) select * from t1;

1116

# This should give warning

1117

drop table if exists t2;

1118

--error ER_DUP_ENTRY

1119

CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1;

1120

# This should give warning

1121

drop table if exists t2;

1122

# TODO: Bug lp:311072

1123

#CREATE TABLE t2 (a int, b int, primary key (a));

1124

#--error ER_DUP_ENTRY

1125

#CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;

1126

#SELECT * from t2;

1127

#TRUNCATE table t2;

1128

#--error ER_DUP_ENTRY

1129

#INSERT INTO t2 select * from t1;

1130

#SELECT * from t2;

1131

#drop table t2;

1132

1133

CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a));

1134

--error ER_DUP_ENTRY

1135

CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;

1136

SELECT * from t2;

1137

TRUNCATE table t2;

1138

--error ER_DUP_ENTRY

1139

INSERT INTO t2 select * from t1;

1140

SELECT * from t2;

1141

drop table t1,t2;

1142

1143

1144

1145

# Test incorrect database names

1146

1147

1148

--error 1102

1149

CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1150

--error 1102

1151

DROP DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1152

1153

# TODO: enable these tests when RENAME DATABASE is implemented.

1154

# --error 1049

1155

# RENAME DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa TO a;

1156

# --error 1102

1157

# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1158

# create database mysqltest;

1159

# --error 1102

1160

# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1161

# drop database mysqltest;

1162

1163

--error 1102

1164

USE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1165

--error 1102

1166

SHOW CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1167

1168

1169

# Bug#21432 Database/Table name limited to 64 bytes, not chars, problems with multi-byte

1170

1171

1172

create database имя_базы_в_кодировке_утф8_длиной_больше_чем_45;

1173

use имя_базы_в_кодировке_утф8_длиной_больше_чем_45;

1174

select database();

1175

use test;

1176

1177

select SCHEMA_NAME from information_schema.schemata

1178

where schema_name='имя_базы_в_кодировке_утф8_длиной_больше_чем_45';

1179

1180

drop database имя_базы_в_кодировке_утф8_длиной_больше_чем_45;

1181

create table имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48

1182

(

1183

имя_поля_в_кодировке_утф8_длиной_больше_чем_45 int,

1184

index имя_индекса_в_кодировке_утф8_длиной_больше_чем_48 (имя_поля_в_кодировке_утф8_длиной_больше_чем_45)

1185

);

1186

1187

1188

# database, table, field, key

1189

select * from имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48;

1190

1191

select TABLE_NAME from information_schema.tables where

1192

table_schema='test';

1193

1194

select COLUMN_NAME from information_schema.columns where

1195

table_schema='test';

1196

1197

select INDEX_NAME from information_schema.statistics where

1198

table_schema='test';

1199

1200

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

1201

show create table имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48;

1202

1203

drop table имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48;

1204

1205

1206

1207

# Bug#25629 CREATE TABLE LIKE does not work with INFORMATION_SCHEMA

1208

1209

--error 1478

1210

create table t1 like information_schema.processlist;

1211

create table t1 like information_schema.processlist engine=innodb;

1212

show create table t1;

1213

drop table t1;

1214

--error 1478

1215

create temporary table t1 like information_schema.processlist;

1216

create temporary table t1 like information_schema.processlist engine=myisam;

1217

show create table t1;

1218

drop table t1;

1219

1220

###########################################################################

1221

1222

--echo

1223

--echo # --

1224

--echo # -- Bug#21380: DEFAULT definition not always transfered by CREATE

1225

--echo # -- TABLE/SELECT to the new table.

1226

--echo # --

1227

--echo

1228

1229

1230

--disable_warnings

1231

DROP TABLE IF EXISTS t1;

1232

DROP TABLE IF EXISTS t2;

1233

--enable_warnings

1234

1235

--echo

1236

1237

CREATE TABLE t1(

1238

c1 INT DEFAULT 12 COMMENT 'column1',

1239

c2 INT NULL COMMENT 'column2',

1240

c3 INT NOT NULL COMMENT 'column3',

1241

c4 VARCHAR(255) NOT NULL DEFAULT 'a',

1242

c5 VARCHAR(255) COLLATE utf8_unicode_ci NULL DEFAULT 'b',

1243

c6 VARCHAR(255))

1244

COLLATE utf8_bin;

1245

1246

--echo

1247

1248

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

1249

SHOW CREATE TABLE t1;

1250

1251

--echo

1252

1253

CREATE TABLE t2 AS SELECT * FROM t1;

1254

1255

--echo

1256

1257

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

1258

SHOW CREATE TABLE t2;

1259

1260

--echo

1261

1262

DROP TABLE t2;

1263

1264

--echo

1265

--echo # -- End of test case for Bug#21380.

1266

1267

###########################################################################

1268

1269

--echo

1270

--echo # --

1271

--echo # -- Bug#18834: ALTER TABLE ADD INDEX on table with two timestamp fields

1272

--echo # --

1273

--echo

1274

1275

--disable_warnings

1276

DROP TABLE IF EXISTS t1;

1277

DROP TABLE IF EXISTS t2;

1278

DROP TABLE IF EXISTS t3;

1279

--enable_warnings

1280

1281

--echo

1282

1283

CREATE TABLE t1(c1 TIMESTAMP, c2 TIMESTAMP);

1284

1285

--echo

1286

1287

--echo

1288

CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP NULL);

1289

drop table t2;

1290

1291

CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP DEFAULT '1982-01-29');

1292

drop table t2;

1293

1294

--echo

1295

CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP);

1296

drop table t2;

1297

1298

--echo

1299

--echo # -- Check that NULL column still can be created.

1300

CREATE TABLE t2(c1 TIMESTAMP NULL);

1301

1302

--echo

1303

--echo # -- Check ALTER TABLE.

1304

ALTER TABLE t1 ADD INDEX(c1);

1305

1306

--echo

1307

--echo # -- Check DATETIME.

1308

--echo

1309

1310

CREATE TABLE t3(c1 DATETIME NOT NULL);

1311

--error 1686 # Bad datetime

1312

INSERT INTO t3 VALUES (0);

1313

1314

--echo

1315

ALTER TABLE t3 ADD INDEX(c1);

1316

1317

--echo

1318

--echo # -- Cleanup.

1319

1320

DROP TABLE t1;

1321

DROP TABLE t2;

1322

DROP TABLE t3;

1323

1324

--echo

1325

--echo # -- End of Bug#18834.

1326

Older »