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

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

drop table if exists ``;

--error 1166

create table t1 (`` int);

100

--error 1280

101

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

102

103

104

# Test of CREATE ... SELECT with indexes

105

106

107

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

108

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

109

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

110

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

111

select * from t2 where b="world";

112

drop table t1,t2;

113

114

115

# Test types after CREATE ... SELECT

116

117

118

create table t1(x varchar(50) );

119

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

120

describe t1;

121

describe t2;

122

drop table t2;

123

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

124

describe t2;

125

drop table t2;

126

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

127

describe t2;

128

drop table t1,t2;

129

130

131

# Test of CREATE ... SELECT with duplicate fields

132

133

134

create table t1 (a int);

135

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

136

describe t1;

137

describe t2;

138

drop table if exists t2;

139

--error 1060

140

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

141

drop table if exists t2;

142

--error 1060

143

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

144

drop table if exists t2;

145

--error 1060

146

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

147

drop table if exists t1,t2;

148

149

150

# Test CREATE ... SELECT when insert fails

151

152

153

CREATE TABLE t1 (a int not null);

154

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

155

--error ER_DUP_ENTRY

156

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

157

--error 1146

158

SELECT * from t2;

159

DROP TABLE t1;

160

DROP TABLE IF EXISTS t2;

161

162

163

# Test of primary key with 32 index

164

165

166

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

167

show create table t1;

168

drop table t1;

169

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

170

drop table t1;

171

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

172

select * from t1;

173

drop table if exists t1;

174

175

176

# Test default table type

177

178

SET SESSION storage_engine="heap";

179

SELECT @@storage_engine;

180

CREATE TABLE t1 (a int not null);

181

show create table t1;

182

drop table t1;

183

--error 1286

184

SET SESSION storage_engine="gemini";

185

SELECT @@storage_engine;

186

CREATE TABLE t1 (a int not null);

187

show create table t1;

188

SET SESSION storage_engine=default;

189

drop table t1;

190

191

192

193

# ISO requires that primary keys are implicitly NOT NULL

194

195

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

196

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

197

--error 1048

198

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

199

--error 1048

200

insert into t1 values (NULL, 3);

201

--error 1048

202

insert into t1 values (NULL, NULL);

203

drop table t1;

204

205

206

# Bug # 801

207

208

209

create table t1 select x'4132';

210

drop table t1;

211

212

213

# bug #1434

214

215

216

create table t1 select 1,2,3;

217

create table if not exists t1 select 1,2;

218

--error 1136

219

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

220

create table if not exists t1 select 1;

221

select * from t1;

222

drop table t1;

223

224

225

# Test create table if not exists with duplicate key error

226

227

228

flush status;

229

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

230

insert into t1 values (1,1);

231

create table if not exists t1 select 2;

232

select * from t1;

233

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

234

--error ER_DUP_ENTRY

235

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

236

show warnings;

237

show status like "Opened_tables";

238

select * from t1;

239

drop table t1;

240

241

242

# Test for Bug #2985

243

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

244

245

246

--error 1103

247

create table `t1 `(a int);

248

--error 1102

249

create database `db1 `;

250

--error 1166

251

create table t1(`a ` int);

252

253

254

# Test for Bug #3481

255

# "Parser permits multiple commas without syntax error"

256

257

258

--error 1064

259

create table t1 (a int,);

260

--error 1064

261

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

262

--error 1064

263

create table t1 (,b int);

264

265

266

# Test create with foreign keys

267

268

269

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

270

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

271

drop table if exists t1,t2;

272

273

274

# Test for CREATE TABLE .. LIKE ..

275

276

277

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

278

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

279

create table t2(id int not null);

280

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

281

create table t3 like t1;

282

show create table t3;

283

select * from t3;

284

# Disable PS becasue of @@warning_count

285

create table if not exists t3 like t1;

286

--disable_ps_protocol

287

select @@warning_count;

288

--enable_ps_protocol

289

create temporary table t3 like t2;

290

show create table t3;

291

select * from t3;

292

drop table t3;

293

show create table t3;

294

select * from t3;

295

drop table t2, t3;

296

create database mysqltest;

297

create table mysqltest.t3 like t1;

298

create temporary table t3 like mysqltest.t3;

299

show create table t3;

300

create table t2 like t3;

301

show create table t2;

302

select * from t2;

303

create table t3 like t1;

304

--error 1050

305

create table t3 like mysqltest.t3;

306

--error 1049

307

create table non_existing_database.t1 like t1;

308

--error ER_NO_SUCH_TABLE

309

create table t3 like non_existing_table;

310

--error 1050

311

create temporary table t3 like t1;

312

drop table t1, t2, t3;

313

drop table t3;

314

drop database mysqltest;

315

316

317

# Test default table type

318

319

SET SESSION storage_engine="heap";

320

