~drizzle-trunk/drizzle/development

eval create $temp table t1 (id int not null auto_increment, code int not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=$engine_type;

eval create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=$engine_type;

insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt');

select id, code, name from t1 order by id;

# by innodb is not always the same between runs

eval create $temp table t1 (

id int NOT NULL auto_increment,

parent_id int DEFAULT '0' NOT NULL,

level int DEFAULT '0' NOT NULL,

eval CREATE TABLE t1 (

id int(11) NOT NULL auto_increment,

parent_id int(11) DEFAULT '0' NOT NULL,

level tinyint(4) DEFAULT '0' NOT NULL,

PRIMARY KEY (id),

KEY parent_id (parent_id),

KEY level (level)

111

explain select level,id,parent_id from t1 where level=1;

112

select level,id from t1 where level=1;

113

select level,id,parent_id from t1 where level=1;

114

eval alter table t1 engine=$engine_type;

114

optimize table t1;

115

--replace_column 7 #

116

show keys from t1;

117

drop table t1;

120

# Test replace

121

122

123

eval create $temp table t1 (

124

gesuchnr int DEFAULT '0' NOT NULL,

125

benutzer_id int DEFAULT '0' NOT NULL,

123

eval CREATE TABLE t1 (

124

gesuchnr int(11) DEFAULT '0' NOT NULL,

125

benutzer_id int(11) DEFAULT '0' NOT NULL,

126

PRIMARY KEY (gesuchnr,benutzer_id)

127

) engine=$engine_type;

128

136

# test delete using hidden_primary_key

137

138

139

eval create $temp table t1 (a int) engine=$engine_type;

139

eval create table t1 (a int) engine=$engine_type;

140

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

141

eval alter table t1 ENGINE=$engine_type;

141

optimize table t1;

142

delete from t1 where a = 1;

143

select * from t1;

144

check table t1;

145

drop table t1;

146

147

eval create $temp table t1 (a int,b varchar(20)) engine=$engine_type;

147

eval create table t1 (a int,b varchar(20)) engine=$engine_type;

148

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

149

delete from t1 where a = 1;

150

select * from t1;

158

159

# Test of reading on secondary key with may be null

160

161

eval create $temp table t1 (a int,b varchar(20),key(a)) engine=$engine_type;

161

eval create table t1 (a int,b varchar(20),key(a)) engine=$engine_type;

162

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

163

select * from t1 where a = 1;

164

drop table t1;

169

# Test rollback

170

171

172

eval create $temp table t1 (n int not null primary key) engine=$engine_type;

172

eval create table t1 (n int not null primary key) engine=$engine_type;

173

set autocommit=0;

174

insert into t1 values (4);

175

rollback;

222

# Test for commit and FLUSH TABLES WITH READ LOCK

223

224

225

eval create $temp table t1 (n int not null primary key) engine=$engine_type;

225

eval create table t1 (n int not null primary key) engine=$engine_type;

226

start transaction;

227

insert into t1 values (4);

228

flush tables with read lock;

239

# Testing transactions

240

241

242

eval create $temp table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) engine=$engine_type;

242

eval create table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) engine=$engine_type;

243

begin;

244

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

245

select id as afterbegin_id,nom as afterbegin_nom from t1;

257

# Simple not autocommit test

258

259

260

eval create $temp table t1 (id char(8) not null primary key, val int not null) engine=$engine_type;

260

eval CREATE TABLE t1 (id char(8) not null primary key, val int not null) engine=$engine_type;

261

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

262

-- error ER_DUP_ENTRY

263

insert into t1 values ('pippo', 12); # Gives error

279

# Test of active transactions

280

281

282

eval create $temp table t1 (a integer) engine=$engine_type;

282

eval create table t1 (a integer) engine=$engine_type;

283

start transaction;

284

rename table t1 to t2;

285

eval create $temp table t1 (b integer) engine=$engine_type;

285

eval create table t1 (b integer) engine=$engine_type;

286

insert into t1 values (1);

287

rollback;

288

drop table t1;

294

# The following simple tests failed at some point

295

296

297

eval create $temp table t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) ENGINE=$engine_type;

297

eval CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) ENGINE=$engine_type;

298

INSERT INTO t1 VALUES (1, 'Jochen');

299

select * from t1;

300

drop table t1;

301

302

eval create $temp table t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) ENGINE=$engine_type;

302

eval CREATE TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) ENGINE=$engine_type;

303

set autocommit=0;

304

INSERT INTO t1 SET _userid='marc@anyware.co.uk';

305

COMMIT;

314

315

# Test when reading on part of unique key

316

317

