~drizzle-trunk/drizzle/development

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

171

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

168

172

show create table t1;

169

173

drop table t1;

170

174

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

176

180

177

181

# Test default table type

178

182

179

SET SESSION storage_engine="heap";

183

SET SESSION storage_engine="MEMORY";

180

184

SELECT @@storage_engine;

181

CREATE TABLE t1 (a int not null);

185

CREATE TEMPORARY TABLE t1 (a int not null);

182

186

show create table t1;

183

187

drop table t1;

184

188

--error 1286

185

189

SET SESSION storage_engine="gemini";

186

190

SELECT @@storage_engine;

187

CREATE TABLE t1 (a int not null);

191

CREATE TEMPORARY TABLE t1 (a int not null);

188

192

show create table t1;

189

193

SET SESSION storage_engine=default;

190

194

drop table t1;

215

219

216

220

217

221

create table t1 select 1,2,3;

222

--error 1364

218

223

create table if not exists t1 select 1,2;

219

224

--error 1136

220

225

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

226

--error 1364

221

227

create table if not exists t1 select 1;

222

228

select * from t1;

223

229

drop table t1;

229

235

flush status;

230

236

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

231

237

insert into t1 values (1,1);

232

create table if not exists t1 select 2;

238

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

239

#create table if not exists t1 select 2;

233

240

select * from t1;

234

241

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

235

242

--error ER_DUP_ENTRY

269

276

270

277

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

271

278

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

279

--error 1217

272

280

drop table if exists t1,t2;

281

drop table if exists t2,t1;

273

282

274

283

275

284

# Test for CREATE TABLE .. LIKE ..

280

289

create table t2(id int not null);

281

290

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

282

291

create table t3 like t1;

292

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

283

293

show create table t3;

284

294

select * from t3;

285

295

# Disable PS becasue of @@warning_count

288

298

select @@warning_count;

289

299

--enable_ps_protocol

290

300

create temporary table t3 like t2;

301

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

291

302

show create table t3;

292

303

select * from t3;

293

304

drop table t3;

305

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

294

306

show create table t3;

295

307

select * from t3;

296

308

drop table t2, t3;

297

309

create database mysqltest;

298

310

create table mysqltest.t3 like t1;

299

311

create temporary table t3 like mysqltest.t3;

312

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

300

313

show create table t3;

301

314

create table t2 like t3;

315

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

302

316

show create table t2;

303

317

select * from t2;

304

318

create table t3 like t1;

317

331

318

332

# Test default table type

319

333

320

SET SESSION storage_engine="heap";

334

SET SESSION storage_engine="MEMORY";

321

335

SELECT @@storage_engine;

322

CREATE TABLE t1 (a int not null);

336

CREATE TEMPORARY TABLE t1 (a int not null);

323

337

show create table t1;

324

338

drop table t1;

325

339

--error 1286

326

340

SET SESSION storage_engine="gemini";

327

341

SELECT @@storage_engine;

328

CREATE TABLE t1 (a int not null);

342

CREATE TEMPORARY TABLE t1 (a int not null);

329

343

show create table t1;

330

344

SET SESSION storage_engine=default;

331

345

drop table t1;

334

348

# Test types of data for create select with functions

335

349

336

350

337

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

351

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

338

352

insert into t1(a)values(1);

339

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

340

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

353

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

354

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

341

355

select * from t1;

342

356

select a,

343

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

344

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

357

ifnull(b,-7) as b,

358

ifnull(c,7) as c,

345

359

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

346

360

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

347

361

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

348

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

349

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

350

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

362

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

351

363

from t1;

352

364

353

365

create table t2

354

366

select

355

367

356

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

357

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

368

ifnull(b,-7) as b,

369

ifnull(c,7) as c,

358

370

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

359

371

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

360

372

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

361

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

362

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

363

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

373

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

364

374

from t1;

365

375

explain t2;

366

376

select * from t2;

367

377

drop table t1, t2;

368

378

369

create table t1 (a tinyint, b smallint, 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));

370

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;

379

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

380

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;

381

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

371

382

show create table t2;

372

383

drop table t1,t2;

373

384

385

396

# Bug #2075

386

397

387

398

388

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

399

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

389

400

describe t1;

390

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

401

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

391

402

describe t2;

392

403

drop table t1, t2;

393

404

395

406

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

396

407

397

408

398

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

399

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

400

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

401

drop table t1, t2, t3;

409

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

410

--error 1291

411

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

412

--error 1291