SELECT @@storage_engine;

321

CREATE TABLE t1 (a int not null);

322

show create table t1;

323

drop table t1;

324

--error 1286

325

SET SESSION storage_engine="gemini";

326

SELECT @@storage_engine;

327

CREATE TABLE t1 (a int not null);

328

show create table t1;

329

SET SESSION storage_engine=default;

330

drop table t1;

331

332

333

# Test types of data for create select with functions

334

335

336

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

337

insert into t1(a)values(1);

338

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

339

values(2,-2,2,'1825-12-14','a','2003-1-1 3:2:1','4:3:2','binary data');

340

select * from t1;

341

select a,

342

ifnull(b,cast(-7 as signed)) as b,

343

ifnull(c,cast(7 as)) as c,

344

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

345

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

346

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

347

ifnull(g,cast('5:4:3' as time)) as g,

348

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

349

addtime(cast('1:0:0' as time),cast('1:0:0' as time)) as dd

350

from t1;

351

352

create table t2

353

select

354

355

ifnull(b,cast(-7 as signed)) as b,

356

ifnull(c,cast(7 as)) as c,

357

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

358

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

359

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

360

ifnull(g,cast('5:4:3' as time)) as g,

361

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

362

addtime(cast('1:0:0' as time),cast('1:0:0' as time)) as dd

363

from t1;

364

explain t2;

365

select * from t2;

366

drop table t1, t2;

367

368

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

369

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

370

show create table t2;

371

drop table t1,t2;

372

373

374

# Test of default()

375

376

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

377

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

378

describe t1;

379

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

380

describe t2;

381

drop table t1, t2;

382

383

384

# Bug #2075

385

386

387

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

388

describe t1;

389

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

390

describe t2;

391

drop table t1, t2;

392

393

394

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

395

396

397

create table t1(cenum enum('a'), cset set('b'));

398

create table t2(cenum enum('a','a'), cset set('b','b'));

399

create table t3(cenum enum('a','A','a','c','c'), cset set('b','B','b','d','d'));

400

drop table t1, t2, t3;

401

402

403

# Bug #1209

404

405

406

create database mysqltest;

407

use mysqltest;

408

select database();

409

drop database mysqltest;

410

select database();

411

412

# Connect without a database as user mysqltest_1

413

create user mysqltest_1;

414

connect (user1,localhost,mysqltest_1,,*NO-ONE*);

415

connection user1;

416

select database(), user();

417

connection default;

418

disconnect user1;

419

drop user mysqltest_1;

420

use test;

421

422

423

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

424

425

426

--error 1280

427

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

428

--error 1280

429

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

430

431

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

432

show create table t1;

433

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

434

show create table t2;

435

436

create table t3 (a int);

437

--error 1280

438

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

439

--error 1280

440

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

441

442

create table t4 (`primary` int);

443

alter table t4 add index(`primary`);

444

show create table t4;

445

create table t5 (`PRIMARY` int);

446

alter table t5 add index(`PRIMARY`);

447

show create table t5;

448

449

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

450

451

452

# bug #3266 TEXT in CREATE TABLE SELECT

453

454

455

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

456

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

457

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

458

459

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

460

461

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;

462

SELECT * FROM t3;

463

drop table t1, t2, t3;

464

465

466

# Bug#9666: Can't use 'DEFAULT FALSE' for column of type bool

467

468

create table t1 (b bool not null default false);

469

create table t2 (b bool not null default true);

470

insert into t1 values ();

471

insert into t2 values ();

472

select * from t1;

473

select * from t2;

474

drop table t1,t2;

475

476

477

# Bug#10224 - ANALYZE TABLE crashing with simultaneous

478

# CREATE ... SELECT statement.

479

# This tests two additional possible errors and a hang if

480

# an improper fix is present.

481

482

create table t1 (a int);

483

--error 1093

484

create table t1 select * from t1;

485

--error ER_WRONG_OBJECT

486

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

487

flush tables with read lock;

488

unlock tables;

489

drop table t1;

490

491

492

# Bug#10413: Invalid column name is not rejected

493

494

--error 1103

495

create table t1(column.name int);

496

--error 1103

497

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

498

--error 1102

499

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

500

create table t1(t1.name int);

501

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

502

drop table t1,t2;

503

504

505

# Bug #12537: UNION produces longtext instead of varchar

506

507

CREATE TABLE t1 (f1 VARCHAR(255) CHARACTER SET utf8);

508

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

509

DESC t2;

510

DROP TABLE t1,t2;

511

512

513

# Bug#12913 Simple SQL can crash server or connection

514

515

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

516

SELECT * FROM t12913;

517

DROP TABLE t12913;

518

519

520

# Bug#11028: Crash on create table like

521

522

create database mysqltest;

523

use mysqltest;

524

drop database mysqltest;

525

--error ER_NO_DB_ERROR