eval create $temp table t1 (

318

user_id int DEFAULT '0' NOT NULL,

317

eval CREATE TABLE t1 (

318

user_id int(10) DEFAULT '0' NOT NULL,

319

name varchar(100),

320

phone varchar(100),

321

ref_email varchar(100) DEFAULT '' NOT NULL,

336

# Test that keys are created in right order

337

338

339

eval create $temp table t1 (a int not null, b int not null,c int not null,

339

eval CREATE TABLE t1 (a int not null, b int not null,c int not null,

340

key(a),primary key(a,b), unique(c),key(a),unique(b)) ENGINE = $engine_type;

341

--replace_column 7 #

342

show index from t1;

346

# Test of ALTER TABLE and innodb tables

347

348

349

eval create $temp table t1 (col1 int not null, col2 char(4) not null, primary key(col1)) ENGINE = $other_engine_type;

349

eval create table t1 (col1 int not null, col2 char(4) not null, primary key(col1)) ENGINE = $other_engine_type;

350

eval alter table t1 engine=$engine_type;

351

insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4');

352

select * from t1;

362

# INSERT INTO innodb tables

363

364

365

eval create $temp table t1 (a int not null , b int, primary key (a)) engine = $engine_type;

366

eval create $temp table t2 (a int not null , b int, primary key (a)) engine = $other_engine_type;

365

eval create table t1 (a int not null , b int, primary key (a)) engine = $engine_type;

366

eval create table t2 (a int not null , b int, primary key (a)) engine = $other_engine_type;

367

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

368

select * from t1;

369

insert into t2 select * from t1;

379

# Search on unique key

380

381

382

eval create $temp table t1 (

383

id int NOT NULL auto_increment,

384

ggid varchar(32) DEFAULT '' NOT NULL,

382

eval CREATE TABLE t1 (

383

id int(11) NOT NULL auto_increment,

384

ggid varchar(32) binary DEFAULT '' NOT NULL,

385

email varchar(64) DEFAULT '' NOT NULL,

386

passwd varchar(32) DEFAULT '' NOT NULL,

386

passwd varchar(32) binary DEFAULT '' NOT NULL,

387

PRIMARY KEY (id),

388

UNIQUE ggid (ggid)

389

) ENGINE=$engine_type;

412

# ORDER BY on not primary key

413

414

415

eval create $temp table t1 (

415

eval CREATE TABLE t1 (

416

user_name varchar(12),

417

password text,

418

subscribed char(1),

419

user_id int DEFAULT '0' NOT NULL,

420

quota bigint,

419

user_id int(11) DEFAULT '0' NOT NULL,

420

quota bigint(20),

421

weight double,

422

access_date date,

423

access_time time,

423

424

approved datetime,

424

dummy_primary_key int NOT NULL auto_increment,

425

dummy_primary_key int(11) NOT NULL auto_increment,

425

426

PRIMARY KEY (dummy_primary_key)

426

427

) ENGINE=$engine_type;

427

INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','2000-09-07 23:06:59',1);

428

INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','2000-09-07 23:06:59',2);

429

INSERT INTO t1 VALUES ('user_2','somepassword','N',2,2,1.4142135623731,'2000-09-07','2000-09-07 23:06:59',3);

430

INSERT INTO t1 VALUES ('user_3','somepassword','Y',3,3,1.7320508075689,'2000-09-07','2000-09-07 23:06:59',4);

431

INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','2000-09-07 23:06:59',5);

432

select user_name, password , subscribed, user_id, quota, weight, access_date, approved, dummy_primary_key from t1 order by user_name;

428

INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','23:06:59','2000-09-07 23:06:59',1);

429

INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','23:06:59','2000-09-07 23:06:59',2);

430

INSERT INTO t1 VALUES ('user_2','somepassword','N',2,2,1.4142135623731,'2000-09-07','23:06:59','2000-09-07 23:06:59',3);

431

INSERT INTO t1 VALUES ('user_3','somepassword','Y',3,3,1.7320508075689,'2000-09-07','23:06:59','2000-09-07 23:06:59',4);

432

INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','23:06:59','2000-09-07 23:06:59',5);

433

select user_name, password , subscribed, user_id, quota, weight, access_date, access_time, approved, dummy_primary_key from t1 order by user_name;

433

434

drop table t1;

434

435

436

437

# Testing of tables without primary keys

437

438

439

eval create $temp table t1 (

440

id int NOT NULL auto_increment,

441

parent_id int DEFAULT '0' NOT NULL,

442

level int DEFAULT '0' NOT NULL,

440

eval CREATE TABLE t1 (

441

id int(11) NOT NULL auto_increment,

442

parent_id int(11) DEFAULT '0' NOT NULL,

443

level tinyint(4) DEFAULT '0' NOT NULL,

443

444

KEY (id),

444

445

KEY parent_id (parent_id),

445

446

KEY level (level)

467

468

469

# Test of index only reads

469

470

eval create $temp table t1 (

471

eval CREATE TABLE t1 (

471

472

sca_code char(6) NOT NULL,

472

473

cat_code char(6) NOT NULL,

473

474

sca_desc varchar(50),

498

499

500

# Test of opening table twice and timestamps

500

501

--echo $temp

502

if(!$using_temp)

503

{

504

502

set @a:=now();

505

eval create $temp table t1 (a int not null, b timestamp not null, primary key (a)) engine=$engine_type;

503

eval CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=$engine_type;

506

504

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

507

505

select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a;

508

506

select a from t1 natural join t1 as t2 where b >= @a order by a;

509

507

update t1 set a=5 where a=1;

510

508

select a from t1;

511

509

drop table t1;

512

}

510

513

511

514

512

# Test with variable length primary key

515

513

516

eval create $temp table t1 (a varchar(100) not null, primary key(a), b int not null) engine=$engine_type;

514

eval create table t1 (a varchar(100) not null, primary key(a), b int not null) engine=$engine_type;

517

515

insert into t1 values("hello",1),("world",2);

518

516

select * from t1 order by b desc;

519

eval alter table t1 ENGINE=$engine_type;

517

optimize table t1;

520

518

--replace_column 7 #

521

519

show keys from t1;

522

520

drop table t1;

524

522

525

523

# Test of create index with NULL columns

526

524

527

eval create $temp table t1 (i int, j int ) ENGINE=$engine_type;

525

eval create table t1 (i int, j int ) ENGINE=$engine_type;

528

526

insert into t1 values (1,2);

529

527

select * from t1 where i=1 and j=2;

530

528

create index ax1 on t1 (i,j);

535

533

# Test min-max optimization

536

534

537

535

538

eval create $temp table t1 (

539

a int NOT NULL,

540

b int NOT NULL,

536

eval CREATE TABLE t1 (

537

a int3 unsigned NOT NULL,

538

b int1 unsigned NOT NULL,

541

539

UNIQUE (a, b)

542

540

) ENGINE = $engine_type;

543

541

549

547

# Test INSERT DELAYED

550

548

551

549

552

eval create $temp table t1 (a int NOT NULL) engine=$engine_type;

550

eval CREATE TABLE t1 (a int unsigned NOT NULL) engine=$engine_type;

553

551

# Can't test this in 3.23

554

552

# INSERT DELAYED INTO t1 VALUES (1);

555

553

INSERT INTO t1 VALUES (1);

561

559

# Crash when using many tables (Test case by Jeremy D Zawodny)

562

560

563

561

564

eval create $temp table t1 (a int primary key,b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int, a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, b1 int, b2 int, b3 int, b4 int, b5 int, b6 int) engine = $engine_type;

562

eval create table t1 (a int primary key,b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int, a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, b1 int, b2 int, b3 int, b4 int, b5 int, b6 int) engine = $engine_type;

565

563

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

566

564

--replace_column 9 #

567

565

explain select * from t1 where a > 0 and a < 50;

568

566

drop table t1;

569

567

570

568

569

# Test lock tables

570

571

572

eval create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=$engine_type;

573

insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');

574

LOCK TABLES t1 WRITE;

575

--error ER_DUP_ENTRY

576

insert into t1 values (99,1,2,'D'),(1,1,2,'D');

577

select id from t1;

578

select id from t1;

579

UNLOCK TABLES;

580

DROP TABLE t1;

581

582

eval create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=$engine_type;

583

insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');

584

LOCK TABLES t1 WRITE;

585

begin;

586

--error ER_DUP_ENTRY

587

insert into t1 values (99,1,2,'D'),(1,1,2,'D');

588

select id from t1;

589

insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D');

590

commit;

591

select id,id3 from t1;

592

UNLOCK TABLES;

593

DROP TABLE t1;

594

595

571

596

# Test prefix key

572

597

573

eval create $temp table t1 (a char(20), unique (a(5))) engine=$engine_type;

598

eval create table t1 (a char(20), unique (a(5))) engine=$engine_type;

574

599

drop table t1;

575

eval create $temp table t1 (a char(20), index (a(5))) engine=$engine_type;

600

eval create table t1 (a char(20), index (a(5))) engine=$engine_type;

576

601

show create table t1;

577

602

drop table t1;

578

603

590

615

drop table t1;

591

616

592

617

#Slashdot bug

593

eval create $temp table t1

618

eval create table t1

594

619

(

595

620

id int auto_increment primary key,

596

621

name varchar(32) not null,

600

625

) engine=$engine_type;

601

626

insert into t1 values (1,'one','one value',101),

602

627

(2,'two','two value',102),(3,'three','three value',103);

628

set insert_id=5;

603

629

replace into t1 (value,name,uid) values ('other value','two',102);

604

630

delete from t1 where uid=102;

631

set insert_id=5;

605

632

replace into t1 (value,name,uid) values ('other value','two',102);

633

set insert_id=6;

606

634

replace into t1 (value,name,uid) values ('other value','two',102);

607

635

select * from t1;

608

636

drop table t1;

616

644

# storage engines) not prevent the dropping of this database.

617

645

618

646

create database mysqltest;

619

eval create $temp table mysqltest.t1 (a int not null) engine= $engine_type;

647

eval create table mysqltest.t1 (a int not null) engine= $engine_type;

620

648

insert into mysqltest.t1 values(1);

621

eval create $temp table mysqltest.t2 (a int not null) engine= $other_engine_type;

649

eval create table mysqltest.t2 (a int not null) engine= $other_engine_type;

622

650

insert into mysqltest.t2 values(1);

623

eval create $temp table mysqltest.t3 (a int not null) engine= $other_engine_type1;

651

eval create table mysqltest.t3 (a int not null) engine= $other_engine_type1;

624

652

insert into mysqltest.t3 values(1);

625

653

commit;

626

654

drop database mysqltest;

633

661

634

662

635

663

set autocommit=0;

636

eval create $temp table t1 (a int not null) engine= $engine_type;

664

eval create table t1 (a int not null) engine= $engine_type;

637

665

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

638

666

truncate table t1;

639

667

commit;

647

675

drop table t1;

648

676

set autocommit=1;

649

677

650

eval create $temp table t1 (a int not null) engine= $engine_type;

678

eval create table t1 (a int not null) engine= $engine_type;

651

679

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

652

680

truncate table t1;

653

681

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

662

690

# Test of how ORDER BY works when doing it on the whole table

663

691

664

692

665

eval create $temp table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=$engine_type;

693

eval create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=$engine_type;

666

694

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

667

695

--replace_column 9 #

668

696

explain select * from t1 order by a;

686

714

# Check describe

687

715

688

716

689

eval create $temp table t1 (t int not null default 1, key (t)) engine=$engine_type;

717

eval create table t1 (t int not null default 1, key (t)) engine=$engine_type;

690

718

desc t1;

691

719

drop table t1;

692

720

693

721

722

# Test of multi-table-delete

723

724

725

eval CREATE TABLE t1 (

726

number bigint(20) NOT NULL default '0',

727

cname char(15) NOT NULL default '',

728

carrier_id smallint(6) NOT NULL default '0',

729

privacy tinyint(4) NOT NULL default '0',

730

last_mod_date timestamp NOT NULL,

731

last_mod_id smallint(6) NOT NULL default '0',

732

last_app_date timestamp NOT NULL,

733

last_app_id smallint(6) default '-1',

734

version smallint(6) NOT NULL default '0',

735

assigned_scps int(11) default '0',

736

status tinyint(4) default '0'

737

) ENGINE=$engine_type;

738

INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,00000000000000,-1,2,3,1);

739

INSERT INTO t1 VALUES (9197722223,'berry',90,3,20020111112809,500,20020102114532,501,4,10,0);

740

INSERT INTO t1 VALUES (650,'San Francisco',0,0,20011227111336,342,00000000000000,-1,1,24,1);

741

INSERT INTO t1 VALUES (302467,'Sue\'s Subshop',90,3,20020109113241,500,20020102115111,501,7,24,0);

742

INSERT INTO t1 VALUES (6014911113,'SudzCarwash',520,1,20020102115234,500,20020102115259,501,33,32768,0);

743

INSERT INTO t1 VALUES (333,'tubs',99,2,20020109113440,501,20020109113440,500,3,10,0);

744

eval CREATE TABLE t2 (

745

number bigint(20) NOT NULL default '0',

746

cname char(15) NOT NULL default '',

747

carrier_id smallint(6) NOT NULL default '0',

748

privacy tinyint(4) NOT NULL default '0',

749

last_mod_date timestamp NOT NULL,

750

last_mod_id smallint(6) NOT NULL default '0',

751

last_app_date timestamp NOT NULL,

752

last_app_id smallint(6) default '-1',

753

version smallint(6) NOT NULL default '0',

754

assigned_scps int(11) default '0',

755

status tinyint(4) default '0'

756

) ENGINE=$engine_type;

757

INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,00000000000000,-1,2,3,1);

758

INSERT INTO t2 VALUES (9197722223,'berry',90,3,20020111112818,500,20020102114532,501,4,10,0);

759

INSERT INTO t2 VALUES (650,'San Francisco',90,0,20020109113158,342,00000000000000,-1,1,24,1);

760

INSERT INTO t2 VALUES (333,'tubs',99,2,20020109113453,501,20020109113453,500,3,10,0);

761

select * from t1;

762

select * from t2;

763

delete t1, t2 from t1 left join t2 on t1.number=t2.number where (t1.carrier_id=90 and t1.number=t2.number) or (t2.carrier_id=90 and t1.number=t2.number) or (t1.carrier_id=90 and t2.number is null);

764

select * from t1;

765

select * from t2;

766

select * from t2;

767

drop table t1,t2;

768

769

694

770

# A simple test with some isolation levels

695

771

# TODO: Make this into a test using replication to really test how

696

772

# this works.

697

773

698

774

699

eval create $temp table t1 (id int not null auto_increment, code int not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=$engine_type;

775

eval create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=$engine_type;

700

776

701

777

BEGIN;

702

778

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

719

795

DROP TABLE t1;

720

796

721

797

798

# Test of multi-table-update

799

800

eval create table t1 (n int(10), d int(10)) engine=$engine_type;

801

eval create table t2 (n int(10), d int(10)) engine=$engine_type;

802

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

803

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

804

UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;

805

select * from t1;

806

select * from t2;

807

drop table t1,t2;

808

809

722

810

# Testing of IFNULL

723

811

724

if(!$using_temp)

725

{

726

eval create $temp table t1 (a int, b int) engine=$engine_type;

812

eval create table t1 (a int, b int) engine=$engine_type;

727

813

insert into t1 values(20,null);

728

814

select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on

729

815

t2.b=t3.a;

733

819

select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on

734

820

t2.b=t3.a order by 1;

735

821

drop table t1;

736

}

822

823

824

# Test of read_through not existing const_table

825

826

827

eval create table t1 (a varchar(10) not null) engine = $other_engine_type;

828

eval create table t2 (b varchar(10) not null unique) engine=$engine_type;

829

select t1.a from t1,t2 where t1.a=t2.b;

830

drop table t1,t2;

831

eval create table t1 (a int not null, b int, primary key (a)) engine = $engine_type;

832

eval create table t2 (a int not null, b int, primary key (a)) engine = $engine_type;

833

insert into t1 values (10, 20);

834

insert into t2 values (10, 20);

835

update t1, t2 set t1.b = 150, t2.b = t1.b where t2.a = t1.a and t1.a = 10;

836

drop table t1,t2;

737

837

738

838

if ($test_foreign_keys)

739

839

{

741

841

# Test of multi-table-delete with foreign key constraints

742

842

743

843

744

eval create $temp table t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=$engine_type;

745

eval create $temp table t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (t1_id) REFERENCES t1(id) ON DELETE CASCADE ) ENGINE=$engine_type;

844

eval CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=$engine_type;

845

eval CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (t1_id) REFERENCES t1(id) ON DELETE CASCADE ) ENGINE=$engine_type;

746

846

insert into t1 set id=1;

747

847

insert into t2 set id=1, t1_id=1;

748

848

delete t1,t2 from t1,t2 where t1.id=t2.t1_id;

749

849

select * from t1;

750

850

select * from t2;

751

851

drop table t2,t1;

752

eval create $temp table t1(id INT NOT NULL, PRIMARY KEY (id)) ENGINE=$engine_type;

753

eval create $temp table t2(id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id) ) ENGINE=$engine_type;

852

eval CREATE TABLE t1(id INT NOT NULL, PRIMARY KEY (id)) ENGINE=$engine_type;

853

eval CREATE TABLE t2(id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id) ) ENGINE=$engine_type;

754

854

INSERT INTO t1 VALUES(1);

755

855

INSERT INTO t2 VALUES(1, 1);

756

856

SELECT * from t1;

769

869

770

870

set autocommit=0;

771

871

772

eval create $temp table t1 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=$engine_type;

773

774

eval create $temp table t2 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=$engine_type;

775

776

eval create $temp table t3 (id1 CHAR(15) NOT NULL, id2 CHAR(15) NOT NULL, PRIMARY KEY(id1, id2)) ENGINE=$engine_type;

872

eval CREATE TABLE t1 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=$engine_type;

873

874

eval CREATE TABLE t2 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=$engine_type;

875

876

eval CREATE TABLE t3 (id1 CHAR(15) NOT NULL, id2 CHAR(15) NOT NULL, PRIMARY KEY(id1, id2)) ENGINE=$engine_type;

777

877

778

878

INSERT INTO t3 VALUES("my-test-1", "my-test-2");

779

879

COMMIT;

800

900

# Check update with conflicting key

801

901

802

902

803

eval create $temp table t1 (a int not null primary key, b int not null, unique (b)) engine=$engine_type;

903

eval CREATE TABLE t1 (a int not null primary key, b int not null, unique (b)) engine=$engine_type;

804

904

INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);

805

905

# We need the a < 1000 test here to quard against the halloween problems

806

906

UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000;

808

908

drop table t1;

809

909

810

910

911

# Test multi update with different join methods

912

913

914

eval CREATE TABLE t1 (a int not null primary key, b int not null, key (b)) engine=$engine_type;

915

eval CREATE TABLE t2 (a int not null primary key, b int not null, key (b)) engine=$engine_type;

916

INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10),(11,11),(12,12);

917

INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);

918

919

# Full join, without key

920

update t1,t2 set t1.a=t1.a+100;

921

select * from t1;

922

923

# unique key

924

update t1,t2 set t1.a=t1.a+100 where t1.a=101;

925

select * from t1;

926

927

# ref key

928

update t1,t2 set t1.b=t1.b+10 where t1.b=2;

929

select * from t1;

930

931

# Range key (in t1)

932

update t1,t2 set t1.b=t1.b+2,t2.b=t1.b+10 where t1.b between 3 and 5 and t1.a=t2.a+100;

933

select * from t1;

934

select * from t2;

935

936

drop table t1,t2;

937

eval CREATE TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=$other_non_trans_engine_type;

938

eval CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=$engine_type;

939

SET AUTOCOMMIT=0;

940

INSERT INTO t1 ( B_ID ) VALUES ( 1 );

941

INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );

942

ROLLBACK;

943

SELECT * FROM t1;

944

drop table t1,t2;

945

eval create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) engine = $engine_type;

946

insert into t1 values (1,0,4), (2,1,3), (3,2,1), (4,1,2);

947

select distinct parent,child from t1 order by parent;

948

drop table t1;

949

950

811

951

# Test that MySQL priorities clustered indexes

812

952

813

eval create $temp table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=$engine_type;

814

eval create $temp table t2 (a int not null auto_increment primary key, b int) ENGINE = $other_engine_type;

953

eval create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=$engine_type;

954

eval create table t2 (a int not null auto_increment primary key, b int) ENGINE = $other_engine_type;

815

955

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

816

956

insert into t2 (a) select b from t1;

817

957

insert into t1 (b) select b from t2;

843

983

# Test of UPDATE ... ORDER BY

844

984

845

985

846

eval create $temp table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=$engine_type;

986

eval create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=$engine_type;

847

987

848

988

insert into t1 (id) values (null),(null),(null),(null),(null);

849

989

update t1 set fk=69 where fk is null order by id limit 1;

850

990

SELECT * from t1;

851

991

drop table t1;

852

992

853

eval create $temp table t1 (a int not null, b int not null, key (a)) engine=$engine_type;

993

eval create table t1 (a int not null, b int not null, key (a)) engine=$engine_type;

854

994

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

855

995

SET @tmp=0;

856

996

update t1 set b=(@tmp:=@tmp+1) order by a;

861

1001

drop table t1;

862

1002

863

1003

1004

# Test of multi-table-updates (bug #1980).

1005

1006

1007

eval create table t1 ( c char(8) not null ) engine=$engine_type;

1008

insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');

1009

insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');

1010

1011

alter table t1 add b char(8) not null;

1012

alter table t1 add a char(8) not null;

1013

alter table t1 add primary key (a,b,c);

1014

update t1 set a=c, b=c;

1015

1016

eval create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) engine=$engine_type;