413

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

414

drop table t1;

402

415

403

416

404

417

# Bug #1209

409

422

select database();

410

423

drop database mysqltest;

411

424

select database();

412

413

# Connect without a database as user mysqltest_1

414

create user mysqltest_1;

415

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

416

connection user1;

417

select database(), user();

418

connection default;

419

disconnect user1;

420

drop user mysqltest_1;

421

425

use test;

422

426

423

427

424

428

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

425

429

426

430

427

--error 1280

428

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

429

--error 1280

430

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

431

432

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

433

show create table t1;

434

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

435

show create table t2;

436

437

create table t3 (a int);

438

--error 1280

439

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

440

--error 1280

441

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

442

443

create table t4 (`primary` int);

444

alter table t4 add index(`primary`);

445

show create table t4;

446

create table t5 (`PRIMARY` int);

447

alter table t5 add index(`PRIMARY`);

448

show create table t5;

449

450

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

431

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

432

#--error 1280

433

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

434

#--error 1280

435

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

436

437

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

438

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

439

#show create table t1;

440

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

441

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

442

#show create table t2;

443

444

#create table t3 (a int);

445

#--error 1280

446

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

447

#--error 1280

448

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

449

450

#create table t4 (`primary` int);

451

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

452

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

453

#show create table t4;

454

#create table t5 (`PRIMARY` int);

455

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

456

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

457

#show create table t5;

458

459

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

451

460

452

461

453

462

# bug #3266 TEXT in CREATE TABLE SELECT

463

472

SELECT * FROM t3;

464

473

drop table t1, t2, t3;

465

474

466

467

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

468

469

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

470

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

471

insert into t1 values ();

472

insert into t2 values ();

473

select * from t1;

474

select * from t2;

475

drop table t1,t2;

476

475

477

476

478

477

# Bug#10224 - ANALYZE TABLE crashing with simultaneous

483

482

create table t1 (a int);

484

483

--error 1093

485

484

create table t1 select * from t1;

486

--error ER_WRONG_OBJECT

487

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

485

## TODO: Huh? --error ER_WRONG_OBJECT

486

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

488

487

flush tables with read lock;

489

488

unlock tables;

490

489

drop table t1;

505

504

506

505

# Bug #12537: UNION produces longtext instead of varchar

507

506

508

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

507

CREATE TABLE t1 (f1 VARCHAR(255));

509

508

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

510

509

DESC t2;

511

510

DROP TABLE t1,t2;

529

528

drop table if exists test.t1;

530

529

--enable_warnings

531

530

532

533

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

534

535

create database mysqltest;

536

use mysqltest;

537

create view v1 as select 'foo' from dual;

538

--error 1347

539

create table t1 like v1;

540

drop view v1;

541

drop database mysqltest;

542

531

# Bug #6008 MySQL does not create warnings when

543

532

# creating database and using IF NOT EXISTS

544

533

545

534

create database mysqltest;

546

create database if not exists mysqltest character set latin2;

535

create database if not exists mysqltest;

547

536

show create database mysqltest;

548

537

drop database mysqltest;

549

538

use test;

553

542

554

543

# BUG#14139

555

544

create table t1 (

556

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

545

a varchar(112) collate utf8_bin not null,

557

546

primary key (a)

558

547

) select 'test' as a ;

559

548

#--warning 1364

549

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

560

550

show create table t1;

561

551

drop table t1;

562

552

565

555

# calculation of number of NULLs.

566

556

567

557

CREATE TABLE t2 (

568

a int(11) default NULL

558

a int default NULL

569

559

);

570

560

insert into t2 values(111);

571

561

572

562

#--warning 1364

573

563

create table t1 (

574

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

564

a varchar(12) collate utf8_bin not null,

575

565

b int not null, primary key (a)

576

566

) select a, 1 as b from t2 ;

567

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

577

568

show create table t1;

578

569

drop table t1;

579

570

580

#--warning 1364

571

--error 1364

581

572

create table t1 (

582

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

573

a varchar(12) collate utf8_bin not null,

583

574

b int not null, primary key (a)

584

575

) select a, 1 as c from t2 ;

585

show create table t1;

586

drop table t1;

587

576

588

#--warning 1364

589

577

create table t1 (

590

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

578

a varchar(12) collate utf8_bin not null,

591

579

b int null, primary key (a)

592

580

) select a, 1 as c from t2 ;

593

show create table t1;

594

drop table t1;

595

596

#--warning 1364