526

create table test.t1 like x;

527

--disable_warnings

528

drop table if exists test.t1;

529

--enable_warnings

530

531

532

# Bug #6859: Bogus error message on attempt to CREATE TABLE t LIKE view

533

534

create database mysqltest;

535

use mysqltest;

536

create view v1 as select 'foo' from dual;

537

--error 1347

538

create table t1 like v1;

539

drop view v1;

540

drop database mysqltest;

541

# Bug #6008 MySQL does not create warnings when

542

# creating database and using IF NOT EXISTS

543

544

create database mysqltest;

545

create database if not exists mysqltest character set latin2;

546

show create database mysqltest;

547

drop database mysqltest;

548

use test;

549

create table t1 (a int);

550

create table if not exists t1 (a int);

551

drop table t1;

552

553

# BUG#14139

554

create table t1 (

555

a varchar(112) charset utf8 collate utf8_bin not null,

556

primary key (a)

557

) select 'test' as a ;

558

#--warning 1364

559

show create table t1;

560

drop table t1;

561

562

563

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

564

# calculation of number of NULLs.

565

566

CREATE TABLE t2 (

567

a int(11) default NULL

568

);

569

insert into t2 values(111);

570

571

#--warning 1364

572

create table t1 (

573

a varchar(12) charset utf8 collate utf8_bin not null,

574

b int not null, primary key (a)

575

) select a, 1 as b from t2 ;

576

show create table t1;

577

drop table t1;

578

579

#--warning 1364

580

create table t1 (

581

a varchar(12) charset utf8 collate utf8_bin not null,

582

b int not null, primary key (a)

583

) select a, 1 as c from t2 ;

584

show create table t1;

585

drop table t1;

586

587

#--warning 1364

588

create table t1 (

589

a varchar(12) charset utf8 collate utf8_bin not null,

590

b int null, primary key (a)

591

) select a, 1 as c from t2 ;

592

show create table t1;

593

drop table t1;

594

595

#--warning 1364

596