1017

insert into t2 select * from t1;

1018

1019

delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;

1020

drop table t1,t2;

1021

1022

864

1023

# test autoincrement with TRUNCATE

865

1024

866

1025

867

1026

SET AUTOCOMMIT=1;

868

eval create $temp table t1 (a integer auto_increment primary key) engine=$engine_type;

1027

eval create table t1 (a integer auto_increment primary key) engine=$engine_type;

869

1028

insert into t1 (a) values (NULL),(NULL);

870

1029

truncate table t1;

871

1030

insert into t1 (a) values (NULL),(NULL);

879

1038

# Test dictionary handling with spaceand quoting

880

1039

881

1040

882

eval create $temp table t1 (`id 1` INT NOT NULL, PRIMARY KEY (`id 1`)) ENGINE=$engine_type;

883

eval create $temp table t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (`t1_id`) REFERENCES `t1`(`id 1`) ON DELETE CASCADE ) ENGINE=$engine_type;

1041

eval CREATE TABLE t1 (`id 1` INT NOT NULL, PRIMARY KEY (`id 1`)) ENGINE=$engine_type;

1042

eval CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (`t1_id`) REFERENCES `t1`(`id 1`) ON DELETE CASCADE ) ENGINE=$engine_type;

884

1043

#show create table t2;

885

1044

drop table t2,t1;

886

1045

888

1047

# Test of multi updated and foreign keys

889

1048

890

1049

891

eval create $temp table `t1` (`id` int not null ,primary key ( `id` )) engine = $engine_type;

1050

eval create table `t1` (`id` int( 11 ) not null ,primary key ( `id` )) engine = $engine_type;

892

1051

insert into `t1`values ( 1 ) ;

893

eval create $temp table `t2` (`id` int not null default '0',unique key `id` ( `id` ) ,constraint `t1_id_fk` foreign key ( `id` ) references `t1` (`id` )) engine = $engine_type;

1052

eval create table `t2` (`id` int( 11 ) not null default '0',unique key `id` ( `id` ) ,constraint `t1_id_fk` foreign key ( `id` ) references `t1` (`id` )) engine = $engine_type;

894

1053

insert into `t2`values ( 1 ) ;

895

eval create $temp table `t3` (`id` int not null default '0',key `id` ( `id` ) ,constraint `t2_id_fk` foreign key ( `id` ) references `t2` (`id` )) engine = $engine_type;

1054

eval create table `t3` (`id` int( 11 ) not null default '0',key `id` ( `id` ) ,constraint `t2_id_fk` foreign key ( `id` ) references `t2` (`id` )) engine = $engine_type;

896

1055

insert into `t3`values ( 1 ) ;

897

1056

--error 1451

898

1057

delete t3,t2,t1 from t1,t2,t3 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;

