~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 not null auto_increment, code int 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 (

eval CREATE TABLE t1 (

id int NOT NULL auto_increment,

parent_id int DEFAULT '0' NOT NULL,

level int DEFAULT '0' NOT NULL,

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;

116

show keys from t1;

117

drop table t1;

118

119

120

# Test replace

121

122

123

eval create $temp table t1 (

123

eval CREATE TABLE t1 (

124

gesuchnr int DEFAULT '0' NOT NULL,

125

benutzer_id int DEFAULT '0' NOT NULL,

126

PRIMARY KEY (gesuchnr,benutzer_id)

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;

152

insert into t1 values (3,""), (4,"testing");

153

analyze table t1;

154

--replace_column 7 #

155

#show keys from t1;

155

show keys from t1;

156

drop table t1;

157

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 (

317

eval CREATE TABLE t1 (

318

user_id int DEFAULT '0' NOT NULL,

319

name varchar(100),

320

phone varchar(100),

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;

342

show index from t1;

343

drop table t1;

344

345

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;

353

update t1 set col2='7' where col1='4';

354

select * from t1;

355

alter table t1 add co3 int DEFAULT 42 not null;

355

alter table t1 add co3 int not null;

356

select * from t1;

357

update t1 set col2='9' where col1='2';

358

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 (

382

eval CREATE TABLE t1 (

383

id int NOT NULL auto_increment,

384

ggid varchar(32) DEFAULT '' NOT NULL,

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

436

# Testing of tables without primary keys

437

438

439

eval create $temp table t1 (

439

eval CREATE TABLE t1 (

440

id int NOT NULL auto_increment,

441

parent_id int DEFAULT '0' NOT NULL,

442

level int DEFAULT '0' NOT NULL,

467

468

# Test of index only reads

469

470

eval create $temp table t1 (

470

eval CREATE TABLE t1 (

471

sca_code char(6) NOT NULL,

472

cat_code char(6) NOT NULL,

473

sca_desc varchar(50),

498

499

# Test of opening table twice and timestamps

500

501

--echo $temp

502

if(!$using_temp)

503

{

504

501

set @a:=now();

505

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

502

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

506

503

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

507

504

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

508

505

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

509

506

update t1 set a=5 where a=1;

510

507

select a from t1;

511

508

drop table t1;

512

}

509

513

510

514

511

# Test with variable length primary key

515

512

516

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

513

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

517

514

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

518

515

select * from t1 order by b desc;

519

eval alter table t1 ENGINE=$engine_type;

516

optimize table t1;

520

517

--replace_column 7 #

521

#show keys from t1;

518

show keys from t1;

522

519

drop table t1;

523

520

524

521

525

522

# Test of create index with NULL columns

526

523

527

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

524

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

528

525

insert into t1 values (1,2);

529

526

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

530

527

create index ax1 on t1 (i,j);

535

532

# Test min-max optimization

536

533

537

534

538

eval create $temp table t1 (

535

eval CREATE TABLE t1 (

539

536

a int NOT NULL,

540

537

b int NOT NULL,

541

538

UNIQUE (a, b)

549

546

# Test INSERT DELAYED

550

547

551

548

552

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

549

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

553

550

# Can't test this in 3.23

554

551

# INSERT DELAYED INTO t1 VALUES (1);

555

552

INSERT INTO t1 VALUES (1);

561

558

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

562

559

563

560

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;

561

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

562

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

563

--replace_column 9 #

567

564

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

568

565

drop table t1;

569

566

570

567

568

# Test lock tables

569

570

571

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;

572

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

573

LOCK TABLES t1 WRITE;

574

--error ER_DUP_ENTRY

575

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

576

select id from t1;

577

select id from t1;

578

UNLOCK TABLES;

579

DROP TABLE t1;

580

581

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;

582

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

583

LOCK TABLES t1 WRITE;

584

begin;

585

--error ER_DUP_ENTRY

586

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

587

select id from t1;

588

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

589

commit;

590

select id,id3 from t1;

591

UNLOCK TABLES;

592

DROP TABLE t1;

593

594

571

595

# Test prefix key

572

596

573

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

597

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

574

598

drop table t1;

575

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

599

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

576

600

show create table t1;

577

601

drop table t1;

578

602

590

614

drop table t1;

591

615

592

616

#Slashdot bug

593

eval create $temp table t1

617

eval create table t1

594

618

(

595

619

id int auto_increment primary key,

596

620

name varchar(32) not null,

616

640

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

617

641

618

642

create database mysqltest;

619

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

643

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

620

644

insert into mysqltest.t1 values(1);

621

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

645

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

622

646

insert into mysqltest.t2 values(1);

623

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

647

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

624

648

insert into mysqltest.t3 values(1);

625

649

commit;

626

650

drop database mysqltest;

633

657

634

658

635

659

set autocommit=0;

636

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

660

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

637

661

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

638

662

truncate table t1;

639

663

commit;

647

671

drop table t1;

648

672

set autocommit=1;

649

673

650

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

674

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

651

675

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

652

676

truncate table t1;

653

677

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

662

686

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

663

687

664

688

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;

689

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

690

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

667

691

--replace_column 9 #

668

692

explain select * from t1 order by a;

686

710

# Check describe

687

711

688

712

689

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

690

#desc t1;

713

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

714

desc t1;

691

715

drop table t1;

692

716

693

717

718

# Test of multi-table-delete

719

720

721

eval CREATE TABLE t1 (

722

number bigint NOT NULL default '0',

723

cname char(15) NOT NULL default '',

724

carrier_id int NOT NULL default '0',

725

privacy int NOT NULL default '0',

726

last_mod_date timestamp NOT NULL,

727

last_mod_id int NOT NULL default '0',

728

last_app_date timestamp NULL,

729

last_app_id int default '-1',

730

version int NOT NULL default '0',

731

assigned_scps int default '0',

732

status int default '0'

733

) ENGINE=$engine_type;

734

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

735

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

736

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

737

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

738

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

739

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

740

eval CREATE TABLE t2 (

741

number bigint NOT NULL default '0',

742

cname char(15) NOT NULL default '',

743

carrier_id int NOT NULL default '0',

744

privacy int NOT NULL default '0',

745

last_mod_date timestamp NOT NULL,

746

last_mod_id int NOT NULL default '0',

747

last_app_date timestamp NULL,

748

last_app_id int default '-1',

749

version int NOT NULL default '0',

750

assigned_scps int default '0',

751

status int default '0'

752

) ENGINE=$engine_type;

753

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

754

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

755

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

756

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

757

select * from t1;

758

select * from t2;

759

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

760

select * from t1;

761

select * from t2;

762

select * from t2;

763

drop table t1,t2;

764

765

694

766

# A simple test with some isolation levels

695

767

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

696

768

# this works.

697

769

698

770

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;

771

eval create 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;

700

772

701

773

BEGIN;

702

774

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

719

791

DROP TABLE t1;

720

792

721

793

794

# Test of multi-table-update

795

796

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

797

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

798

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

799

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

800

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

801

select * from t1;

802

select * from t2;

803

drop table t1,t2;

804

805

722

806

# Testing of IFNULL

723

807

724

if(!$using_temp)

725

{

726

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

808

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

727

809

insert into t1 values(20,null);

728

810

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

729

811

t2.b=t3.a;

733

815

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

734

816

t2.b=t3.a order by 1;

735

817

drop table t1;

736

}

818

819

820

# Test of read_through not existing const_table

821

822

823

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

824

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

825

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

826

drop table t1,t2;

827

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

828

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

829

insert into t1 values (10, 20);

830

insert into t2 values (10, 20);

831

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

832

drop table t1,t2;

737

833

738

834

if ($test_foreign_keys)

739

835

{

741

837

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

742

838

743

839

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;

840

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

841

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

842

insert into t1 set id=1;

747

843

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

748

844

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

749

845

select * from t1;

750

846

select * from t2;

751

847

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;

848

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

849

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

754

850

INSERT INTO t1 VALUES(1);

755

851

INSERT INTO t2 VALUES(1, 1);

756

852

SELECT * from t1;

769

865

770

866

set autocommit=0;

771

867

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;

868

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

869

870

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

871

872

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

777

873

778

874

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

779

875

COMMIT;

800

896

# Check update with conflicting key

801

897

802

898

803

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

899

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

804

900

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

805

901

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

806

902

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

808

904

drop table t1;

809

905

810

906

907

# Test multi update with different join methods

908

909

910

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

911

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

912

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

913

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

914

915

# Full join, without key

916

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

917

select * from t1;

918

919

# unique key

920

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

921

select * from t1;

922

923

# ref key

924

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

925

select * from t1;

926

927

# Range key (in t1)

928

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;

929

select * from t1;

930

select * from t2;

931

932

drop table t1,t2;

933

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

934

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

935

SET AUTOCOMMIT=0;

936

INSERT INTO t1 ( B_ID ) VALUES ( 1 );

937

INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );

938

ROLLBACK;

939

SELECT * FROM t1;

940

drop table t1,t2;

941

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

942

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

943

select distinct parent,child from t1 order by parent;

944

drop table t1;

945

946

811

947

# Test that MySQL priorities clustered indexes

812

948

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;

949

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

950

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

815

951

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

816

952

insert into t2 (a) select b from t1;

817

953

insert into t1 (b) select b from t2;

843

979

# Test of UPDATE ... ORDER BY

844

980

845

981

846

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

982

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

847

983

848

984

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

849

985

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

850

986

SELECT * from t1;

851

987

drop table t1;

852

988

853

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

989

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

854

990

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

991

SET @tmp=0;

856

992

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

861

997

drop table t1;

862

998

863

999

1000

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

1001

1002

1003

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

1004

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

1005

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

1006

1007

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

1008

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

1009

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

1010

update t1 set a=c, b=c;

1011

1012

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;

1013

insert into t2 select * from t1;

1014

1015

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

1016

drop table t1,t2;

1017

1018

864

1019

# test autoincrement with TRUNCATE

865

1020

866

1021

867

1022

SET AUTOCOMMIT=1;

868

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

1023

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

869

1024

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

870

1025

truncate table t1;

871

1026

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

879

1034

# Test dictionary handling with spaceand quoting

880

1035

881

1036

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;

1037

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

1038

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

1039

#show create table t2;

885

1040

drop table t2,t1;

886

1041

888

1043

# Test of multi updated and foreign keys

889

1044

890

1045

891

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

1046

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

892

1047

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;

1048

eval create 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;

894

1049

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;

1050

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

896

1051

insert into `t3`values ( 1 ) ;

897

1052

--error 1451

898

1053

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

905

1060

906

1061

# test for recursion depth limit

907

1062

908

eval create $temp table t1(

1063

eval create table t1(

909

1064

id int primary key,

910

1065

pid int,

911

1066

index(pid),

925

1080

# Test timestamps

926

1081

927

1082

928

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

929

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

1083

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

1084

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

930

1085

(stamp))ENGINE=$engine_type;

931

1086

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

932

1087

# Note that timestamp 3 is wrong

940

1095

# Test by Francois MASUREL

941

1096

942

1097

943

if(!$using_temp)

944

{

945

eval create $temp table t1 (

1098

eval CREATE TABLE t1 (

946

1099

`id` int NOT NULL auto_increment,

947

1100

`id_object` int default '0',

948

1101

`id_version` int NOT NULL default '1',

955

1108

956

1109

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

1110

958

eval create $temp table t2 (

1111

eval CREATE TABLE t2 (

959

1112

`id` int NOT NULL auto_increment,

960

1113

`id_version` int NOT NULL default '1',

961

1114

PRIMARY KEY (`id`),

968

1121

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

969

1122

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

970

1123

drop table t1,t2;

971

}

972

1124

973

1125

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

1126

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

975

1127

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

1128

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

977

1129

978

1130

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

1131

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

980

1132

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

1133

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

982

1134

983

1135

# Live checksum feature probably available + enabled

984

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

1136

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

985

1137

# Live checksum feature probably available + disabled

986

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

1138

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

987

1139

988

1140

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

989

1141

insert t2 select * from t1;

991

1143

insert t4 select * from t1;

992

1144

insert t5 select * from t1;

993

1145

insert t6 select * from t1;

1146

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

1147

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

1148

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

994

1149

# #show table status;

995

1150

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

996

1151

998

1153

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

999

1154

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

1000

1155

1001

if(!$using_temp)

1002

{

1003

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

1156

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

1004

1157

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

1005

1158

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

1006

1159

drop table t1;

1007

}

1008

1009

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

1010

1011

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

1012

--error 1005

1160

1161

1162

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

1163

1164

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

1013

1165

create table t2 like t1;

1014

create table t2 like t1 engine=innodb;

1015

1166

show create table t2;

1016

1167

drop table t1,t2;

1017

1168

1021

1172

# Test of automaticly created foreign keys

1022

1173

1023

1174

1024

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

1025

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

1175

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

1176

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

1026

1177

show create table t1;

1027

1178

show create table t2;

1028

1179

create index id on t2 (id);

1035

1186

show create table t2;

1036

1187

drop table t2;

1037

1188

1038

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;

1189

eval create 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;

1039

1190

show create table t2;

1040

1191

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

1041

1192

show create table t2;

1042

1193

drop table t2;

1043

1194

1044

1195

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

1045

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;

1046

show create table t2;

1047

drop table t2;

1048

1049

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;

1050

show create table t2;

1051

drop table t2;

1052

1053

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;

1054

show create table t2;

1055

drop table t2;

1056

1057

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;

1058

show create table t2;

1059

drop table t2;

1060

1061

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;

1196

eval create 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;

1197

show create table t2;

1198

drop table t2;

1199

1200

eval create 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;

1201

show create table t2;

1202

drop table t2;

1203

1204

eval create 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;

1205

show create table t2;

1206

drop table t2;

1207

1208

eval create 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;

1209

show create table t2;

1210

drop table t2;

1211

1212

eval create 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;

1062

1213

show create table t2;

1063

1214

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

1064

1215

show create table t2;

1070

1221

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

1071

1222

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

1072

1223

--error 1005

1073

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;

1224

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

1074

1225

1075

1226

# bug#3749

1076

1227

1077

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;

1228

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;

1078

1229

show create table t2;

1079

1230

drop table t2;

1080

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;

1231

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;

1081

1232

show create table t2;

1082

1233

drop table t2, t1;

1083

1234

}

1093

1244

show status like "binlog_cache_use";

1094

1245

show status like "binlog_cache_disk_use";

1095

1246

1096

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

1247

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

1097

1248

1098

1249

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

1099

1250

# transaction binlog will be flushed to disk...

1123

1274

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

1124

1275

1125

1276

--error 1060

1126

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

1127

--error 1060

1128

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

1129

--error 1060

1130

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

1131

--error 1060

1132

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

1133

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

1277

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

1278

--error 1060

1279

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

1280

--error 1060

1281

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

1282

--error 1060

1283

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

1284

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

1134

1285

--error 1060

1135

1286

alter table t1 add key (c1,c1);

1136

1287

--error 1060

1145

1296

# Bug #4082: integer truncation

1146

1297

1147

1298

1148

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

1299

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

1149

1300

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

1150

1301

select distinct concat(a, b) from t1;

1151

1302

drop table t1;

1157

1308

# engines does not fail

1158

1309

1159

1310

1160

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

1311

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

1161

1312

--error 1214

1162

1313

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

1163

1314

DROP TABLE t1;

1170

1321

1171

1322

1172

1323

--disable_warnings

1173

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

1324

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

1174

1325

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

1175

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

1326

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

1176

1327

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;

1177

1328

--enable_warnings

1178

1329

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

1192

1343

select * from t1;

1193

1344

drop table t1;

1194

1345

# Show that it works with permanent tables too.

1195

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

1346

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

1196

1347

insert into t1 values (4711);

1197

1348

truncate t1;

1198

1349

insert into t1 values (42);

1203

1354

# Bug #13025 Server crash during filesort

1204

1355

1205

1356

1206

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;

1357

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;

1207

1358

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

1208

1359

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

1209

1360

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

1213

1364

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

1214

1365

1215

1366

1216

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

1367

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

1217

1368

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

1218

1369

select min(a) from t1;

1219

1370

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

1225

1376

# range optimizer problem

1226

1377

1227

1378

1228

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

1379

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

1229

1380

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

1230

1381

--error 1264

1231

1382

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

1318

1469

# @TODO The below fails because it assumes latin1

1319

1470

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

1320

1471

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

1321

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

1472

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

1322

1473

#drop table t1;

1323

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

1474

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

1324

1475

#show create table t1;

1325

1476

#drop table t1;

1326

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

1477

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

1327

1478

#show create table t1;

1328

1479

#drop table t1;

1329

1480

1330

1481

#eval set storage_engine=$default;

1331

1482

1332

1483

# InnoDB specific varchar tests

1333

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

1484

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

1334

1485

#drop table t1;

1335

1486

1336

1487

1337

1488

# BUG#11039 Wrong key length in min()

1338

1489

1339

1490

1340

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

1491

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

1341

1492

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

1342

1493

select min(a) from t1;

1343

1494

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

1347

1498

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

1348

1499

1349

1500

1350

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;

1501

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

1351

1502

insert into t1 (b) values (1);

1352

1503

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

1353

1504

select * from t1;

1359

1510

select * from t1;

1360

1511

drop table t1;

1361

1512

1362

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

1513

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

1363

1514

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

1364

1515

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

1365

1516

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

1374

1525

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

1375

1526

1376

1527

1377

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

1528

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

1378

1529

insert into t1 (val) values (1);

1379

1530

update t1 set a=2 where a=1;

1380

1531

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

1390

1541

1391

1542

1392

1543

--disable_warnings

1393

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

1544

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

1394

1545

--enable_warnings

1395

1546

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

1396

1547

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

1397

1548

SELECT GRADE FROM t1 WHERE GRADE= 151;

1398

1549

DROP TABLE t1;

1399

1550

1551

1552

# Bug #12340 multitable delete deletes only one record

1553

1554

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

1555

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

1556

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

1557

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

1558

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

1559

select * from t1;

1560

drop table t1,t2;

1561

1400

1562

if ($test_foreign_keys)

1401

1563

{

1402

1564

1404

1566

# (bug #11946)

1405

1567

1406

1568

1407

eval create $temp table t1 (

1569

eval CREATE TABLE t1 (

1408

1570

id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)

1409

1571

) ENGINE=$engine_type;

1410

1572

1411

eval create $temp table t2 (

1573

eval CREATE TABLE t2 (

1412

1574

id INTEGER NOT NULL,

1413

1575

FOREIGN KEY (id) REFERENCES t1 (id)

1414

1576

) ENGINE=$engine_type;

1428

1590

DROP TABLE t2, t1;

1429

1591

1430

1592

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

1431

eval create $temp table t1

1593

eval CREATE TABLE t1

1432

1594

(

1433

1595

id INT PRIMARY KEY

1434

1596

) ENGINE=$engine_type;

1455

1617

1456

1618

1457

1619

# prefix index

1458

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

1620

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

1459

1621

character set = latin1 engine = $engine_type;

1460

1622

1461

1623

# normal indexes

1462

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

1463

character set = latin1 engine = $engine_type;

1464

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

1465

character set = latin1 engine = $engine_type;

1466

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

1467

character set = latin1 engine = $engine_type;

1468

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

1469

character set = latin1 engine = $engine_type;

1470

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

1471

character set = latin1 engine = $engine_type;

1472

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

1473

character set = latin1 engine = $engine_type;

1474

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

1624

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

1625

character set = latin1 engine = $engine_type;

1626

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

1627

character set = latin1 engine = $engine_type;

1628

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

1629

character set = latin1 engine = $engine_type;

1630

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

1631

character set = latin1 engine = $engine_type;

1632

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

1633

character set = latin1 engine = $engine_type;

1634

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

1635

character set = latin1 engine = $engine_type;

1636

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

1475

1637

character set = latin1 engine = $engine_type;

1476

1638

1477

1639

1478

1640

# multi-column indexes are allowed to be longer

1479

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

1641

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

1480

1642

character set = latin1 engine = $engine_type;

1481

1643

1482

1644

show create table t9;

1484

1646

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

1485

1647

1486

1648

# these should have their index length trimmed

1487

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

1488

character set = latin1 engine = $engine_type;

1489

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

1490

character set = latin1 engine = $engine_type;

1491

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

1492

character set = latin1 engine = $engine_type;

1493

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

1649

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

1650

character set = latin1 engine = $engine_type;

1651

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

1652

character set = latin1 engine = $engine_type;

1653

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

1654

character set = latin1 engine = $engine_type;

1655

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

1494

1656

character set = latin1 engine = $engine_type;

1495

1657

1496

1658

show create table t1;

1508

1670

1509

1671

# these should be refused

1510

1672

--error 1071

1511

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

1512

character set = latin1 engine = $engine_type;

1513

--error 1071

1514

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

1515

character set = latin1 engine = $engine_type;

1516

--error 1071

1517

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

1518

character set = latin1 engine = $engine_type;

1519

--error 1071

1520

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

1673

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

1674

character set = latin1 engine = $engine_type;

1675

--error 1071

1676

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

1677

character set = latin1 engine = $engine_type;

1678

--error 1071

1679

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

1680

character set = latin1 engine = $engine_type;

1681

--error 1071

1682

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

1521

1683

character set = latin1 engine = $engine_type;

1522

1684

1523

1685

}

1528

1690

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

1529

1691

1530

1692

1531

eval create $temp table t1

1693

eval CREATE TABLE t1

1532

1694

(

1533

1695

id INT PRIMARY KEY

1534

1696

) ENGINE=$engine_type;

1535

1697

1536

eval create $temp table t2

1698

eval CREATE TABLE t2

1537

1699

(

1538

1700

v INT,

1539

1701

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

1562

1724

}

1563

1725

# End of FOREIGN tests

1564

1726

1727

if ($test_transactions)

1728

{

1729

1730

# Test that checksum table uses a consistent read Bug #12669

1731

1732

connect (a,localhost,root,,);

1733

connect (b,localhost,root,,);

1734

connection a;

1735

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

1736

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

1737

set autocommit=0;

1738

checksum table t1;

1739

connection b;

1740

insert into t1 values(3);

1741

connection a;

1742

1743

# Here checksum should not see insert

1744

1745

checksum table t1;

1746

connection a;

1747

commit;

1748

checksum table t1;

1749

commit;

1750

drop table t1;

1751

1752

# autocommit = 1

1753

1754

connection a;

1755

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

1756

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

1757

set autocommit=1;

1758

checksum table t1;

1759

connection b;

1760

set autocommit=1;

1761

insert into t1 values(3);

1762

connection a;

1763

1764

# Here checksum sees insert

1765

1766

checksum table t1;

1767

drop table t1;

1768

1769

connection default;

1770

disconnect a;

1771

disconnect b;

1772

}

1773

1565

1774

# tests for bugs #9802 and #13778

1566

1775

1567

1776

if ($test_foreign_keys)

1569

1778

# test that FKs between invalid types are not accepted

1570

1779

1571

1780

set foreign_key_checks=0;

1572

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

1781

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

1573

1782

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

1574

1783

-- error 1005

1575

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

1784

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

1576

1785

set foreign_key_checks=1;

1577

1786

drop table t2;

1578

1787

1580

1789

# when f_k_c is 0

1581

1790

1582

1791

set foreign_key_checks=0;

1583

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

1792

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

1584

1793

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

1585

1794

-- error 1005

1586

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

1795

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

1587

1796

set foreign_key_checks=1;

1588

1797

drop table t1;

1589

1798

1590

1799

# test that invalid datatype conversions with ALTER are not allowed

1591

1800

1592

1801

set foreign_key_checks=0;

1593

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

1594

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

1802

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

1803

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

1595

1804

-- error 1025,1025

1596

1805

alter table t1 modify column a int;

1597

1806

set foreign_key_checks=1;

1600

1809

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

1601

1810

1602

1811

set foreign_key_checks=0;

1603

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

1604

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

1812

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

1813

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

1605

1814

alter table t1 convert to character set utf8;

1606

1815

set foreign_key_checks=1;

1607

1816

drop table t2,t1;

1609

1818

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

1610

1819

1611

1820

set foreign_key_checks=0;

1612

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

1613

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

1821

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

1822

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

1614

1823

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

1615

1824

-- error 1025

1616

1825

rename table t3 to t1;

1619

1828

1620

1829

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

1621

1830

1622

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

1623

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

1624

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

1625

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

1831

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

1832

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

1833

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

1834

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

1626

1835

1627

1836

insert into t1 values(1);

1628

1837

insert into t3 values(1);

1663

1872

1664

1873

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

1665

1874

1666

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

1875

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

1667

1876

b varchar(255) character set utf8,

1668

1877

c varchar(255) character set utf8,

1669

1878

d varchar(255) character set utf8,

1670

1879

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

1671

1880

drop table t1;

1672

1881

--error ER_TOO_LONG_KEY

1673

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

1882

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

1674

1883

b varchar(255) character set utf8,

1675

1884

c varchar(255) character set utf8,

1676

1885

d varchar(255) character set utf8,

1680

1889

1681

1890

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

1682

1891

1683

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

1684

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

1685

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

1686

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

1892

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

1893

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

1894

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

1895

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

1687

1896

1688

1897

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

1689

1898

-- error ER_DUP_ENTRY

1704

1913

1705

1914

if (test_foreign_keys)

1706

1915

{

1707

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

1708

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

1916

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

1917

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

1709

1918

1710

1919

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

1711

1920

-- error 1452

1738

1947

1739

1948

drop table t2,t1;

1740

1949

1741

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

1742

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

1950

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

1951

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

1743

1952

1744

1953

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

1745

1954

insert into t2 values(0x41);

1763

1972

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

1764

1973

# generated foreign key identifier. (Bug #16387)

1765

1974

1766

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

1767

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

1975

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

1976

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

1768

1977

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

1769

1978

ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;

1770

1979

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

1771

1980

ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0;

1772

SHOW create $temp table t2;

1981

SHOW CREATE TABLE t2;

1773

1982

DROP TABLE t2,t1;

1774

1983

}

1775

1984

1780

1989

# error message (bug #9680)

1781

1990

1782

1991

1783

eval create $temp table t1 (

1992

eval CREATE TABLE t1 (

1784

1993

field1 varchar(8) NOT NULL DEFAULT '',

1785

1994

field2 varchar(8) NOT NULL DEFAULT '',

1786

1995

PRIMARY KEY (field1, field2)

1787

1996

) ENGINE=$engine_type;

1788

1997

1789

eval create $temp table t2 (

1998

eval CREATE TABLE t2 (

1790

1999

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

1791

2000

FOREIGN KEY (field1) REFERENCES t1 (field1)

1792

2001

ON DELETE CASCADE ON UPDATE CASCADE

1807

2016

1808

2017

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

1809

2018

1810

eval create $temp table t1 (

2019

eval create table t1 (

1811

2020

c1 bigint not null,

1812

2021

c2 bigint not null,

1813

2022

primary key (c1),

1814

2023

unique key (c2)

1815

2024

) engine=$engine_type;

1816

2025

1817

eval create $temp table t2 (

2026

eval create table t2 (

1818

2027

c1 bigint not null,

1819

2028

primary key (c1)

1820

2029

) engine=$engine_type;

1834

2043

# Bug #14360: problem with intervals

1835

2044

1836

2045

1837

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

1838

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

2046

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

2047

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

1839

2048

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

1840

2049

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

1841

2050

select * from t1, t2

1842

2051

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

1843

2052

drop table t1, t2;

1844

2053

1845

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

2054

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

1846

2055

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

1847

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

2056

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

1848

2057

primary key(id)) engine=$engine_type;

1849

2058

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

1850

2059

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

1851

2060

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

1852

2061

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

1853

2062

commit;

2063

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

2064

WHERE mm.id IS NULL;

1854

2065

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

1855

2066

where mm.id is null lock in share mode;

1856

2067

drop table t1,t2;

1859

2070

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

1860

2071

# update (because READ COMMITTED isolation level)

1861

2072

1862

if(!$using_temp)

1863

{

2073

1864

2074

connect (a,localhost,root,,);

1865

2075

connect (b,localhost,root,,);

1866

2076

connection a;

1867

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

2077

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

1868

2078

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

1869

2079

commit;

1870

2080

set autocommit = 0;

1885

2095

connection default;

1886

2096

disconnect a;

1887

2097

disconnect b;

1888

}

2098

1889

2099

if ($test_transactions)

1890

2100

{

1891

2101

1896

2106

connect (a,localhost,root,,);

1897

2107

connect (b,localhost,root,,);

1898

2108

connection a;

1899

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

2109

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

1900

2110

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

1901

2111

commit;

1902

2112

set autocommit = 0;

1932

2142

connect (a,localhost,root,,);

1933

2143

connect (b,localhost,root,,);

1934

2144

connection a;

1935

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

2145

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

1936

2146

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

1937

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

2147

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

1938

2148

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

1939

2149

commit;

1940

2150

set autocommit = 0;

1944

2154

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

1945

2155

insert into t1 select * from t2;

1946

2156

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

1947

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

2157

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

1948

2158

select * from t2;

1949

2159

commit;

1950

2160

connection a;

1988

2198

connect (j,localhost,root,,);

1989

2199

eval SET SESSION STORAGE_ENGINE = $engine_type;

1990

2200

connection a;

1991

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

2201

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

1992

2202

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

1993

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

2203

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

1994

2204

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

1995

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

2205

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

1996

2206

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

1997

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

2207

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

1998

2208

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

1999

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

2209

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

2000

2210

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

2001

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

2211

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

2002

2212

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

2003

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

2213

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

2004

2214

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

2005

2215

commit;

2006

2216

set autocommit = 0;

2019

2229

set autocommit = 0;

2020

2230

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2021

2231

--send

2022

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

2232

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

2023

2233

connection e;

2024

2234

set autocommit = 0;

2025

2235

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2034

2244

set autocommit = 0;

2035

2245

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2036

2246

--send

2037

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

2247

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

2038

2248

connection h;

2039

2249

set autocommit = 0;

2040

2250

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2049

2259

set autocommit = 0;

2050

2260

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2051

2261

--send

2052

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

2262

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

2053

2263

2054

2264

connection b;

2055

2265

--error 1205

2109

2319

{

2110

2320

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

2111

2321

--error 1005

2112

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

2322

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

2113

2323

2114

2324

2115

2325

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

2116

2326

2117

2327

2118

eval create $temp table t1 (

2328

eval CREATE TABLE t1 (

2119

2329

a BIGINT(20) NOT NULL,

2120

2330

PRIMARY KEY (a)

2121

2331

) ENGINE=$engine_type DEFAULT CHARSET=UTF8;

2122

2332

2123

eval create $temp table t2 (

2333

eval CREATE TABLE t2 (

2124

2334

a BIGINT(20) NOT NULL,

2125

2335

b VARCHAR(128) NOT NULL,

2126

2336

c TEXT NOT NULL,

2152

2362

# Bug #15680 (SPATIAL key in innodb)

2153

2363

2154

2364

--error ER_TABLE_CANT_HANDLE_SPKEYS

2155

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

2365

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

2156

2366

}

2157

2367

2158

2368

2159

2369

# Test optimize on table with open transaction

2160

2370

2161

2371

2162

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

2372

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

2163

2373

BEGIN;

2164

2374

INSERT INTO t1 VALUES (1);

2165

eval ALTER TABLE t1 ENGINE=$engine_type;

2375

OPTIMIZE TABLE t1;

2166

2376

DROP TABLE t1;

2167

2377

2168

2378

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

Older »