597

create table t1 (

598

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

599

b int not null, primary key (a)

600

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

601

show create table t1;

602

drop table t1;

603

604

#--warning 1364

605

create table t1 (

606

a varchar(12) charset utf8 collate utf8_bin,

607

b int not null, primary key (a)

608

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

581

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

582

show create table t1;

583

drop table t1;

584

585

create table t1 (

586

a varchar(12) collate utf8_bin not null,

587

b int not null, primary key (a)

588

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

589

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

590

show create table t1;

591

drop table t1;

592

593

create table t1 (

594

a varchar(12) collate utf8_bin,

595

b int not null, primary key (a)

596

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

597

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

609

598

show create table t1;

610

599

drop table t1, t2;

611

600

617

606

618

607

#--warning 1364

619

608

create table t2 (

620

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

609

a1 varchar(12) collate utf8_bin not null,

621

610

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

622

611

primary key (a1)

623

612

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

625

614

626

615

#--warning 1364

627

616

create table t2 (

628

a1 varchar(12) charset utf8 collate utf8_bin,

617

a1 varchar(12) collate utf8_bin,

629

618

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

630

619

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

631

620

638

627

639

628

#--warning 1364

640

629

create table t2 (

641

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

630

a1 varchar(12) collate utf8_bin not null,

642

631

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

643

632

primary key (a1)

644

633

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

648

637

649

638

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

650

639

select a1,a2 from t1;

640

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

651

641

show create table t2;

652

642

653

643

drop table t1, t2;

654

644

655

656

# Bug #15316 SET value having comma not correctly handled

657

658

--error 1367

659

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

660

661

# End of 4.1 tests

662

663

664

665

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

666

# platforms

667

668

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

669

show create table t1;

670

alter table t1 max_rows=100;

671

show create table t1;

672

alter table t1 max_rows=100000000000;

673

show create table t1;

674

drop table t1;

675

676

645

677

646

678

647

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

688

657

create table t1 select * from t1;

689

658

# Error which happens before select_create::prepare()

690

659

--error ER_CANT_AGGREGATE_2COLLATIONS

691

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

660

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

692

661

# Error during table creation

693

662

--error ER_KEY_COLUMN_DOES_NOT_EXITS

694

663

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

695

664

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

696

create table t1 (a int);

697

--error ER_WRONG_VALUE_COUNT_ON_ROW

698

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

699

drop table t1;

665

# TODO: This really should be failing...

666

# create table t1 (a int);

667

# --error ER_WRONG_VALUE_COUNT_ON_ROW

668

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

669

# drop table t1;

700

670

# Finally error which happens during insert

701

671

--error ER_DUP_ENTRY

702

672

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

703

673

# What happens if table already exists ?

704

674

create table t1 (i int);

705

--error ER_TABLE_EXISTS_ERROR

706

create table t1 select 1 as i;

675

# TODO: BUG lp:311045

676

#--error ER_TABLE_EXISTS_ERROR

677

#create table t1 select 1 as i;

707

678

create table if not exists t1 select 1 as i;

708

679

select * from t1;

680

drop table t1;

709

681

# Error before select_create::prepare()

710

682

--error ER_CANT_AGGREGATE_2COLLATIONS

711

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

712

select * from t1;

683

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

713

684

# Error which happens during insertion of rows

714

alter table t1 add primary key (i);

715

--error ER_DUP_ENTRY

716

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

717

select * from t1;

718

drop table t1;

685

# TODO: Bug lp:311072

686

# create table t1 (i int);

687

# alter table t1 add primary key (i);

688

# --error ER_DUP_ENTRY

689

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

690

# select * from t1;

691

# drop table t1;

719

692

720

693

721

694

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

735

708

736

709

737

710

738

# CREATE TABLE ... SELECT and LOCK TABLES

739

740

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

741

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

742

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

743

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

744

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

745

create table t1 (i int);

746

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

747

lock tables t1 read;

748

--error ER_TABLE_NOT_LOCKED

749

create table t2 select * from t1;

750

--error ER_TABLE_NOT_LOCKED

751

create table if not exists t2 select * from t1;

752

unlock tables;

753

create table t2 (j int);

754

lock tables t1 read;

755

--error ER_TABLE_NOT_LOCKED

756

create table t2 select * from t1;

757

# This should not be ever allowed as it will undermine

758

# lock-all-at-once approach

759

--error ER_TABLE_NOT_LOCKED

760

create table if not exists t2 select * from t1;

761

unlock tables;

762

lock table t1 read, t2 read;

763

--error ER_TABLE_NOT_LOCKED_FOR_WRITE

764

create table t2 select * from t1;

765

--error ER_TABLE_NOT_LOCKED_FOR_WRITE

766

create table if not exists t2 select * from t1;

767

unlock tables;

768

lock table t1 read, t2 write;

769

--error ER_TABLE_EXISTS_ERROR

770

create table t2 select * from t1;

771

# This is the only case which really works.

772

create table if not exists t2 select * from t1;

773

select * from t1;

774

unlock tables;

775

drop table t2;

776

777

# OTOH CREATE TEMPORARY TABLE ... SELECT should work

778

# well under LOCK TABLES.

779

lock tables t1 read;

780

create temporary table t2 select * from t1;

781

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

782

select * from t2;

783

unlock tables;

784

drop table t1, t2;

785

786

787

788

711

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

789

712

790

713

create table t1 (upgrade int);

940

863

);

941

864

942

865

# Check that the table is not corrupted

866

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

943

867

show create table t1;

944

868

flush tables;

869

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

945

870

show create table t1;

946

871

947

872

# Repeat test using ALTER to add indexes

1087

1012

add key a064_long_123456789_123456789_123456789_123456789_123456789_1234 (

1088

1013

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

1089

1014

1015

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

1090

1016

show create table t1;

1091

1017

flush tables;

1018

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

1092

1019

show create table t1;

1093

1020

1094

1021

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

1118

1045

alter table t1 add key

1119

1046

a001_long_123456789_123456789_123456789_123456789_123456789_12345 (c1);

1120

1047

1048

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

1121

1049

show create table t1;

1122

1050

1123

1051

drop table t1;

1167

1095

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

1168

1096

# precedence

1169

1097

1170

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

1098

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

1171

1099

SHOW INDEX FROM t1;

1172

1100

DROP TABLE t1;

1173

1101

1174

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

1102

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

1175

1103

SHOW INDEX FROM t1;

1176

1104

DROP TABLE t1;

1177

1105

1192

1120

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

1193

1121

# This should give warning

1194

1122

drop table if exists t2;

1195

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

1196

--error ER_DUP_ENTRY

1197

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

1198

SELECT * from t2;

1199

TRUNCATE table t2;

1200

--error ER_DUP_ENTRY

1201

INSERT INTO t2 select * from t1;

1202

SELECT * from t2;

1203

drop table t2;

1123

# TODO: Bug lp:311072

1124

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

1125

#--error ER_DUP_ENTRY

1126

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

1127

#SELECT * from t2;

1128

#TRUNCATE table t2;

1129

#--error ER_DUP_ENTRY

1130

#INSERT INTO t2 select * from t1;

1131

#SELECT * from t2;

1132

#drop table t2;

1204

1133

1205

1134

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

1206

1135

--error ER_DUP_ENTRY

1240

1169

1241

1170

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

1242

1171

1243

set names utf8;

1244

1172

1245

1173

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

1246

1174

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

1257

1185

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

1258

1186

);

1259

1187

1260

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

1261

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

1262

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

1263

1188

1264

# database, table, field, key, view

1189

# database, table, field, key

1265

1190

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

1266

1191

1267

1192

select TABLE_NAME from information_schema.tables where

1273

1198

select INDEX_NAME from information_schema.statistics where

1274

1199

table_schema='test';

1275

1200

1276

select TABLE_NAME from information_schema.views where

1277

table_schema='test';

1278

1201

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

1279

1202

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

1280

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

1281

1282

# procedure, function, trigger

1283

1284

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

1285

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

1286

select TRIGGER_NAME from information_schema.triggers where

1287

trigger_schema='test';

1288

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

1289

--error 1059

1290

create trigger

1291

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

1292

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

1293

--error 1059

1294

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

1295

1296

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

1297

begin

1298

end;

1299

select ROUTINE_NAME from information_schema.routines where

1300

routine_schema='test';

1301

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

1302

--error 1059

1303

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

1304

begin

1305

end;

1306

1307

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

1308

returns int

1309

return 0;

1310

select ROUTINE_NAME from information_schema.routines where

1311

routine_schema='test';

1312

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

1313

--error 1059

1314

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

1315

returns int

1316

return 0;

1317

1318

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

1203

1319

1204

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

1320

set names default;

1321

1322

1323

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

1324

1325

1326

--disable_warnings

1327

drop table if exists t1,t2,t3;

1328

drop function if exists f1;

1329

--enable_warnings

1330

1331

--delimiter |

1332

create function f1() returns int

1333

begin

1334

declare res int;

1335

create temporary table t3 select 1 i;

1336

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

1337

drop temporary table t3;

1338

return res;

1339

end|

1340

--delimiter ;

1341

create table t1 as select 1;

1342

create table t2 as select f1() from t1;

1343

drop table t1,t2;

1344

drop function f1;

1205

1345

1206

1346

1207

1347

1208

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

1348

1209

1210

--error 1478

1349

1211

create table t1 like information_schema.processlist;

1212

create table t1 like information_schema.processlist engine=innodb;

1350

1213

show create table t1;

1351

1214

drop table t1;

1215

--error 1478

1352

1216

create temporary table t1 like information_schema.processlist;

1353

show create table t1;

1354

drop table t1;

1355

create table t1 like information_schema.character_sets;

1217

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

1356

1218

show create table t1;

1357

1219

drop table t1;

1358

1220

1377

1239

c1 INT DEFAULT 12 COMMENT 'column1',

1378

1240

c2 INT NULL COMMENT 'column2',

1379

1241

c3 INT NOT NULL COMMENT 'column3',

1380

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

1242

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

1381

1243

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

1382

1244

c6 VARCHAR(255))

1383

COLLATE latin1_bin;

1245

COLLATE utf8_bin;

1384

1246

1385

1247

--echo

1386

1248

1249

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

1387

1250

SHOW CREATE TABLE t1;

1388

1251

1389

1252

--echo

1392

1255

1393

1256

--echo

1394

1257

1258

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

1395

1259

SHOW CREATE TABLE t2;

1396

1260

1397

1261

--echo

1421

1285

1422

1286

--echo

1423

1287

1424

SET sql_mode = NO_ZERO_DATE;

1425

1426

--echo

1427

--error ER_INVALID_DEFAULT

1428

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

1429

1430

--echo

1431

--error ER_INVALID_DEFAULT

1288

--echo

1289

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

1290

drop table t2;

1291

1292

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

1293

drop table t2;

1294

1295

--echo

1432

1296

CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP);

1297

drop table t2;

1433

1298

1434

1299

--echo

1435

1300

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

1437

1302

1438

1303

--echo

1439

1304

--echo # -- Check ALTER TABLE.

1440

--error ER_INVALID_DEFAULT

1441

1305

ALTER TABLE t1 ADD INDEX(c1);

1442

1306

1443

1307

--echo

1444

1308

--echo # -- Check DATETIME.

1445

SET sql_mode = '';

1446

1447

1309

--echo

1448

1310

1449

1311

CREATE TABLE t3(c1 DATETIME NOT NULL);

1312

--error 1686 # Bad datetime

1450

1313

INSERT INTO t3 VALUES (0);

1451

1314

1452

1315

--echo

1453

SET sql_mode = TRADITIONAL;

1454

1455

--echo

1456

--error ER_TRUNCATED_WRONG_VALUE

1457

1316

ALTER TABLE t3 ADD INDEX(c1);

1458

1317

1459

1318

--echo

1460

1319

--echo # -- Cleanup.

1461

1320

1462

SET sql_mode = '';

1463

1321

DROP TABLE t1;

1464

1322

DROP TABLE t2;

1465

1323

DROP TABLE t3;

1467

1325

--echo

1468

1326

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

1469

1327

1470

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

1471

1472

--echo

1473

--echo # --

1474

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

1475

--echo # --

1476

--echo

1477

1478

--disable_warnings

1479

DROP TABLE IF EXISTS t1;

1480

--enable_warnings

1481

1482

--echo

1483

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

1484

1485

--echo

1486

SHOW CREATE TABLE t1;

1487

1488

--echo

1489

INSERT INTO t1 VALUES();

1490

1491

--echo

1492

SELECT * FROM t1;

1493

1494

--echo

1495

ALTER TABLE t1 MODIFY c1 YEAR DEFAULT 0;

1496

1497

--echo

1498

SHOW CREATE TABLE t1;

1499

1500

--echo

1501

INSERT INTO t1 VALUES();

1502

1503

--echo

1504

SELECT * FROM t1;

1505

1506

--echo

1507

DROP TABLE t1;

1508

1509

--echo

1510

--echo # -- End of Bug#34274

1511

1512

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

1513

1514

--echo

1515

--echo End of 5.1 tests

Older »