create table t1 (

597

a varchar(12) charset utf8 collate utf8_bin not null,

598

b int not null, primary key (a)

599

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

600

show create table t1;

601

drop table t1;

602

603

#--warning 1364

604

create table t1 (

605

a varchar(12) charset utf8 collate utf8_bin,

606

b int not null, primary key (a)

607

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

608

show create table t1;

609

drop table t1, t2;

610

611

create table t1 (

612

a1 int not null,

613

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

614

);

615

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

616

617

#--warning 1364

618

create table t2 (

619

a1 varchar(12) charset utf8 collate utf8_bin not null,

620

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

621

primary key (a1)

622

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

623

drop table t2;

624

625

#--warning 1364

626

create table t2 (

627

a1 varchar(12) charset utf8 collate utf8_bin,

628

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

629

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

630

631

drop table t1, t2;

632

#--warning 1364

633

create table t1 (

634

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

635

);

636

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

637

638

#--warning 1364

639

create table t2 (

640

a1 varchar(12) charset utf8 collate utf8_bin not null,

641

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

642

primary key (a1)

643

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

644

645

# Test the default value

646

drop table t2;

647

648

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

649

select a1,a2 from t1;

650

show create table t2;

651

652

drop table t1, t2;

653

654

655

# Bug #15316 SET value having comma not correctly handled

656

657

--error 1367

658

create table t1(a set("a,b","c,d") not null);

659

660

# End of 4.1 tests

661

662

663

664

# Bug #14155: Maximum value of MAX_ROWS handled incorrectly on 64-bit

665

# platforms

666

667

create table t1 (i int) engine=myisam max_rows=100000000000;

668

show create table t1;

669

alter table t1 max_rows=100;

670

show create table t1;

671

alter table t1 max_rows=100000000000;

672

show create table t1;

673

drop table t1;

674

675

676

677

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

678

679

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

680

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

681

682

# Error during open_and_lock_tables() of tables

683

--error ER_NO_SUCH_TABLE

684

create table t1 select * from t2;

685

# Rather special error which also caught during open tables pahse

686

--error ER_UPDATE_TABLE_USED

687

create table t1 select * from t1;

688

# Error which happens before select_create::prepare()

689

--error ER_CANT_AGGREGATE_2COLLATIONS

690

create table t1 select coalesce('a' collate latin1_swedish_ci,'b' collate latin1_bin);

691

# Error during table creation

692

--error ER_KEY_COLUMN_DOES_NOT_EXITS

693

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

694

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

695

create table t1 (a int);

696

--error ER_WRONG_VALUE_COUNT_ON_ROW

697

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

698

drop table t1;

699

# Finally error which happens during insert

700

--error ER_DUP_ENTRY

701

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

702

# What happens if table already exists ?

703

create table t1 (i int);

704

--error ER_TABLE_EXISTS_ERROR

705

create table t1 select 1 as i;

706

create table if not exists t1 select 1 as i;

707

select * from t1;

708

# Error before select_create::prepare()

709

--error ER_CANT_AGGREGATE_2COLLATIONS

710

create table t1 select coalesce('a' collate latin1_swedish_ci,'b' collate latin1_bin);

711

select * from t1;

712

# Error which happens during insertion of rows

713

alter table t1 add primary key (i);

714

--error ER_DUP_ENTRY

715

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

716

select * from t1;

717

drop table t1;

718

719

720

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

721

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

722

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

723

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

724

# of permanent table. Since currently temporary tables always shadow

725

# permanent tables we adopt second approach.

726

create temporary table t1 (j int);

727

create table if not exists t1 select 1;

728

select * from t1;

729

drop temporary table t1;

730

--error ER_NO_SUCH_TABLE

731

select * from t1;

732

--error ER_BAD_TABLE_ERROR

733

drop table t1;

734

735

736

737

# CREATE TABLE ... SELECT and LOCK TABLES

738

739

# There is little sense in using CREATE TABLE ... SELECT under

740

# LOCK TABLES as it mostly does not work. At least we check that

741

# the server doesn't crash, hang and produces sensible errors.

742

# Includes test for bug #20662 "Infinite loop in CREATE TABLE

743

# IF NOT EXISTS ... SELECT with locked tables".

744

create table t1 (i int);

745

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

746

lock tables t1 read;

747

--error ER_TABLE_NOT_LOCKED

748

create table t2 select * from t1;

749

--error ER_TABLE_NOT_LOCKED

750

create table if not exists t2 select * from t1;

751

unlock tables;

752

create table t2 (j int);

753

lock tables t1 read;

754

--error ER_TABLE_NOT_LOCKED

755

create table t2 select * from t1;

756

# This should not be ever allowed as it will undermine

757

# lock-all-at-once approach

758

--error ER_TABLE_NOT_LOCKED

759

create table if not exists t2 select * from t1;

760

unlock tables;

761

lock table t1 read, t2 read;

762

--error ER_TABLE_NOT_LOCKED_FOR_WRITE

763

create table t2 select * from t1;

764

--error ER_TABLE_NOT_LOCKED_FOR_WRITE

765

create table if not exists t2 select * from t1;

766

unlock tables;

767

lock table t1 read, t2 write;

768

--error ER_TABLE_EXISTS_ERROR

769

create table t2 select * from t1;

770

# This is the only case which really works.

771

create table if not exists t2 select * from t1;

772

select * from t1;

773

unlock tables;

774

drop table t2;

775

776

# OTOH CREATE TEMPORARY TABLE ... SELECT should work

777

# well under LOCK TABLES.

778

lock tables t1 read;

779

create temporary table t2 select * from t1;

780

create temporary table if not exists t2 select * from t1;

781

select * from t2;

782

unlock tables;

783

drop table t1, t2;

784

785

786

787

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

788

789

create table t1 (upgrade int);

790

drop table t1;

791

792

793

794

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

795

796

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

797

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

798

# and a maximum of 16 key segements per Key

799

800

801

create table t1 (

802

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

803

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

804

805

key a001_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 a002_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

key a003_long_123456789_123456789_123456789_123456789_123456789_1234 (

810

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

811

key a004_long_123456789_123456789_123456789_123456789_123456789_1234 (

812

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

813

key a005_long_123456789_123456789_123456789_123456789_123456789_1234 (

814

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

815

key a006_long_123456789_123456789_123456789_123456789_123456789_1234 (

816

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

817

key a007_long_123456789_123456789_123456789_123456789_123456789_1234 (

818

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

819

key a008_long_123456789_123456789_123456789_123456789_123456789_1234 (

820

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

821

key a009_long_123456789_123456789_123456789_123456789_123456789_1234 (

822

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

823

824

key a010_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 a011_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 a012_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

key a013_long_123456789_123456789_123456789_123456789_123456789_1234 (

831

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

832

key a014_long_123456789_123456789_123456789_123456789_123456789_1234 (

833

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

834

key a015_long_123456789_123456789_123456789_123456789_123456789_1234 (

835

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

836

key a016_long_123456789_123456789_123456789_123456789_123456789_1234 (

837

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

838

key a017_long_123456789_123456789_123456789_123456789_123456789_1234 (

839

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

840

key a018_long_123456789_123456789_123456789_123456789_123456789_1234 (

841

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

842

key a019_long_123456789_123456789_123456789_123456789_123456789_1234 (

843

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

844

845

key a020_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 a021_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 a022_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

key a023_long_123456789_123456789_123456789_123456789_123456789_1234 (

852

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

853

key a024_long_123456789_123456789_123456789_123456789_123456789_1234 (

854

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

855

key a025_long_123456789_123456789_123456789_123456789_123456789_1234 (

856

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

857

key a026_long_123456789_123456789_123456789_123456789_123456789_1234 (

858

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

859

key a027_long_123456789_123456789_123456789_123456789_123456789_1234 (

860

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

861

key a028_long_123456789_123456789_123456789_123456789_123456789_1234 (

862

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

863

key a029_long_123456789_123456789_123456789_123456789_123456789_1234 (

864

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

865

866

key a030_long_123456789_123456789_123456789_123456789_123456789_1234 (

867

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

868

key a031_long_123456789_123456789_123456789_123456789_123456789_1234 (

869

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

870

key a032_long_123456789_123456789_123456789_123456789_123456789_1234 (

871

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

872

key a033_long_123456789_123456789_123456789_123456789_123456789_1234 (

873

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

874

key a034_long_123456789_123456789_123456789_123456789_123456789_1234 (

875

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

876

key a035_long_123456789_123456789_123456789_123456789_123456789_1234 (

877

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

878

key a036_long_123456789_123456789_123456789_123456789_123456789_1234 (

879

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

880

key a037_long_123456789_123456789_123456789_123456789_123456789_1234 (

881

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

882

key a038_long_123456789_123456789_123456789_123456789_123456789_1234 (

883

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

884

key a039_long_123456789_123456789_123456789_123456789_123456789_1234 (

885

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

886

887

key a040_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

key a041_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

key a042_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

key a043_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

key a044_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

key a045_long_123456789_123456789_123456789_123456789_123456789_1234 (

898

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

899

key a046_long_123456789_123456789_123456789_123456789_123456789_1234 (

900

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

901

key a047_long_123456789_123456789_123456789_123456789_123456789_1234 (

902

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

903

key a048_long_123456789_123456789_123456789_123456789_123456789_1234 (

904

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

905

key a049_long_123456789_123456789_123456789_123456789_123456789_1234 (

906

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

907

908

key a050_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

key a051_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

key a052_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

key a053_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

key a054_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

key a055_long_123456789_123456789_123456789_123456789_123456789_1234 (

919

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

920

key a056_long_123456789_123456789_123456789_123456789_123456789_1234 (

921

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

922

key a057_long_123456789_123456789_123456789_123456789_123456789_1234 (

923

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

924

key a058_long_123456789_123456789_123456789_123456789_123456789_1234 (

925

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

926

key a059_long_123456789_123456789_123456789_123456789_123456789_1234 (

927

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

928

929

key a060_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

key a061_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

key a062_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

key a063_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

key a064_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

941

# Check that the table is not corrupted

942

show create table t1;

943

flush tables;

944

show create table t1;

945

946

# Repeat test using ALTER to add indexes

947

948

drop table t1;

949

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

950

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

951

952

alter table t1

953

954

add key a001_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 a002_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 a003_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

add key a004_long_123456789_123456789_123456789_123456789_123456789_1234 (

961

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

962

add key a005_long_123456789_123456789_123456789_123456789_123456789_1234 (

963

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

964

add key a006_long_123456789_123456789_123456789_123456789_123456789_1234 (

965

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

966

add key a007_long_123456789_123456789_123456789_123456789_123456789_1234 (

967

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

968

add key a008_long_123456789_123456789_123456789_123456789_123456789_1234 (

969

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

970

add key a009_long_123456789_123456789_123456789_123456789_123456789_1234 (

971

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

972

973

add key a010_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 a011_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 a012_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 a013_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

add key a014_long_123456789_123456789_123456789_123456789_123456789_1234 (

982

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

983

add key a015_long_123456789_123456789_123456789_123456789_123456789_1234 (

984

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

985

add key a016_long_123456789_123456789_123456789_123456789_123456789_1234 (

986

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

987

add key a017_long_123456789_123456789_123456789_123456789_123456789_1234 (

988

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

989

add key a018_long_123456789_123456789_123456789_123456789_123456789_1234 (

990

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

991

add key a019_long_123456789_123456789_123456789_123456789_123456789_1234 (

992

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

993

994

add key a020_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 a021_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 a022_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 a023_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

add key a024_long_123456789_123456789_123456789_123456789_123456789_1234 (

1003

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

1004

add key a025_long_123456789_123456789_123456789_123456789_123456789_1234 (

1005

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

1006

add key a026_long_123456789_123456789_123456789_123456789_123456789_1234 (

1007

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

1008

add key a027_long_123456789_123456789_123456789_123456789_123456789_1234 (

1009

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

1010

add key a028_long_123456789_123456789_123456789_123456789_123456789_1234 (

1011

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

1012

add key a029_long_123456789_123456789_123456789_123456789_123456789_1234 (

1013

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

1014

1015

add key a030_long_123456789_123456789_123456789_123456789_123456789_1234 (

1016

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

1017

add key a031_long_123456789_123456789_123456789_123456789_123456789_1234 (

1018

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

1019

add key a032_long_123456789_123456789_123456789_123456789_123456789_1234 (

1020

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

1021

add key a033_long_123456789_123456789_123456789_123456789_123456789_1234 (

1022

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

1023

add key a034_long_123456789_123456789_123456789_123456789_123456789_1234 (

1024

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

1025

add key a035_long_123456789_123456789_123456789_123456789_123456789_1234 (

1026

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

1027

add key a036_long_123456789_123456789_123456789_123456789_123456789_1234 (

1028

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

1029

add key a037_long_123456789_123456789_123456789_123456789_123456789_1234 (

1030

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

1031

add key a038_long_123456789_123456789_123456789_123456789_123456789_1234 (

1032

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

1033

add key a039_long_123456789_123456789_123456789_123456789_123456789_1234 (

1034

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

1035

1036

add key a040_long_123456789_123456789_123456789_123456789_123456789_1234 (

1037

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

1038

add key a041_long_123456789_123456789_123456789_123456789_123456789_1234 (

1039

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

1040

add key a042_long_123456789_123456789_123456789_123456789_123456789_1234 (

1041

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

1042

add key a043_long_123456789_123456789_123456789_123456789_123456789_1234 (

1043

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

1044

add key a044_long_123456789_123456789_123456789_123456789_123456789_1234 (

1045

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

1046

add key a045_long_123456789_123456789_123456789_123456789_123456789_1234 (

1047

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

1048

add key a046_long_123456789_123456789_123456789_123456789_123456789_1234 (

1049

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

1050

add key a047_long_123456789_123456789_123456789_123456789_123456789_1234 (

1051

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

1052

add key a048_long_123456789_123456789_123456789_123456789_123456789_1234 (

1053

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

1054

add key a049_long_123456789_123456789_123456789_123456789_123456789_1234 (

1055

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

1056

1057

add key a050_long_123456789_123456789_123456789_123456789_123456789_1234 (

1058

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

1059

add key a051_long_123456789_123456789_123456789_123456789_123456789_1234 (

1060

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

1061

add key a052_long_123456789_123456789_123456789_123456789_123456789_1234 (

1062

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

1063

add key a053_long_123456789_123456789_123456789_123456789_123456789_1234 (

1064

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

1065

add key a054_long_123456789_123456789_123456789_123456789_123456789_1234 (

1066

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

1067

add key a055_long_123456789_123456789_123456789_123456789_123456789_1234 (

1068

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

1069

add key a056_long_123456789_123456789_123456789_123456789_123456789_1234 (

1070

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

1071

add key a057_long_123456789_123456789_123456789_123456789_123456789_1234 (

1072

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

1073

add key a058_long_123456789_123456789_123456789_123456789_123456789_1234 (

1074

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

1075

add key a059_long_123456789_123456789_123456789_123456789_123456789_1234 (

1076

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

1077

1078

add key a060_long_123456789_123456789_123456789_123456789_123456789_1234 (

1079

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

1080

add key a061_long_123456789_123456789_123456789_123456789_123456789_1234 (

1081

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

1082

add key a062_long_123456789_123456789_123456789_123456789_123456789_1234 (

1083

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

1084

add key a063_long_123456789_123456789_123456789_123456789_123456789_1234 (

1085

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

1086

add key a064_long_123456789_123456789_123456789_123456789_123456789_1234 (

1087

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

1088

1089

show create table t1;

1090

flush tables;

1091

show create table t1;

1092

1093

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

1094

# to be rewritten to hit the limit

1095

1096

# Ensure limit is really 64 keys

1097

--error 1069

1098

alter table t1 add key

1099

a065_long_123456789_123456789_123456789_123456789_123456789_1234 (

1100

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

1101

1102

drop table t1;

1103

1104

# Ensure limit is really 16 key parts per key

1105

1106

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

1107

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

1108

c16 int, c17 int);

1109

1110

# Get error for max key parts

1111

--error 1070

1112

alter table t1 add key i1 (

1113

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

1114

1115

# Get error for max key-name length

1116

--error 1059

1117

alter table t1 add key

1118

a001_long_123456789_123456789_123456789_123456789_123456789_12345 (c1);

1119

1120

show create table t1;

1121

1122

drop table t1;

1123

1124

--echo

1125

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

1126

--echo

1127

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

1128

--echo and semantic checks in mysql_prepare_table work.

1129

--echo

1130

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

1131

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

1132

--echo syntax is supported for all engines.

1133

--echo

1134

--disable_warnings

1135

drop table if exists t1,t2;

1136

--enable_warnings

1137

1138

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

1139

--error ER_WRONG_FK_DEF

1140

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

1141

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

1142

drop table t1;

1143

1144

1145

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

1146

1147

create table t1(f1 int,f2 int);

1148

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

1149

flush status;

1150

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

1151

show status like 'handler_read%';

1152

drop table t1,t2;

1153

1154

1155

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

1156

1157

1158

# Show that the old syntax for index type is supported

1159

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

1160

DROP TABLE t1;

1161

1162

# Show that the new syntax for index type is supported

1163

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

1164

DROP TABLE t1;

1165

1166

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

1167

# precedence

1168

1169

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

1170

SHOW INDEX FROM t1;

1171

DROP TABLE t1;

1172

1173

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

1174

SHOW INDEX FROM t1;

1175

DROP TABLE t1;

1176

1177

1178

--echo End of 5.0 tests

1179

1180

1181

# Test of behaviour with CREATE ... SELECT

1182

1183

1184

CREATE TABLE t1 (a int, b int);

1185

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

1186

--error ER_DUP_ENTRY

1187

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

1188

# This should give warning

1189

drop table if exists t2;

1190

--error ER_DUP_ENTRY

1191

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

1192

# This should give warning

1193

drop table if exists t2;

1194

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

1195

--error ER_DUP_ENTRY

1196

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

1197

SELECT * from t2;

1198

TRUNCATE table t2;

1199

--error ER_DUP_ENTRY

1200

INSERT INTO t2 select * from t1;

1201

SELECT * from t2;

1202

drop table t2;

1203

1204

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

1205

--error ER_DUP_ENTRY

1206

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

1207

SELECT * from t2;

1208

TRUNCATE table t2;

1209

--error ER_DUP_ENTRY

1210

INSERT INTO t2 select * from t1;

1211

SELECT * from t2;

1212

drop table t1,t2;

1213

1214

1215

1216

# Test incorrect database names

1217

1218

1219

--error 1102

1220

CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1221

--error 1102

1222

DROP DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1223

1224

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

1225

# --error 1049

1226

# RENAME DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa TO a;

1227

# --error 1102

1228

# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1229

# create database mysqltest;

1230

# --error 1102

1231

# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1232

# drop database mysqltest;

1233

1234

--error 1102

1235

USE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1236

--error 1102

1237

SHOW CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1238

1239

1240

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

1241

1242

set names utf8;

1243

1244

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

1245

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

1246

select database();

1247

use test;

1248

1249

select SCHEMA_NAME from information_schema.schemata

1250

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

1251

1252

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

1253

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

1254

(

1255

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

1256

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

1257

);

1258

1259

create view имя_вью_кодировке_утф8_длиной_больше_чем_42 as

1260

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

1261

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

1262

1263

# database, table, field, key, view

1264

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

1265

1266

select TABLE_NAME from information_schema.tables where

1267

table_schema='test';

1268

1269

select COLUMN_NAME from information_schema.columns where

1270

table_schema='test';

1271

1272

select INDEX_NAME from information_schema.statistics where

1273

table_schema='test';

1274

1275

select TABLE_NAME from information_schema.views where

1276

table_schema='test';

1277

1278

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

1279

show create view имя_вью_кодировке_утф8_длиной_больше_чем_42;

1280

1281

# procedure, function, trigger

1282

1283

create trigger имя_триггера_в_кодировке_утф8_длиной_больше_чем_49

1284

before insert on имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48 for each row set @a:=1;

1285

select TRIGGER_NAME from information_schema.triggers where

1286

trigger_schema='test';

1287

drop trigger имя_триггера_в_кодировке_утф8_длиной_больше_чем_49;

1288

--error 1059

1289

create trigger

1290

очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66

1291

before insert on имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48 for each row set @a:=1;

1292

--error 1059

1293

drop trigger очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66;

1294

1295

create procedure имя_процедуры_в_кодировке_утф8_длиной_больше_чем_50()

1296

begin

1297

end;

1298

select ROUTINE_NAME from information_schema.routines where

1299

routine_schema='test';

1300

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

1301

--error 1059

1302

create procedure очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66()

1303

begin

1304

end;

1305

1306

create function имя_функции_в_кодировке_утф8_длиной_больше_чем_49()

1307

returns int

1308

return 0;

1309

select ROUTINE_NAME from information_schema.routines where

1310

routine_schema='test';

1311

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

1312

--error 1059

1313

create function очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66()

1314

returns int

1315

return 0;

1316

1317

drop view имя_вью_кодировке_утф8_длиной_больше_чем_42;

1318

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

1319

set names default;

1320

1321

1322

# Bug#21136 CREATE TABLE SELECT within CREATE TABLE SELECT causes server crash

1323

1324

1325

--disable_warnings

1326

drop table if exists t1,t2,t3;

1327

drop function if exists f1;

1328

--enable_warnings

1329

1330

--delimiter |

1331

create function f1() returns int

1332

begin

1333

declare res int;

1334

create temporary table t3 select 1 i;

1335

set res:= (select count(*) from t1);

1336

drop temporary table t3;

1337

return res;

1338

end|

1339

--delimiter ;

1340

create table t1 as select 1;

1341

create table t2 as select f1() from t1;

1342

drop table t1,t2;

1343

drop function f1;

1344

1345

1346

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

1347

1348

create table t1 like information_schema.processlist;

1349

show create table t1;

1350

drop table t1;

1351

create temporary table t1 like information_schema.processlist;

1352

show create table t1;

1353

drop table t1;

1354

create table t1 like information_schema.character_sets;

1355

show create table t1;

1356

drop table t1;

1357

1358

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

1359

1360

--echo

1361

--echo # --

1362

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

1363

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

1364

--echo # --

1365

--echo

1366

1367

1368

--disable_warnings

1369

DROP TABLE IF EXISTS t1;

1370

DROP TABLE IF EXISTS t2;

1371

--enable_warnings

1372

1373

--echo

1374

1375

CREATE TABLE t1(

1376

c1 INT DEFAULT 12 COMMENT 'column1',

1377

c2 INT NULL COMMENT 'column2',

1378

c3 INT NOT NULL COMMENT 'column3',

1379

c4 VARCHAR(255) CHARACTER SET utf8 NOT NULL DEFAULT 'a',

1380

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

1381

c6 VARCHAR(255))

1382

COLLATE latin1_bin;

1383

1384

--echo

1385

1386

SHOW CREATE TABLE t1;

1387

1388

--echo

1389

1390

CREATE TABLE t2 AS SELECT * FROM t1;

1391

1392

--echo

1393

1394

SHOW CREATE TABLE t2;

1395

1396

--echo

1397

1398

DROP TABLE t2;

1399

1400

--echo

1401

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

1402

1403

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

1404

1405

--echo

1406

--echo # --

1407

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

1408

--echo # --

1409

--echo

1410

1411

--disable_warnings

1412

DROP TABLE IF EXISTS t1;

1413

DROP TABLE IF EXISTS t2;

1414

DROP TABLE IF EXISTS t3;

1415

--enable_warnings

1416

1417

--echo

1418

1419

CREATE TABLE t1(c1 TIMESTAMP, c2 TIMESTAMP);

1420

1421

--echo

1422

1423

SET sql_mode = NO_ZERO_DATE;

1424

1425

--echo

1426

--error ER_INVALID_DEFAULT

1427

CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP DEFAULT 0);

1428

1429

--echo

1430

--error ER_INVALID_DEFAULT

1431

CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP);

1432

1433

--echo

1434

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

1435

CREATE TABLE t2(c1 TIMESTAMP NULL);

1436

1437

--echo

1438

--echo # -- Check ALTER TABLE.

1439

--error ER_INVALID_DEFAULT

1440

ALTER TABLE t1 ADD INDEX(c1);

1441

1442

--echo

1443

--echo # -- Check DATETIME.

1444

SET sql_mode = '';

1445

1446

--echo

1447

1448

CREATE TABLE t3(c1 DATETIME NOT NULL);

1449

INSERT INTO t3 VALUES (0);

1450

1451

--echo

1452

SET sql_mode = TRADITIONAL;

1453

1454

--echo

1455

--error ER_TRUNCATED_WRONG_VALUE

1456

ALTER TABLE t3 ADD INDEX(c1);

1457

1458

--echo

1459

--echo # -- Cleanup.

1460

1461

SET sql_mode = '';

1462

DROP TABLE t1;

1463

DROP TABLE t2;

1464

DROP TABLE t3;

1465

1466

--echo

1467

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

1468

1469

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

1470

1471

--echo

1472

--echo # --

1473

--echo # -- Bug#34274: Invalid handling of 'DEFAULT 0' for YEAR data type.

1474

--echo # --

1475

--echo

1476

1477

--disable_warnings

1478

DROP TABLE IF EXISTS t1;

1479

--enable_warnings

1480

1481

--echo

1482

CREATE TABLE t1(c1 YEAR DEFAULT 2008, c2 YEAR DEFAULT 0);

1483

1484

--echo

1485

SHOW CREATE TABLE t1;

1486

1487

--echo

1488

INSERT INTO t1 VALUES();

1489

1490

--echo

1491

SELECT * FROM t1;

1492

1493

--echo

1494

ALTER TABLE t1 MODIFY c1 YEAR DEFAULT 0;

1495

1496

--echo

1497

SHOW CREATE TABLE t1;

1498

1499

--echo

1500

INSERT INTO t1 VALUES();

1501

1502

--echo

1503

SELECT * FROM t1;

1504

1505

--echo

1506

DROP TABLE t1;

1507

1508

--echo

1509

--echo # -- End of Bug#34274

1510

1511

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

1512

1513

--echo

1514

--echo End of 5.1 tests

Older »