905

1064

906

1065

# test for recursion depth limit

907

1066

908

eval create $temp table t1(

1067

eval create table t1(

909

1068

id int primary key,

910

1069

pid int,

911

1070

index(pid),

925

1084

# Test timestamps

926

1085

927

1086

928

eval create $temp table t1 (col1 int)ENGINE=$engine_type;

929

eval create $temp table t2 (col1 int,stamp TIMESTAMP,INDEX stamp_idx

1087

eval CREATE TABLE t1 (col1 int(1))ENGINE=$engine_type;

1088

eval CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx

930

1089

(stamp))ENGINE=$engine_type;

931

1090

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

932

1091

# Note that timestamp 3 is wrong

933

--error 1685 # Bad timestamp

934

1092

insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000);

935

1093

SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp <

936

1094

'20020204120000' GROUP BY col1;

940

1098

# Test by Francois MASUREL

941

1099

942

1100

943

if(!$using_temp)

944

{

945

eval create $temp table t1 (

946

`id` int NOT NULL auto_increment,

947

`id_object` int default '0',

948

`id_version` int NOT NULL default '1',

1101

eval CREATE TABLE t1 (

1102

`id` int(10) unsigned NOT NULL auto_increment,

1103

`id_object` int(10) unsigned default '0',

1104

`id_version` int(10) unsigned NOT NULL default '1',

949

1105

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

950

1106

`description` text,

951

1107

PRIMARY KEY (`id`),

955

1111

956

1112

INSERT INTO t1 VALUES("6", "3382", "9", "Test", NULL), ("7", "102", "5", "Le Pekin (Test)", NULL),("584", "1794", "4", "Test de resto", NULL),("837", "1822", "6", "Test 3", NULL),("1119", "3524", "1", "Societe Test", NULL),("1122", "3525", "1", "Fournisseur Test", NULL);

957

1113

958

eval create $temp table t2 (

959

`id` int NOT NULL auto_increment,

960

`id_version` int NOT NULL default '1',

1114

eval CREATE TABLE t2 (

1115

`id` int(10) unsigned NOT NULL auto_increment,

1116

`id_version` int(10) unsigned NOT NULL default '1',

961

1117

PRIMARY KEY (`id`),

962

1118

KEY `id_version` (`id_version`)

963

1119

) ENGINE=$engine_type;

968

1124

(SELECT t1.id_object as id_object FROM t1 WHERE t1.`label` LIKE '%test%') AS lbl

969

1125

ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object);

970

1126

drop table t1,t2;

971

}

972

1127

973

1128

# Live checksum feature available + enabled

974

eval create $temp table t1 (a int, b varchar(200), c text not null) engine=$other_live_chks_engine_type;

1129

eval create table t1 (a int, b varchar(200), c text not null) checksum=1 engine=$other_live_chks_engine_type;

975

1130

# Live checksum feature available + disabled

976

eval create $temp table t2 (a int, b varchar(200), c text not null) engine=$other_live_chks_engine_type;

1131

eval create table t2 (a int, b varchar(200), c text not null) checksum=0 engine=$other_live_chks_engine_type;

977

1132

978

1133

# Live checksum feature not available + enabled

979

eval create $temp table t3 (a int, b varchar(200), c varchar(200) not null) engine=$other_non_live_chks_engine_type;

1134

eval create table t3 (a int, b varchar(200), c varchar(200) not null) checksum=1 engine=$other_non_live_chks_engine_type;

980

1135

# Live checksum feature not available + disabled

981

eval create $temp table t4 (a int, b varchar(200), c varchar(200) not null) engine=$other_non_live_chks_engine_type;

1136

eval create table t4 (a int, b varchar(200), c varchar(200) not null) checksum=0 engine=$other_non_live_chks_engine_type;

982

1137

983

1138

# Live checksum feature probably available + enabled

984

eval create $temp table t5 (a int, b varchar(200), c text not null) engine=$engine_type;

1139

eval create table t5 (a int, b varchar(200), c text not null) checksum=1 engine=$engine_type;

985

1140

# Live checksum feature probably available + disabled

986

eval create $temp table t6 (a int, b varchar(200), c text not null) engine=$engine_type;

1141

eval create table t6 (a int, b varchar(200), c text not null) checksum=0 engine=$engine_type;

987

1142

988

1143

insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, "");

989

1144

insert t2 select * from t1;

991

1146

insert t4 select * from t1;

992

1147

insert t5 select * from t1;

993

1148

insert t6 select * from t1;

994

checksum table t1, t2, t3, t4, t5, t6, t7;

995

checksum table t1, t2, t3, t4, t5, t6, t7;

996

checksum table t1, t2, t3, t4, t5, t6, t7;

1149

checksum table t1, t2, t3, t4, t5, t6, t7 quick;

1150

checksum table t1, t2, t3, t4, t5, t6, t7;

1151

checksum table t1, t2, t3, t4, t5, t6, t7 extended;

997

1152

# #show table status;

998

1153

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

999

1154

1001

1156

# Test problem with refering to different fields in same table in UNION

1002

1157

# (Bug#2552: UNION returns NULL instead of expected value (innoDB only tables))

1003

1158

1004

if(!$using_temp)

1005

{

1006

eval create $temp table t1 (id int, name char(10) not null, name2 char(10) not null) engine=$engine_type;

1159

eval create table t1 (id int, name char(10) not null, name2 char(10) not null) engine=$engine_type;

1007

1160

insert into t1 values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt');

1008

1161

select trim(name2) from t1 union all select trim(name) from t1 union all select trim(id) from t1;

1009

1162

drop table t1;

1010

}

1011

1012

# Bug#2160: Extra error message for create $temp table LIKE with InnoDB

1013

1014

eval create $temp table t1 (a int) engine=$engine_type;

1015

--error 1005

1163

1164

1165

# Bug#2160: Extra error message for CREATE TABLE LIKE with InnoDB

1166

1167

eval create table t1 (a int) engine=$engine_type;

1016

1168

create table t2 like t1;

1017

create table t2 like t1 engine=innodb;

1018

1169

show create table t2;

1019

1170

drop table t1,t2;

1020

1171

1024

1175

# Test of automaticly created foreign keys

1025

1176

1026

1177

1027

eval create $temp table t1 (id int not null, id2 int not null, unique (id,id2)) engine=$engine_type;

1028

eval create $temp table t2 (id int not null, constraint t1_id_fk foreign key ( id ) references t1 (id)) engine = $engine_type;

1178

eval create table t1 (id int(11) not null, id2 int(11) not null, unique (id,id2)) engine=$engine_type;

1179

eval create table t2 (id int(11) not null, constraint t1_id_fk foreign key ( id ) references t1 (id)) engine = $engine_type;

1029

1180

show create table t1;

1030

1181

show create table t2;

1031

1182

create index id on t2 (id);

1038

1189

show create table t2;

1039

1190

drop table t2;

1040

1191

1041

eval create $temp table t2 (id int not null, id2 int not null, constraint t1_id_fk foreign key (id,id2) references t1 (id,id2)) engine = $engine_type;

1192

eval create table t2 (id int(11) not null, id2 int(11) not null, constraint t1_id_fk foreign key (id,id2) references t1 (id,id2)) engine = $engine_type;

1042

1193

show create table t2;

1043

1194

create unique index id on t2 (id,id2);

1044

1195

show create table t2;

1045

1196

drop table t2;

1046

1197

1047

1198

# Check foreign key columns created in different order than key columns

1048

eval create $temp table t2 (id int not null, id2 int not null, unique (id,id2),constraint t1_id_fk foreign key (id2,id) references t1 (id,id2)) engine = $engine_type;

1049

show create table t2;

1050

drop table t2;

1051

1052

eval create $temp table t2 (id int not null, id2 int not null, unique (id,id2), constraint t1_id_fk foreign key (id) references t1 (id)) engine = $engine_type;

1053

show create table t2;

1054

drop table t2;

1055

1056

eval create $temp table t2 (id int not null, id2 int not null, unique (id,id2),constraint t1_id_fk foreign key (id2,id) references t1 (id,id2)) engine = $engine_type;

1057

show create table t2;

1058

drop table t2;

1059

1060

eval create $temp table t2 (id int not null auto_increment, id2 int not null, constraint t1_id_fk foreign key (id) references t1 (id), primary key (id), index (id,id2)) engine = $engine_type;

1061

show create table t2;

1062

drop table t2;

1063

1064

eval create $temp table t2 (id int not null auto_increment, id2 int not null, constraint t1_id_fk foreign key (id) references t1 (id)) engine= $engine_type;

1199

eval create table t2 (id int(11) not null, id2 int(11) not null, unique (id,id2),constraint t1_id_fk foreign key (id2,id) references t1 (id,id2)) engine = $engine_type;

1200

show create table t2;

1201

drop table t2;

1202

1203

eval create table t2 (id int(11) not null, id2 int(11) not null, unique (id,id2), constraint t1_id_fk foreign key (id) references t1 (id)) engine = $engine_type;

1204

show create table t2;

1205

drop table t2;

1206

1207

eval create table t2 (id int(11) not null, id2 int(11) not null, unique (id,id2),constraint t1_id_fk foreign key (id2,id) references t1 (id,id2)) engine = $engine_type;

1208

show create table t2;

1209

drop table t2;

1210

1211

eval create table t2 (id int(11) not null auto_increment, id2 int(11) not null, constraint t1_id_fk foreign key (id) references t1 (id), primary key (id), index (id,id2)) engine = $engine_type;

1212

show create table t2;

1213

drop table t2;

1214

1215

eval create table t2 (id int(11) not null auto_increment, id2 int(11) not null, constraint t1_id_fk foreign key (id) references t1 (id)) engine= $engine_type;

1065

1216

show create table t2;

1066

1217

alter table t2 add index id_test (id), add index id_test2 (id,id2);

1067

1218

show create table t2;

1071

1222

1072

1223

# Clean up filename -- embedded server reports whole path without .frm,

1073

1224

# regular server reports relative path with .frm (argh!)

1074

--replace_result \\ / $DRIZZLE_TEST_DIR . /var/master-data/ / t2.frm t2

1225

--replace_result \\ / $MYSQL_TEST_DIR . /var/master-data/ / t2.frm t2

1075

1226

--error 1005

1076

eval create $temp table t2 (id int not null, id2 int not null, constraint t1_id_fk foreign key (id2,id) references t1 (id)) engine = $engine_type;

1227

eval create table t2 (id int(11) not null, id2 int(11) not null, constraint t1_id_fk foreign key (id2,id) references t1 (id)) engine = $engine_type;

1077

1228

1078

1229

# bug#3749

1079

1230

1080

eval create $temp table t2 (a int auto_increment primary key, b int, index(b), foreign key (b) references t1(id), unique(b)) engine=$engine_type;

1231

eval create table t2 (a int auto_increment primary key, b int, index(b), foreign key (b) references t1(id), unique(b)) engine=$engine_type;

1081

1232

show create table t2;

1082

1233

drop table t2;

1083

eval create $temp table t2 (a int auto_increment primary key, b int, foreign key (b) references t1(id), foreign key (b) references t1(id), unique(b)) engine=$engine_type;

1234

eval create table t2 (a int auto_increment primary key, b int, foreign key (b) references t1(id), foreign key (b) references t1(id), unique(b)) engine=$engine_type;

1084

1235

show create table t2;

1085

1236

drop table t2, t1;

1086

1237

}

1096

1247

show status like "binlog_cache_use";

1097

1248

show status like "binlog_cache_disk_use";

1098

1249

1099

eval create $temp table t1 (a int) engine=$engine_type;

1250

eval create table t1 (a int) engine=$engine_type;

1100

1251

1101

1252

# Now we are going to create transaction which is long enough so its

1102

1253

# transaction binlog will be flushed to disk...

1126

1277

# Bug #6126: Duplicate columns in keys gives misleading error message

1127

1278

1128

1279

--error 1060

1129

eval create $temp table t1 (c char(10), index (c,c)) engine=$engine_type;

1130

--error 1060

1131

eval create $temp table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=$engine_type;

1132

--error 1060

1133

eval create $temp table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=$engine_type;

1134

--error 1060

1135

eval create $temp table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=$engine_type;

1136

eval create $temp table t1 (c1 char(10), c2 char(10)) engine=$engine_type;

1280

eval create table t1 (c char(10), index (c,c)) engine=$engine_type;

1281

--error 1060

1282

eval create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=$engine_type;

1283

--error 1060

1284

eval create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=$engine_type;

1285

--error 1060

1286

eval create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=$engine_type;

1287

eval create table t1 (c1 char(10), c2 char(10)) engine=$engine_type;

1137

1288

--error 1060

1138

1289

alter table t1 add key (c1,c1);

1139

1290

--error 1060

1148

1299

# Bug #4082: integer truncation

1149

1300

1150

1301

1151

eval create $temp table t1(a int, b int) engine=$engine_type;

1302

eval create table t1(a int(1) , b int(1)) engine=$engine_type;

1152

1303

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

1153

1304

select distinct concat(a, b) from t1;

1154

1305

drop table t1;

1160

1311

# engines does not fail

1161

1312

1162

1313

1163

eval create $temp table t1 ( a char(10) ) ENGINE=$engine_type;

1314

eval CREATE TABLE t1 ( a char(10) ) ENGINE=$engine_type;

1164

1315

--error 1214

1165

1316

SELECT a FROM t1 WHERE MATCH (a) AGAINST ('test' IN BOOLEAN MODE);

1166

1317

DROP TABLE t1;

1173

1324

1174

1325

1175

1326

--disable_warnings

1176

eval create $temp table t1 (a_id int NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=$engine_type DEFAULT CHARSET=latin1;

1327

eval CREATE TABLE t1 (a_id tinyint(4) NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=$engine_type DEFAULT CHARSET=latin1;

1177

1328

INSERT INTO t1 VALUES (1),(2),(3);

1178

eval create $temp table t2 (b_id int NOT NULL default '0',b_a int NOT NULL default '0', PRIMARY KEY (b_id), KEY (b_a),

1329

eval CREATE TABLE t2 (b_id tinyint(4) NOT NULL default '0',b_a tinyint(4) NOT NULL default '0', PRIMARY KEY (b_id), KEY (b_a),

1179

1330

CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=$engine_type DEFAULT CHARSET=latin1;

1180

1331

--enable_warnings

1181

1332

INSERT INTO t2 VALUES (1,1),(2,1),(3,1),(4,2),(5,2);

1195

1346

select * from t1;

1196

1347

drop table t1;

1197

1348

# Show that it works with permanent tables too.

1198

eval create $temp table t1 (a int) engine=$engine_type;

1349

eval create table t1 (a int) engine=$engine_type;

1199

1350

insert into t1 values (4711);

1200

1351

truncate t1;

1201

1352

insert into t1 values (42);

1206

1357

# Bug #13025 Server crash during filesort

1207

1358

1208

1359

1209

eval create $temp table t1 (a int not null, b int not null, c blob not null, d int not null, e int, primary key (a,b,c(255),d)) engine=$engine_type;

1360

eval create table t1 (a int not null, b int not null, c blob not null, d int not null, e int, primary key (a,b,c(255),d)) engine=$engine_type;

1210

1361

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

1211

1362

select * from t1 order by a,b,c,d;

1212

1363

explain select * from t1 order by a,b,c,d;

1216

1367

# BUG#11039,#13218 Wrong key length in min()

1217

1368

1218

1369

1219

eval create $temp table t1 (a char(1), b char(1), key(a, b)) engine=$engine_type;

1370

eval create table t1 (a char(1), b char(1), key(a, b)) engine=$engine_type;

1220

1371

insert into t1 values ('8', '6'), ('4', '7');

1221

1372

select min(a) from t1;

1222

1373

select min(b) from t1 where a='8';

1228

1379

# range optimizer problem

1229

1380

1230

1381

1231

eval create $temp table t1 (x bigint not null primary key) engine=$engine_type;

1232

# The below is out of range for a BIGINT (signed)

1233

--error 1264

1382

eval create table t1 (x bigint unsigned not null primary key) engine=$engine_type;

1234

1383

insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1);

1235

1384

select * from t1;

1236

1385

select count(*) from t1 where x>0;

1318

1467

1319

1468

# Clean up filename -- embedded server reports whole path without .frm,

1320

1469

# regular server reports relative path with .frm (argh!)

1321

# @TODO The below fails because it assumes latin1

1322

# as the charset. Possibly re-enable a similar test

1323

#--replace_result \\ / $DRIZZLE_TEST_DIR . /var/master-data/ / t1.frm t1

1324

#create $temp table t1 (v varchar(65530), key(v));

1325

#drop table t1;

1326

#create $temp table t1 (v varchar(65536));

1327

#show create table t1;

1328

#drop table t1;

1329

#create $temp table t1 (v varchar(65530) character set utf8);

1330

#show create table t1;

1331

#drop table t1;

1332

1333

#eval set storage_engine=$default;

1334

1470

--replace_result \\ / $MYSQL_TEST_DIR . /var/master-data/ / t1.frm t1

1471

create table t1 (v varchar(65530), key(v));

1472

drop table t1;

1473

create table t1 (v varchar(65536));

1474

show create table t1;

1475

drop table t1;

1476

create table t1 (v varchar(65530) character set utf8);

1477

show create table t1;

1478

drop table t1;

1479

1480

eval set storage_engine=$default;

1481

1335

1482

# InnoDB specific varchar tests

1336

#eval create $temp table t1 (v varchar(16384)) engine=$engine_type;

1337

#drop table t1;

1483

eval create table t1 (v varchar(16384)) engine=$engine_type;

1484

drop table t1;

1338

1485

1339

1486

1340

1487

# BUG#11039 Wrong key length in min()

1341

1488

1342

1489

1343

eval create $temp table t1 (a char(1), b char(1), key(a, b)) engine=$engine_type;

1490

eval create table t1 (a char(1), b char(1), key(a, b)) engine=$engine_type;

1344

1491

insert into t1 values ('8', '6'), ('4', '7');

1345

1492

select min(a) from t1;

1346

1493

select min(b) from t1 where a='8';

1350

1497

# Bug #11080 & #11005 Multi-row REPLACE fails on a duplicate key error

1351

1498

1352

1499

1353

eval create $temp table t1 ( `a` int NOT NULL auto_increment, `b` int default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=$engine_type;

1500

eval CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=$engine_type;

1354

1501

insert into t1 (b) values (1);

1355

1502

replace into t1 (b) values (2), (1), (3);

1356

1503

select * from t1;

1362

1509

select * from t1;

1363

1510

drop table t1;

1364

1511

1365

eval create $temp table t1 (rowid int not null auto_increment, val int not null,primary

1512

eval create table t1 (rowid int not null auto_increment, val int not null,primary

1366

1513

key (rowid), unique(val)) engine=$engine_type;

1367

1514

replace into t1 (val) values ('1'),('2');

1368

1515

replace into t1 (val) values ('1'),('2');

1377

1524

# Test that update does not change internal auto-increment value

1378

1525

1379

1526

1380

eval create $temp table t1 (a int not null auto_increment primary key, val int) engine=$engine_type;

1527

eval create table t1 (a int not null auto_increment primary key, val int) engine=$engine_type;

1381

1528

insert into t1 (val) values (1);

1382

1529

update t1 set a=2 where a=1;

1383

1530

# We should get the following error because InnoDB does not update the counter

1393

1540

1394

1541

1395

1542

--disable_warnings

1396

eval create $temp table t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=$engine_type;

1543

eval CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=$engine_type;

1397

1544

--enable_warnings

1398

1545

INSERT INTO t1 (GRADE) VALUES (151),(252),(343);

1399

1546

SELECT GRADE FROM t1 WHERE GRADE > 160 AND GRADE < 300;

1400

1547

SELECT GRADE FROM t1 WHERE GRADE= 151;

1401

1548

DROP TABLE t1;

1402

1549

1550

1551

# Bug #12340 multitable delete deletes only one record

1552

1553

eval create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=$engine_type;

1554

eval create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=$engine_type;

1555

insert into t2 values ('aa','cc');

1556

insert into t1 values ('aa','bb'),('aa','cc');

1557

delete t1 from t1,t2 where f1=f3 and f4='cc';

1558

select * from t1;

1559

drop table t1,t2;

1560

1403

1561

if ($test_foreign_keys)

1404

1562

{

1405

1563

1407

1565

# (bug #11946)

1408

1566

1409

1567

1410

eval create $temp table t1 (

1568

eval CREATE TABLE t1 (

1411

1569

id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)

1412

1570

) ENGINE=$engine_type;

1413

1571

1414

eval create $temp table t2 (

1572

eval CREATE TABLE t2 (

1415

1573

id INTEGER NOT NULL,

1416

1574

FOREIGN KEY (id) REFERENCES t1 (id)

1417

1575

) ENGINE=$engine_type;

1431

1589

DROP TABLE t2, t1;

1432

1590

1433

1591

# Test that foreign keys in temporary tables are not accepted (bug #12084)

1434

eval create $temp table t1

1592

eval CREATE TABLE t1

1435

1593

(

1436

1594

id INT PRIMARY KEY

1437

1595

) ENGINE=$engine_type;

1458

1616

1459

1617

1460

1618

# prefix index

1461

eval create $temp table t1 (col1 varchar(2000), index (col1(767)))

1619

eval create table t1 (col1 varchar(2000), index (col1(767)))

1462

1620

character set = latin1 engine = $engine_type;

1463

1621

1464

1622

# normal indexes

1465

eval create $temp table t2 (col1 char(255), index (col1))

1466

character set = latin1 engine = $engine_type;

1467

eval create $temp table t3 (col1 binary(255), index (col1))

1468

character set = latin1 engine = $engine_type;

1469

eval create $temp table t4 (col1 varchar(767), index (col1))

1470

character set = latin1 engine = $engine_type;

1471

eval create $temp table t5 (col1 varchar(767) primary key)

1472

character set = latin1 engine = $engine_type;

1473

eval create $temp table t6 (col1 varbinary(767) primary key)

1474

character set = latin1 engine = $engine_type;

1475

eval create $temp table t7 (col1 text, index(col1(767)))

1476

character set = latin1 engine = $engine_type;

1477

eval create $temp table t8 (col1 blob, index(col1(767)))

1623

eval create table t2 (col1 char(255), index (col1))

1624

character set = latin1 engine = $engine_type;

1625

eval create table t3 (col1 binary(255), index (col1))

1626

character set = latin1 engine = $engine_type;

1627

eval create table t4 (col1 varchar(767), index (col1))

1628

character set = latin1 engine = $engine_type;

1629

eval create table t5 (col1 varchar(767) primary key)

1630

character set = latin1 engine = $engine_type;

1631

eval create table t6 (col1 varbinary(767) primary key)

1632

character set = latin1 engine = $engine_type;

1633

eval create table t7 (col1 text, index(col1(767)))

1634

character set = latin1 engine = $engine_type;

1635

eval create table t8 (col1 blob, index(col1(767)))

1478

1636

character set = latin1 engine = $engine_type;

1479

1637

1480

1638

1481

1639

# multi-column indexes are allowed to be longer

1482

eval create $temp table t9 (col1 varchar(512), col2 varchar(512), index(col1, col2))

1640

eval create table t9 (col1 varchar(512), col2 varchar(512), index(col1, col2))

1483

1641

character set = latin1 engine = $engine_type;

1484

1642

1485

1643

show create table t9;

1487

1645

drop table t1, t2, t3, t4, t5, t6, t7, t8, t9;

1488

1646

1489

1647

# these should have their index length trimmed

1490

eval create $temp table t1 (col1 varchar(768), index(col1))

1491

character set = latin1 engine = $engine_type;

1492

eval create $temp table t2 (col1 varbinary(768), index(col1))

1493

character set = latin1 engine = $engine_type;

1494

eval create $temp table t3 (col1 text, index(col1(768)))

1495

character set = latin1 engine = $engine_type;

1496

eval create $temp table t4 (col1 blob, index(col1(768)))

1648

eval create table t1 (col1 varchar(768), index(col1))

1649

character set = latin1 engine = $engine_type;

1650

eval create table t2 (col1 varbinary(768), index(col1))

1651

character set = latin1 engine = $engine_type;

1652

eval create table t3 (col1 text, index(col1(768)))

1653

character set = latin1 engine = $engine_type;

1654

eval create table t4 (col1 blob, index(col1(768)))

1497

1655

character set = latin1 engine = $engine_type;

1498

1656

1499

1657

show create table t1;

1511

1669

1512

1670

# these should be refused

1513

1671

--error 1071

1514

eval create $temp table t1 (col1 varchar(768) primary key)

1515

character set = latin1 engine = $engine_type;

1516

--error 1071

1517

eval create $temp table t2 (col1 varbinary(768) primary key)

1518

character set = latin1 engine = $engine_type;

1519

--error 1071

1520

eval create $temp table t3 (col1 text, primary key(col1(768)))

1521

character set = latin1 engine = $engine_type;

1522

--error 1071

1523

eval create $temp table t4 (col1 blob, primary key(col1(768)))

1672

eval create table t1 (col1 varchar(768) primary key)

1673

character set = latin1 engine = $engine_type;

1674

--error 1071

1675

eval create table t2 (col1 varbinary(768) primary key)

1676

character set = latin1 engine = $engine_type;

1677

--error 1071

1678

eval create table t3 (col1 text, primary key(col1(768)))

1679

character set = latin1 engine = $engine_type;

1680

--error 1071

1681

eval create table t4 (col1 blob, primary key(col1(768)))

1524

1682

character set = latin1 engine = $engine_type;

1525

1683

1526

1684

}

1531

1689

# Test improved foreign key error messages (bug #3443)

1532

1690

1533

1691

1534

eval create $temp table t1

1692

eval CREATE TABLE t1

1535

1693

(

1536

1694

id INT PRIMARY KEY

1537

1695

) ENGINE=$engine_type;

1538

1696

1539

eval create $temp table t2

1697

eval CREATE TABLE t2

1540

1698

(

1541

1699

v INT,

1542

1700

CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id)

1573

1731

connect (a,localhost,root,,);

1574

1732

connect (b,localhost,root,,);

1575

1733

connection a;

1576

eval create $temp table t1(a int not null) engine=$engine_type DEFAULT CHARSET=latin1;

1734

eval create table t1(a int not null) engine=$engine_type DEFAULT CHARSET=latin1;

1577

1735

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

1578

1736

set autocommit=0;

1579

1737

checksum table t1;

1593

1751

# autocommit = 1

1594

1752

1595

1753

connection a;

1596

eval create $temp table t1(a int not null) engine=$engine_type DEFAULT CHARSET=latin1;

1754

eval create table t1(a int not null) engine=$engine_type DEFAULT CHARSET=latin1;

1597

1755

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

1598

1756

set autocommit=1;

1599

1757

checksum table t1;

1619

1777

# test that FKs between invalid types are not accepted

1620

1778

1621

1779

set foreign_key_checks=0;

1622

eval create $temp table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = $engine_type;

1780

eval create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = $engine_type;

1623

1781

--replace_result $MYSQLTEST_VARDIR . master-data/ ''

1624

1782

-- error 1005

1625

eval create $temp table t1(a char(10) primary key, b varchar(20)) engine = $engine_type;

1783

eval create table t1(a char(10) primary key, b varchar(20)) engine = $engine_type;

1626

1784

set foreign_key_checks=1;

1627

1785

drop table t2;

1628

1786

1630

1788

# when f_k_c is 0

1631

1789

1632

1790

set foreign_key_checks=0;

1633

eval create $temp table t1(a varchar(10) primary key) engine = $engine_type DEFAULT CHARSET=latin1;

1791

eval create table t1(a varchar(10) primary key) engine = $engine_type DEFAULT CHARSET=latin1;

1634

1792

--replace_result $MYSQLTEST_VARDIR . master-data/ ''

1635

1793

-- error 1005

1636

eval create $temp table t2 (a varchar(10), foreign key (a) references t1(a)) engine = $engine_type DEFAULT CHARSET=utf8;

1794

eval create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = $engine_type DEFAULT CHARSET=utf8;

1637

1795

set foreign_key_checks=1;

1638

1796

drop table t1;

1639

1797

1640

1798

# test that invalid datatype conversions with ALTER are not allowed

1641

1799

1642

1800

set foreign_key_checks=0;

1643

eval create $temp table t2 (a varchar(10), foreign key (a) references t1(a)) engine = $engine_type;

1644

eval create $temp table t1(a varchar(10) primary key) engine = $engine_type;

1801

eval create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = $engine_type;

1802

eval create table t1(a varchar(10) primary key) engine = $engine_type;

1645

1803

-- error 1025,1025

1646

1804

alter table t1 modify column a int;

1647

1805

set foreign_key_checks=1;

1650

1808

# test that charset conversions with ALTER are allowed when f_k_c is 0

1651

1809

1652

1810

set foreign_key_checks=0;

1653

eval create $temp table t2 (a varchar(10), foreign key (a) references t1(a)) engine = $engine_type DEFAULT CHARSET=latin1;

1654

eval create $temp table t1(a varchar(10) primary key) engine = $engine_type DEFAULT CHARSET=latin1;

1811

eval create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = $engine_type DEFAULT CHARSET=latin1;

1812

eval create table t1(a varchar(10) primary key) engine = $engine_type DEFAULT CHARSET=latin1;

1655

1813

alter table t1 convert to character set utf8;

1656

1814

set foreign_key_checks=1;

1657

1815

drop table t2,t1;

1659

1817

# test that RENAME does not allow invalid charsets when f_k_c is 0

1660

1818

1661

1819

set foreign_key_checks=0;

1662

eval create $temp table t2 (a varchar(10), foreign key (a) references t1(a)) engine = $engine_type DEFAULT CHARSET=latin1;

1663

eval create $temp table t3(a varchar(10) primary key) engine = $engine_type DEFAULT CHARSET=utf8;

1820

eval create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = $engine_type DEFAULT CHARSET=latin1;

1821

eval create table t3(a varchar(10) primary key) engine = $engine_type DEFAULT CHARSET=utf8;

1664

1822

--replace_result $MYSQLTEST_VARDIR . master-data/ ''

1665

1823

-- error 1025

1666

1824

rename table t3 to t1;

1669

1827

1670

1828

# test that foreign key errors are reported correctly (Bug #15550)

1671

1829

1672

eval create $temp table t1(a int primary key) row_format=redundant engine=$engine_type;

1673

eval create $temp table t2(a int primary key,constraint foreign key(a)references t1(a)) row_format=compact engine=$engine_type;

1674

eval create $temp table t3(a int primary key) row_format=compact engine=$engine_type;

1675

eval create $temp table t4(a int primary key,constraint foreign key(a)references t3(a)) row_format=redundant engine=$engine_type;

1830

eval create table t1(a int primary key) row_format=redundant engine=$engine_type;

1831

eval create table t2(a int primary key,constraint foreign key(a)references t1(a)) row_format=compact engine=$engine_type;

1832

eval create table t3(a int primary key) row_format=compact engine=$engine_type;

1833

eval create table t4(a int primary key,constraint foreign key(a)references t3(a)) row_format=redundant engine=$engine_type;

1676

1834

1677

1835

insert into t1 values(1);

1678

1836

insert into t3 values(1);

1713

1871

1714

1872

# Test that we can create a large (>1K) key

1715

1873

1716

eval create $temp table t1 (a varchar(255) character set utf8,

1874

eval create table t1 (a varchar(255) character set utf8,

1717

1875

b varchar(255) character set utf8,

1718

1876

c varchar(255) character set utf8,

1719

1877

d varchar(255) character set utf8,

1720

1878

key (a,b,c,d)) engine=$engine_type;

1721

1879

drop table t1;

1722

1880

--error ER_TOO_LONG_KEY

1723

eval create $temp table t1 (a varchar(255) character set utf8,

1881

eval create table t1 (a varchar(255) character set utf8,

1724

1882

b varchar(255) character set utf8,

1725

1883

c varchar(255) character set utf8,

1726

1884

d varchar(255) character set utf8,

1730

1888

1731

1889

# test the padding of BINARY types and collations (Bug #14189)

1732

1890

1733

eval create $temp table t1 (s1 varbinary(2),primary key (s1)) engine=$engine_type;

1734

eval create $temp table t2 (s1 binary(2),primary key (s1)) engine=$engine_type;

1735

eval create $temp table t3 (s1 varchar(2),primary key (s1)) engine=$engine_type;

1736

eval create $temp table t4 (s1 char(2),primary key (s1)) engine=$engine_type;

1891

eval create table t1 (s1 varbinary(2),primary key (s1)) engine=$engine_type;

1892

eval create table t2 (s1 binary(2),primary key (s1)) engine=$engine_type;

1893

eval create table t3 (s1 varchar(2) binary,primary key (s1)) engine=$engine_type;

1894

eval create table t4 (s1 char(2) binary,primary key (s1)) engine=$engine_type;

1737

1895

1738

1896

insert into t1 values (0x41),(0x4120),(0x4100);

1739

1897

-- error ER_DUP_ENTRY

1754

1912

1755

1913

if (test_foreign_keys)

1756

1914

{

1757

eval create $temp table t1 (a int primary key,s1 varbinary(3) not null unique) engine=$engine_type;

1758

eval create $temp table t2 (s1 binary(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=$engine_type;

1915

eval create table t1 (a int primary key,s1 varbinary(3) not null unique) engine=$engine_type;

1916

eval create table t2 (s1 binary(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=$engine_type;

1759

1917

1760

1918

insert into t1 values(1,0x4100),(2,0x41),(3,0x4120),(4,0x42);

1761

1919

-- error 1452

1788

1946

1789

1947

drop table t2,t1;

1790

1948

1791

eval create $temp table t1 (a int primary key,s1 varchar(2) not null unique) engine=$engine_type;

1792

eval create $temp table t2 (s1 char(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=$engine_type;

1949

eval create table t1 (a int primary key,s1 varchar(2) binary not null unique) engine=$engine_type;

1950

eval create table t2 (s1 char(2) binary not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=$engine_type;

1793

1951

1794

1952

insert into t1 values(1,0x4100),(2,0x41);

1795

1953

insert into t2 values(0x41);

1813

1971

# Ensure that <tablename>_ibfk_0 is not mistreated as a

1814

1972

# generated foreign key identifier. (Bug #16387)

1815

1973

1816

eval create $temp table t1(a INT, PRIMARY KEY(a)) ENGINE=$engine_type;

1817

eval create $temp table t2(a INT) ENGINE=$engine_type;

1974

eval CREATE TABLE t1(a INT, PRIMARY KEY(a)) ENGINE=$engine_type;

1975

eval CREATE TABLE t2(a INT) ENGINE=$engine_type;

1818

1976

ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1(a);

1819

1977

ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;

1820

1978

ALTER TABLE t2 ADD CONSTRAINT t2_ibfk_0 FOREIGN KEY (a) REFERENCES t1(a);

1821

1979

ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0;

1822

SHOW create $temp table t2;

1980

SHOW CREATE TABLE t2;

1823

1981

DROP TABLE t2,t1;

1824

1982

}

1825

1983

1984

1985

# Test case for bug #16229: MySQL/InnoDB uses full explicit table locks in trigger processing

1986

1987

1988

connect (a,localhost,root,,);

1989

connect (b,localhost,root,,);

1990

connection a;

1991

eval create table t1(a int not null, b int, c int, d int, primary key(a)) engine=$engine_type;

1992

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

1993

commit;

1994

connection b;

1995

set autocommit = 0;

1996

update t1 set b = 5 where a = 2;

1997

connection a;

1998

delimiter |;

1999

create trigger t1t before insert on t1 for each row begin set NEW.b = NEW.a * 10 + 5, NEW.c = NEW.a / 10; end |

2000

delimiter ;|

2001

set autocommit = 0;

2002

connection a;

2003

insert into t1(a) values (10),(20),(30),(40),(50),(60),(70),(80),(90),(100),

2004

(11),(21),(31),(41),(51),(61),(71),(81),(91),(101),

2005

(12),(22),(32),(42),(52),(62),(72),(82),(92),(102),

2006

(13),(23),(33),(43),(53),(63),(73),(83),(93),(103),

2007

(14),(24),(34),(44),(54),(64),(74),(84),(94),(104);

2008

connection b;

2009

commit;

2010

connection a;

2011

commit;

2012

drop trigger t1t;

2013

drop table t1;

2014

disconnect a;

2015

disconnect b;

2016

2017

# Another trigger test

2018

2019

connect (a,localhost,root,,);

2020

connect (b,localhost,root,,);

2021

connection a;

2022

eval create table t1(a int not null, b int, c int, d int, primary key(a)) engine=$engine_type;

2023

eval create table t2(a int not null, b int, c int, d int, primary key(a)) engine=$engine_type;

2024

eval create table t3(a int not null, b int, c int, d int, primary key(a)) engine=$engine_type;

2025

eval create table t4(a int not null, b int, c int, d int, primary key(a)) engine=$engine_type;

2026

eval create table t5(a int not null, b int, c int, d int, primary key(a)) engine=$engine_type;

2027

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

2028

insert into t2(a) values (1),(2),(3);

2029

insert into t3(a) values (1),(2),(3);

2030

insert into t4(a) values (1),(2),(3);

2031

insert into t3(a) values (5),(7),(8);

2032

insert into t4(a) values (5),(7),(8);

2033

insert into t5(a) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);

2034

2035

delimiter |;

2036

create trigger t1t before insert on t1 for each row begin

2037

INSERT INTO t2 SET a = NEW.a;

2038

end |

2039

2040

create trigger t2t before insert on t2 for each row begin

2041

DELETE FROM t3 WHERE a = NEW.a;

2042

end |

2043

2044

create trigger t3t before delete on t3 for each row begin

2045

UPDATE t4 SET b = b + 1 WHERE a = OLD.a;

2046

end |

2047

2048

create trigger t4t before update on t4 for each row begin

2049

UPDATE t5 SET b = b + 1 where a = NEW.a;

2050

end |

2051

delimiter ;|

2052

commit;

2053

set autocommit = 0;

2054

update t1 set b = b + 5 where a = 1;

2055

update t2 set b = b + 5 where a = 1;

2056

update t3 set b = b + 5 where a = 1;

2057

update t4 set b = b + 5 where a = 1;

2058

insert into t5(a) values(20);

2059

connection b;

2060

set autocommit = 0;

2061

insert into t1(a) values(7);

2062

insert into t2(a) values(8);

2063

delete from t2 where a = 3;

2064

update t4 set b = b + 1 where a = 3;

2065

commit;

2066

drop trigger t1t;

2067

drop trigger t2t;

2068

drop trigger t3t;

2069

drop trigger t4t;

2070

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

2071

connection default;

2072

disconnect a;

2073

disconnect b;

2074

1826

2075

if ($test_foreign_keys)

1827

2076

{

1828

2077

1830

2079

# error message (bug #9680)

1831

2080

1832

2081

1833

eval create $temp table t1 (

2082

eval CREATE TABLE t1 (

1834

2083

field1 varchar(8) NOT NULL DEFAULT '',

1835

2084

field2 varchar(8) NOT NULL DEFAULT '',

1836

2085

PRIMARY KEY (field1, field2)

1837

2086

) ENGINE=$engine_type;

1838

2087

1839

eval create $temp table t2 (

2088

eval CREATE TABLE t2 (

1840

2089

field1 varchar(8) NOT NULL DEFAULT '' PRIMARY KEY,

1841

2090

FOREIGN KEY (field1) REFERENCES t1 (field1)

1842

2091

ON DELETE CASCADE ON UPDATE CASCADE

1857

2106

1858

2107

# Bug#18477 - MySQL/InnoDB Ignoring Foreign Keys in ALTER TABLE

1859

2108

1860

eval create $temp table t1 (

2109

eval create table t1 (

1861

2110

c1 bigint not null,

1862

2111

c2 bigint not null,

1863

2112

primary key (c1),

1864

2113

unique key (c2)

1865

2114

) engine=$engine_type;

1866

2115

1867

eval create $temp table t2 (

2116

eval create table t2 (

1868

2117

c1 bigint not null,

1869

2118

primary key (c1)

1870

2119

) engine=$engine_type;

1884

2133

# Bug #14360: problem with intervals

1885

2134

1886

2135

1887

eval create $temp table t1(a date) engine=$engine_type;

1888

eval create $temp table t2(a date, key(a)) engine=$engine_type;

2136

eval create table t1(a date) engine=$engine_type;

2137

eval create table t2(a date, key(a)) engine=$engine_type;

1889

2138

insert into t1 values('2005-10-01');

1890

2139

insert into t2 values('2005-10-01');

1891

2140

select * from t1, t2

1892

2141

where t2.a between t1.a - interval 2 day and t1.a + interval 2 day;

1893

2142

drop table t1, t2;

1894

2143

1895

eval create $temp table t1 (id int not null, f_id int not null, f int not null,

2144

eval create table t1 (id int not null, f_id int not null, f int not null,

1896

2145

primary key(f_id, id)) engine=$engine_type;

1897

eval create $temp table t2 (id int not null,s_id int not null,s varchar(200),

2146

eval create table t2 (id int not null,s_id int not null,s varchar(200),

1898

2147

primary key(id)) engine=$engine_type;

1899

2148

INSERT INTO t1 VALUES (8, 1, 3);

1900

2149

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

1901

2150

INSERT INTO t2 VALUES (1, 0, '');

1902

2151

INSERT INTO t2 VALUES (8, 1, '');

1903

2152

commit;

2153

DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id)

2154

WHERE mm.id IS NULL;

1904

2155

select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)

1905

2156

where mm.id is null lock in share mode;

1906

2157

drop table t1,t2;

1909

2160

# Test case where X-locks on unused rows should be released in a

1910

2161

# update (because READ COMMITTED isolation level)

1911

2162

1912

if(!$using_temp)

1913

{

2163

1914

2164

connect (a,localhost,root,,);

1915

2165

connect (b,localhost,root,,);

1916

2166

connection a;

1917

eval create $temp table t1(a int not null, b int, primary key(a)) engine=$engine_type;

2167

eval create table t1(a int not null, b int, primary key(a)) engine=$engine_type;

1918

2168

insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);

1919

2169

commit;

1920

2170

set autocommit = 0;

1935

2185

connection default;

1936

2186

disconnect a;

1937

2187

disconnect b;

1938

}

2188

1939

2189

if ($test_transactions)

1940

2190

{

1941

2191

1946

2196

connect (a,localhost,root,,);

1947

2197

connect (b,localhost,root,,);

1948

2198

connection a;

1949

eval create $temp table t1(a int not null, b int, primary key(a)) engine=$engine_type;

2199

eval create table t1(a int not null, b int, primary key(a)) engine=$engine_type;

1950

2200

insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2);

1951

2201

commit;

1952

2202

set autocommit = 0;

1982

2232

connect (a,localhost,root,,);

1983

2233

connect (b,localhost,root,,);

1984

2234

connection a;

1985

eval create $temp table t1(a int not null, b int, primary key(a)) engine=$engine_type;

2235

eval create table t1(a int not null, b int, primary key(a)) engine=$engine_type;

1986

2236

insert into t1 values (1,2),(5,3),(4,2);

1987

eval create $temp table t2(d int not null, e int, primary key(d)) engine=$engine_type;

2237

eval create table t2(d int not null, e int, primary key(d)) engine=$engine_type;

1988

2238

insert into t2 values (8,6),(12,1),(3,1);

1989

2239

commit;

1990

2240

set autocommit = 0;

1994

2244

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

1995

2245

insert into t1 select * from t2;

1996

2246

update t1 set b = (select e from t2 where a = d);

1997

eval create $temp table t3(d int not null, e int, primary key(d)) engine=$engine_type

2247

eval create table t3(d int not null, e int, primary key(d)) engine=$engine_type

1998

2248

select * from t2;

1999

2249

commit;

2000

2250

connection a;

2038

2288

connect (j,localhost,root,,);

2039

2289

eval SET SESSION STORAGE_ENGINE = $engine_type;

2040

2290

connection a;

2041

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

2291

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

2042

2292

insert into t1 values (1,2),(5,3),(4,2);

2043

create $temp table t2(a int not null, b int, primary key(a));

2293

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

2044

2294

insert into t2 values (8,6),(12,1),(3,1);

2045

create $temp table t3(d int not null, b int, primary key(d));

2295

create table t3(d int not null, b int, primary key(d));

2046

2296

insert into t3 values (8,6),(12,1),(3,1);

2047

create $temp table t5(a int not null, b int, primary key(a));

2297

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

2048

2298

insert into t5 values (1,2),(5,3),(4,2);

2049

create $temp table t6(d int not null, e int, primary key(d));

2299

create table t6(d int not null, e int, primary key(d));

2050

2300

insert into t6 values (8,6),(12,1),(3,1);

2051

create $temp table t8(a int not null, b int, primary key(a));

2301

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

2052

2302

insert into t8 values (1,2),(5,3),(4,2);

2053

create $temp table t9(d int not null, e int, primary key(d));

2303

create table t9(d int not null, e int, primary key(d));

2054

2304

insert into t9 values (8,6),(12,1),(3,1);

2055

2305

commit;

2056

2306

set autocommit = 0;

2069

2319

set autocommit = 0;

2070

2320

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2071

2321

--send

2072

create $temp table t4(a int not null, b int, primary key(a)) select * from t2;

2322

create table t4(a int not null, b int, primary key(a)) select * from t2;

2073

2323

connection e;

2074

2324

set autocommit = 0;

2075

2325

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2084

2334

set autocommit = 0;

2085

2335

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2086

2336

--send

2087

create $temp table t7(a int not null, b int, primary key(a)) select * from t2 lock in share mode;

2337

create table t7(a int not null, b int, primary key(a)) select * from t2 lock in share mode;

2088

2338

connection h;

2089

2339

set autocommit = 0;

2090

2340

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2099

2349

set autocommit = 0;

2100

2350

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2101

2351

--send

2102

create $temp table t10(a int not null, b int, primary key(a)) select * from t2 for update;

2352

create table t10(a int not null, b int, primary key(a)) select * from t2 for update;

2103

2353

2104

2354

connection b;

2105

2355

--error 1205

2159

2409

{

2160

2410

# bug 18934, "InnoDB crashes when table uses column names like DB_ROW_ID"

2161

2411

--error 1005

2162

eval create $temp table t1 (DB_ROW_ID int) engine=$engine_type;

2412

eval CREATE TABLE t1 (DB_ROW_ID int) engine=$engine_type;

2163

2413

2164

2414

2165

2415

# Bug #17152: Wrong result with BINARY comparison on aliased column

2166

2416

2167

2417

2168

eval create $temp table t1 (

2418

eval CREATE TABLE t1 (

2169

2419

a BIGINT(20) NOT NULL,

2170

2420

PRIMARY KEY (a)

2171

2421

) ENGINE=$engine_type DEFAULT CHARSET=UTF8;

2172

2422

2173

eval create $temp table t2 (

2423

eval CREATE TABLE t2 (

2174

2424

a BIGINT(20) NOT NULL,

2175

2425

b VARCHAR(128) NOT NULL,

2176

2426

c TEXT NOT NULL,

2202

2452

# Bug #15680 (SPATIAL key in innodb)

2203

2453

2204

2454

--error ER_TABLE_CANT_HANDLE_SPKEYS

2205

eval create $temp table t1 (g geometry not null, spatial gk(g)) engine=$engine_type;

2455

eval create table t1 (g geometry not null, spatial gk(g)) engine=$engine_type;

2206

2456

}

2207

2457

2208

2458

2209

2459

# Test optimize on table with open transaction

2210

2460

2211

2461

2212

eval create $temp table t1 ( a int ) ENGINE=$engine_type;

2462

eval CREATE TABLE t1 ( a int ) ENGINE=$engine_type;

2213

2463

BEGIN;

2214

2464

INSERT INTO t1 VALUES (1);

2215

eval ALTER TABLE t1 ENGINE=$engine_type;

2465

OPTIMIZE TABLE t1;

2216

2466

DROP TABLE t1;

2217

2467

2218

2468

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

Older »