~drizzle-trunk/drizzle/development

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;

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;

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 TABLE t1 (

eval create $temp table t1 (

id int NOT NULL auto_increment,

parent_id int DEFAULT '0' NOT NULL,

level int DEFAULT '0' NOT NULL,

120

# Test replace

121

122

123

eval CREATE TABLE t1 (

123

eval create $temp 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 table t1 (a int) engine=$engine_type;

139

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

140

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

141

optimize table t1;

142

delete from t1 where a = 1;

144

check table t1;

145

drop table t1;

146

147

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

147

eval create $temp 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 table t1 (a int,b varchar(20),key(a)) engine=$engine_type;

161

eval create $temp 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 table t1 (n int not null primary key) engine=$engine_type;

172

eval create $temp 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 table t1 (n int not null primary key) engine=$engine_type;

225

eval create $temp 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 table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) engine=$engine_type;

242

eval create $temp 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 TABLE t1 (id char(8) not null primary key, val int not null) engine=$engine_type;

260

eval create $temp 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 table t1 (a integer) engine=$engine_type;

282

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

283

start transaction;

284

rename table t1 to t2;

285

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

285

eval create $temp 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 TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) ENGINE=$engine_type;

297

eval create $temp 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 TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) ENGINE=$engine_type;

302

eval create $temp 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 TABLE t1 (

317

eval create $temp 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 TABLE t1 (a int not null, b int not null,c int not null,

339

eval create $temp 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 table t1 (col1 int not null, col2 char(4) not null, primary key(col1)) ENGINE = $other_engine_type;

349

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

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;

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 TABLE t1 (

382

eval create $temp table t1 (

383

id int NOT NULL auto_increment,

384

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

385

email varchar(64) DEFAULT '' NOT NULL,

412

# ORDER BY on not primary key

413

414

415

eval CREATE TABLE t1 (

415

eval create $temp 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 TABLE t1 (

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,

467

468

# Test of index only reads

469

470

eval CREATE TABLE t1 (

470

eval create $temp 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

{

501

504

set @a:=now();

502

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

505

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

503

506

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

504

507

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

505

508

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

506

509

update t1 set a=5 where a=1;

507

510

select a from t1;

508

511

drop table t1;

509

512

}

510

513

511

514

# Test with variable length primary key

512

515

513

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

516

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

514

517

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

515

518

select * from t1 order by b desc;

516

519

optimize table t1;

521

524

522

525

# Test of create index with NULL columns

523

526

524

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

527

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

525

528

insert into t1 values (1,2);

526

529

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

527

530

create index ax1 on t1 (i,j);

532

535

# Test min-max optimization

533

536

534

537

535

eval CREATE TABLE t1 (

538

eval create $temp table t1 (

536

539

a int NOT NULL,

537

540

b int NOT NULL,

538

541

UNIQUE (a, b)

546

549

# Test INSERT DELAYED

547

550

548

551

549

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

552

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

550

553

# Can't test this in 3.23

551

554

# INSERT DELAYED INTO t1 VALUES (1);

552

555

INSERT INTO t1 VALUES (1);

558

561

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

559

562

560

563

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;

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

565

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

563

566

--replace_column 9 #

564

567

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

567

570

568

571

# Test prefix key

569

572

570

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

573

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

571

574

drop table t1;

572

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

575

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

573

576

show create table t1;

574

577

drop table t1;

575

578

587

590

drop table t1;

588

591

589

592

#Slashdot bug

590

eval create table t1

593

eval create $temp table t1

591

594

(

592

595

id int auto_increment primary key,

593

596

name varchar(32) not null,

613

616

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

614

617

615

618

create database mysqltest;

616

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

619

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

617

620

insert into mysqltest.t1 values(1);

618

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

621

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

619

622

insert into mysqltest.t2 values(1);

620

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

623

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

621

624

insert into mysqltest.t3 values(1);

622

625

commit;

623

626

drop database mysqltest;

630

633

631

634

632

635

set autocommit=0;

633

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

636

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

634

637

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

635

638

truncate table t1;

636

639

commit;

644

647

drop table t1;

645

648

set autocommit=1;

646

649

647

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

650

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

648

651

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

649

652

truncate table t1;

650

653

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

659

662

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

660

663

661

664

662

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

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;

663

666

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

664

667

--replace_column 9 #

665

668

explain select * from t1 order by a;

683

686

# Check describe

684

687

685

688

686

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

689

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

687

690

desc t1;

688

691

drop table t1;

689

692

691

694

# Test of multi-table-delete

692

695

693

696

694

eval CREATE TABLE t1 (

697

eval create $temp table t1 (

695

698

number bigint NOT NULL default '0',

696

699

cname char(15) NOT NULL default '',

697

700

carrier_id int NOT NULL default '0',

710

713

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

711

714

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

712

715

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

713

eval CREATE TABLE t2 (

716

eval create $temp table t2 (

714

717

number bigint NOT NULL default '0',

715

718

cname char(15) NOT NULL default '',

716

719

carrier_id int NOT NULL default '0',

741

744

# this works.

742

745

743

746

744

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;

747

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;

745

748

746

749

BEGIN;

747

750

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

766

769

767

770

# Test of multi-table-update

768

771

769

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

770

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

772

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

773

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

771

774

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

772

775

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

773

776

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

778

781

779

782

# Testing of IFNULL

780

783

781

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

784

if(!$using_temp)

785

{

786

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

782

787

insert into t1 values(20,null);

783

788

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

784

789

t2.b=t3.a;

788

793

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

789

794

t2.b=t3.a order by 1;

790

795

drop table t1;

791

796

}

792

797

793

798

# Test of read_through not existing const_table

794

799

795

800

796

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

797

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

801

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

802

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

798

803

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

799

804

drop table t1,t2;

800

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

801

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

805

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

806

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

802

807

insert into t1 values (10, 20);

803

808

insert into t2 values (10, 20);

804

809

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

810

815

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

811

816

812

817

813

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

814

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;

818

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

819

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;

815

820

insert into t1 set id=1;

816

821

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

817

822

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

818

823

select * from t1;

819

824

select * from t2;

820

825

drop table t2,t1;

821

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

822

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

826

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

827

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

823

828

INSERT INTO t1 VALUES(1);

824

829

INSERT INTO t2 VALUES(1, 1);

825

830

SELECT * from t1;

838

843

839

844

set autocommit=0;

840

845

841

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

842

843

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

844

845

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

846

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

847

848

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

849

850

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

846

851

847

852

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

848

853

COMMIT;

869

874

# Check update with conflicting key

870

875

871

876

872

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

877

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

873

878

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

874

879

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

875

880

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

880

885

# Test multi update with different join methods

881

886

882

887

883

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

884

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

888

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

889

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

885

890

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

886

891

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

887

892

903

908

select * from t2;

904

909

905

910

drop table t1,t2;

906

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

907

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

911

eval create $temp table t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=$other_non_trans_engine_type;

912

eval create $temp table t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=$engine_type;

908

913

SET AUTOCOMMIT=0;

909

914

INSERT INTO t1 ( B_ID ) VALUES ( 1 );

910

915

INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );

911

916

ROLLBACK;

912

917

SELECT * FROM t1;

913

918

drop table t1,t2;

914

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

919

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

915

920

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

916

921

select distinct parent,child from t1 order by parent;

917

922

drop table t1;

919

924

920

925

# Test that MySQL priorities clustered indexes

921

926

922

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

923

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

927

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

928

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

924

929

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

925

930

insert into t2 (a) select b from t1;

926

931

insert into t1 (b) select b from t2;

952

957

# Test of UPDATE ... ORDER BY

953

958

954

959

955

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

960

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

956

961

957

962

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

958

963

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

959

964

SELECT * from t1;

960

965

drop table t1;

961

966

962

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

967

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

963

968

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

964

969

SET @tmp=0;

965

970

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

973

978

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

974

979

975

980

976

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

981

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

977

982

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

978

983

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

979

984

982

987

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

983

988

update t1 set a=c, b=c;

984

989

985

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;

990

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

986

991

insert into t2 select * from t1;

987

992

988

993

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

993

998

994

999

995

1000

SET AUTOCOMMIT=1;

996

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

1001

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

997

1002

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

998

1003

truncate table t1;

999

1004

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

1007

1012

# Test dictionary handling with spaceand quoting

1008

1013

1009

1014

1010

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

1011

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;

1015

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

1016

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;

1012

1017

#show create table t2;

1013

1018

drop table t2,t1;

1014

1019

1016

1021

# Test of multi updated and foreign keys

1017

1022

1018

1023

1019

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

1024

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

1020

1025

insert into `t1`values ( 1 ) ;

1021

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;

1026

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;

1022

1027

insert into `t2`values ( 1 ) ;

1023

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;

1028

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;

1024

1029

insert into `t3`values ( 1 ) ;

1025

1030

--error 1451

1026

1031

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

1033

1038

1034

1039

# test for recursion depth limit

1035

1040

1036

eval create table t1(

1041

eval create $temp table t1(

1037

1042

id int primary key,

1038

1043

pid int,

1039

1044

index(pid),

1053

1058

# Test timestamps

1054

1059

1055

1060

1056

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

1057

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

1061

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

1062

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

1058

1063

(stamp))ENGINE=$engine_type;

1059

1064

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

1060

1065

# Note that timestamp 3 is wrong

1068

1073

# Test by Francois MASUREL

1069

1074

1070

1075

1071

eval CREATE TABLE t1 (

1076

if(!$using_temp)

1077

{

1078

eval create $temp table t1 (

1072

1079

`id` int NOT NULL auto_increment,

1073

1080

`id_object` int default '0',

1074

1081

`id_version` int NOT NULL default '1',

1081

1088

1082

1089

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

1083

1090

1084

eval CREATE TABLE t2 (

1091

eval create $temp table t2 (

1085

1092

`id` int NOT NULL auto_increment,

1086

1093

`id_version` int NOT NULL default '1',

1087

1094

PRIMARY KEY (`id`),

1094

1101

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

1095

1102

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

1096

1103

drop table t1,t2;

1104

}

1097

1105

1098

1106

# Live checksum feature available + enabled

1099

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

1107

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

1100

1108

# Live checksum feature available + disabled

1101

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

1109

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

1102

1110

1103

1111

# Live checksum feature not available + enabled

1104

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

1112

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

1105

1113

# Live checksum feature not available + disabled

1106

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

1114

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

1107

1115

1108

1116

# Live checksum feature probably available + enabled

1109

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

1117

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

1110

1118

# Live checksum feature probably available + disabled

1111

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

1119

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

1112

1120

1113

1121

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

1114

1122

insert t2 select * from t1;

1126

1134

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

1127

1135

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

1128

1136

1129

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

1137

if(!$using_temp)

1138

{

1139

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

1130

1140

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

1131

1141

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

1132

1142

drop table t1;

1133

1134

1135

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

1136

1137

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

1143

}

1144

1145

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

1146

1147

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

1138

1148

create table t2 like t1;

1139

1149

show create table t2;

1140

1150

drop table t1,t2;

1145

1155

# Test of automaticly created foreign keys

1146

1156

1147

1157

1148

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

1149

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

1158

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

1159

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

1150

1160

show create table t1;

1151

1161

show create table t2;

1152

1162

create index id on t2 (id);

1159

1169

show create table t2;

1160

1170

drop table t2;

1161

1171

1162

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;

1172

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;

1163

1173

show create table t2;

1164

1174

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

1165

1175

show create table t2;

1166

1176

drop table t2;

1167

1177

1168

1178

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

1169

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;

1170

show create table t2;

1171

drop table t2;

1172

1173

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;

1174

show create table t2;

1175

drop table t2;

1176

1177

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;

1178

show create table t2;

1179

drop table t2;

1180

1181

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;

1182

show create table t2;

1183

drop table t2;

1184

1185

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;

1179

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;

1180

show create table t2;

1181

drop table t2;

1182

1183

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;

1184

show create table t2;

1185

drop table t2;

1186

1187

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;

1188

show create table t2;

1189

drop table t2;

1190

1191

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;

1192

show create table t2;

1193

drop table t2;

1194

1195

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;

1186

1196

show create table t2;

1187

1197

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

1188

1198

show create table t2;

1194

1204

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

1195

1205

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

1196

1206

--error 1005

1197

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;

1207

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;

1198

1208

1199

1209

# bug#3749

1200

1210

1201

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;

1211

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;

1202

1212

show create table t2;

1203

1213

drop table t2;

1204

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;

1214

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;

1205

1215

show create table t2;

1206

1216

drop table t2, t1;

1207

1217

}

1217

1227

show status like "binlog_cache_use";

1218

1228

show status like "binlog_cache_disk_use";

1219

1229

1220

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

1230

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

1221

1231

1222

1232

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

1223

1233

# transaction binlog will be flushed to disk...

1247

1257

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

1248

1258

1249

1259

--error 1060

1250

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

1251

--error 1060

1252

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

1253

--error 1060

1254

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

1255

--error 1060

1256

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

1257

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

1260

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

1261

--error 1060

1262

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

1263

--error 1060

1264

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

1265

--error 1060

1266

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

1267

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

1258

1268

--error 1060

1259

1269

alter table t1 add key (c1,c1);

1260

1270

--error 1060

1269

1279

# Bug #4082: integer truncation

1270

1280

1271

1281

1272

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

1282

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

1273

1283

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

1274

1284

select distinct concat(a, b) from t1;

1275

1285

drop table t1;

1281

1291

# engines does not fail

1282

1292

1283

1293

1284

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

1294

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

1285

1295

--error 1214

1286

1296

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

1287

1297

DROP TABLE t1;

1294

1304

1295

1305

1296

1306

--disable_warnings

1297

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

1307

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

1298

1308

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

1299

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

1309

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

1300

1310

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;

1301

1311

--enable_warnings

1302

1312

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

1316

1326

select * from t1;

1317

1327

drop table t1;

1318

1328

# Show that it works with permanent tables too.

1319

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

1329

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

1320

1330

insert into t1 values (4711);

1321

1331

truncate t1;

1322

1332

insert into t1 values (42);

1327

1337

# Bug #13025 Server crash during filesort

1328

1338

1329

1339

1330

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;

1340

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;

1331

1341

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

1332

1342

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

1333

1343

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

1337

1347

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

1338

1348

1339

1349

1340

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

1350

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

1341

1351

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

1342

1352

select min(a) from t1;

1343

1353

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

1349

1359

# range optimizer problem

1350

1360

1351

1361

1352

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

1362

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

1353

1363

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

1354

1364

--error 1264

1355

1365

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

1442

1452

# @TODO The below fails because it assumes latin1

1443

1453

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

1444

1454

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

1445

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

1455

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

1446

1456

#drop table t1;

1447

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

1457

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

1448

1458

#show create table t1;

1449

1459

#drop table t1;

1450

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

1460

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

1451

1461

#show create table t1;

1452

1462

#drop table t1;

1453

1463

1454

1464

#eval set storage_engine=$default;

1455

1465

1456

1466

# InnoDB specific varchar tests

1457

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

1467

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

1458

1468

#drop table t1;

1459

1469

1460

1470

1461

1471

# BUG#11039 Wrong key length in min()

1462

1472

1463

1473

1464

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

1474

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

1465

1475

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

1466

1476

select min(a) from t1;

1467

1477

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

1471

1481

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

1472

1482

1473

1483

1474

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

1484

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;

1475

1485

insert into t1 (b) values (1);

1476

1486

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

1477

1487

select * from t1;

1483

1493

select * from t1;

1484

1494

drop table t1;

1485

1495

1486

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

1496

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

1487

1497

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

1488

1498

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

1489

1499

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

1498

1508

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

1499

1509

1500

1510

1501

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

1511

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

1502

1512

insert into t1 (val) values (1);

1503

1513

update t1 set a=2 where a=1;

1504

1514

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

1514

1524

1515

1525

1516

1526

--disable_warnings

1517

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

1527

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

1518

1528

--enable_warnings

1519

1529

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

1520

1530

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

1524

1534

1525

1535

# Bug #12340 multitable delete deletes only one record

1526

1536

1527

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

1528

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

1537

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

1538

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

1529

1539

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

1530

1540

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

1531

1541

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

1539

1549

# (bug #11946)

1540

1550

1541

1551

1542

eval CREATE TABLE t1 (

1552

eval create $temp table t1 (

1543

1553

id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)

1544

1554

) ENGINE=$engine_type;

1545

1555

1546

eval CREATE TABLE t2 (

1556

eval create $temp table t2 (

1547

1557

id INTEGER NOT NULL,

1548

1558

FOREIGN KEY (id) REFERENCES t1 (id)

1549

1559

) ENGINE=$engine_type;

1563

1573

DROP TABLE t2, t1;

1564

1574

1565

1575

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

1566

eval CREATE TABLE t1

1576

eval create $temp table t1

1567

1577

(

1568

1578

id INT PRIMARY KEY

1569

1579

) ENGINE=$engine_type;

1590

1600

1591

1601

1592

1602

# prefix index

1593

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

1603

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

1594

1604

character set = latin1 engine = $engine_type;

1595

1605

1596

1606

# normal indexes

1597

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

1598

character set = latin1 engine = $engine_type;

1599

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

1600

character set = latin1 engine = $engine_type;

1601

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

1602

character set = latin1 engine = $engine_type;

1603

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

1604

character set = latin1 engine = $engine_type;

1605

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

1606

character set = latin1 engine = $engine_type;

1607

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

1608

character set = latin1 engine = $engine_type;

1609

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

1607

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

1608

character set = latin1 engine = $engine_type;

1609

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

1610

character set = latin1 engine = $engine_type;

1611

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

1612

character set = latin1 engine = $engine_type;

1613

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

1614

character set = latin1 engine = $engine_type;

1615

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

1616

character set = latin1 engine = $engine_type;

1617

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

1618

character set = latin1 engine = $engine_type;

1619

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

1610

1620

character set = latin1 engine = $engine_type;

1611

1621

1612

1622

1613

1623

# multi-column indexes are allowed to be longer

1614

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

1624

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

1615

1625

character set = latin1 engine = $engine_type;

1616

1626

1617

1627

show create table t9;

1619

1629

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

1620

1630

1621

1631

# these should have their index length trimmed

1622

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

1623

character set = latin1 engine = $engine_type;

1624

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

1625

character set = latin1 engine = $engine_type;

1626

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

1627

character set = latin1 engine = $engine_type;

1628

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

1632

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

1633

character set = latin1 engine = $engine_type;

1634

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

1635

character set = latin1 engine = $engine_type;

1636

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

1637

character set = latin1 engine = $engine_type;

1638

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

1629

1639

character set = latin1 engine = $engine_type;

1630

1640

1631

1641

show create table t1;

1643

1653

1644

1654

# these should be refused

1645

1655

--error 1071

1646

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

1647

character set = latin1 engine = $engine_type;

1648

--error 1071

1649

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

1650

character set = latin1 engine = $engine_type;

1651

--error 1071

1652

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

1653

character set = latin1 engine = $engine_type;

1654

--error 1071

1655

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

1656

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

1657

character set = latin1 engine = $engine_type;

1658

--error 1071

1659

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

1660

character set = latin1 engine = $engine_type;

1661

--error 1071

1662

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

1663

character set = latin1 engine = $engine_type;

1664

--error 1071

1665

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

1656

1666

character set = latin1 engine = $engine_type;

1657

1667

1658

1668

}

1663

1673

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

1664

1674

1665

1675

1666

eval CREATE TABLE t1

1676

eval create $temp table t1

1667

1677

(

1668

1678

id INT PRIMARY KEY

1669

1679

) ENGINE=$engine_type;

1670

1680

1671

eval CREATE TABLE t2

1681

eval create $temp table t2

1672

1682

(

1673

1683

v INT,

1674

1684

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

1705

1715

connect (a,localhost,root,,);

1706

1716

connect (b,localhost,root,,);

1707

1717

connection a;

1708

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

1718

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

1709

1719

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

1710

1720

set autocommit=0;

1711

1721

checksum table t1;

1725

1735

# autocommit = 1

1726

1736

1727

1737

connection a;

1728

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

1738

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

1729

1739

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

1730

1740

set autocommit=1;

1731

1741

checksum table t1;

1751

1761

# test that FKs between invalid types are not accepted

1752

1762

1753

1763

set foreign_key_checks=0;

1754

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

1764

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

1755

1765

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

1756

1766

-- error 1005

1757

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

1767

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

1758

1768

set foreign_key_checks=1;

1759

1769

drop table t2;

1760

1770

1762

1772

# when f_k_c is 0

1763

1773

1764

1774

set foreign_key_checks=0;

1765

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

1775

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

1766

1776

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

1767

1777

-- error 1005

1768

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

1778

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

1769

1779

set foreign_key_checks=1;

1770

1780

drop table t1;

1771

1781

1772

1782

# test that invalid datatype conversions with ALTER are not allowed

1773

1783

1774

1784

set foreign_key_checks=0;

1775

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

1776

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

1785

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

1786

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

1777

1787

-- error 1025,1025

1778

1788

alter table t1 modify column a int;

1779

1789

set foreign_key_checks=1;

1782

1792

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

1783

1793

1784

1794

set foreign_key_checks=0;

1785

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

1786

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

1795

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

1796

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

1787

1797

alter table t1 convert to character set utf8;

1788

1798

set foreign_key_checks=1;

1789

1799

drop table t2,t1;

1791

1801

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

1792

1802

1793

1803

set foreign_key_checks=0;

1794

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

1795

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

1804

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

1805

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

1796

1806

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

1797

1807

-- error 1025

1798

1808

rename table t3 to t1;

1801

1811

1802

1812

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

1803

1813

1804

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

1805

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

1806

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

1807

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

1814

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

1815

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

1816

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

1817

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

1808

1818

1809

1819

insert into t1 values(1);

1810

1820

insert into t3 values(1);

1845

1855

1846

1856

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

1847

1857

1848

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

1858

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

1849

1859

b varchar(255) character set utf8,

1850

1860

c varchar(255) character set utf8,

1851

1861

d varchar(255) character set utf8,

1852

1862

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

1853

1863

drop table t1;

1854

1864

--error ER_TOO_LONG_KEY

1855

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

1865

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

1856

1866

b varchar(255) character set utf8,

1857

1867

c varchar(255) character set utf8,

1858

1868

d varchar(255) character set utf8,

1862

1872

1863

1873

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

1864

1874

1865

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

1866

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

1867

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

1868

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

1875

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

1876

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

1877

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

1878

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

1869

1879

1870

1880

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

1871

1881

-- error ER_DUP_ENTRY

1886

1896

1887

1897

if (test_foreign_keys)

1888

1898

{

1889

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

1890

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

1899

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

1900

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

1891

1901

1892

1902

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

1893

1903

-- error 1452

1920

1930

1921

1931

drop table t2,t1;

1922

1932

1923

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

1924

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

1933

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

1934

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

1925

1935

1926

1936

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

1927

1937

insert into t2 values(0x41);

1945

1955

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

1946

1956

# generated foreign key identifier. (Bug #16387)

1947

1957

1948

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

1949

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

1958

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

1959

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

1950

1960

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

1951

1961

ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;

1952

1962

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

1953

1963

ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0;

1954

SHOW CREATE TABLE t2;

1964

SHOW create $temp table t2;

1955

1965

DROP TABLE t2,t1;

1956

1966

}

1957

1967

1962

1972

# error message (bug #9680)

1963

1973

1964

1974

1965

eval CREATE TABLE t1 (

1975

eval create $temp table t1 (

1966

1976

field1 varchar(8) NOT NULL DEFAULT '',

1967

1977

field2 varchar(8) NOT NULL DEFAULT '',

1968

1978

PRIMARY KEY (field1, field2)

1969

1979

) ENGINE=$engine_type;

1970

1980

1971

eval CREATE TABLE t2 (

1981

eval create $temp table t2 (

1972

1982

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

1973

1983

FOREIGN KEY (field1) REFERENCES t1 (field1)

1974

1984

ON DELETE CASCADE ON UPDATE CASCADE

1989

1999

1990

2000

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

1991

2001

1992

eval create table t1 (

2002

eval create $temp table t1 (

1993

2003

c1 bigint not null,

1994

2004

c2 bigint not null,

1995

2005

primary key (c1),

1996

2006

unique key (c2)

1997

2007

) engine=$engine_type;

1998

2008

1999

eval create table t2 (

2009

eval create $temp table t2 (

2000

2010

c1 bigint not null,

2001

2011

primary key (c1)

2002

2012

) engine=$engine_type;

2016

2026

# Bug #14360: problem with intervals

2017

2027

2018

2028

2019

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

2020

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

2029

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

2030

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

2021

2031

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

2022

2032

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

2023

2033

select * from t1, t2

2024

2034

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

2025

2035

drop table t1, t2;

2026

2036

2027

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

2037

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

2028

2038

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

2029

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

2039

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

2030

2040

primary key(id)) engine=$engine_type;

2031

2041

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

2032

2042

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

2043

2053

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

2044

2054

# update (because READ COMMITTED isolation level)

2045

2055

2046

2056

if(!$using_temp)

2057

{

2047

2058

connect (a,localhost,root,,);

2048

2059

connect (b,localhost,root,,);

2049

2060

connection a;

2050

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

2061

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

2051

2062

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

2052

2063

commit;

2053

2064

set autocommit = 0;

2068

2079

connection default;

2069

2080

disconnect a;

2070

2081

disconnect b;

2071

2082

}

2072

2083

if ($test_transactions)

2073

2084

{

2074

2085

2079

2090

connect (a,localhost,root,,);

2080

2091

connect (b,localhost,root,,);

2081

2092

connection a;

2082

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

2093

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

2083

2094

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

2084

2095

commit;

2085

2096

set autocommit = 0;

2115

2126

connect (a,localhost,root,,);

2116

2127

connect (b,localhost,root,,);

2117

2128

connection a;

2118

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

2129

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

2119

2130

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

2120

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

2131

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

2121

2132

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

2122

2133

commit;

2123

2134

set autocommit = 0;

2127

2138

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2128

2139

insert into t1 select * from t2;

2129

2140

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

2130

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

2141

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

2131

2142

select * from t2;

2132

2143

commit;

2133

2144

connection a;

2171

2182

connect (j,localhost,root,,);

2172

2183

eval SET SESSION STORAGE_ENGINE = $engine_type;

2173

2184

connection a;

2174

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

2185

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

2175

2186

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

2176

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

2187

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

2177

2188

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

2178

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

2189

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

2179

2190

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

2180

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

2191

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

2181

2192

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

2182

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

2193

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

2183

2194

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

2184

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

2195

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

2185

2196

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

2186

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

2197

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

2187

2198

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

2188

2199

commit;

2189

2200

set autocommit = 0;

2202

2213

set autocommit = 0;

2203

2214

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2204

2215

--send

2205

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

2216

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

2206

2217

connection e;

2207

2218

set autocommit = 0;

2208

2219

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2217

2228

set autocommit = 0;

2218

2229

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2219

2230

--send

2220

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

2231

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

2221

2232

connection h;

2222

2233

set autocommit = 0;

2223

2234

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2232

2243

set autocommit = 0;

2233

2244

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2234

2245

--send

2235

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

2246

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

2236

2247

2237

2248

connection b;

2238

2249

--error 1205

2292

2303

{

2293

2304

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

2294

2305

--error 1005

2295

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

2306

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

2296

2307

2297

2308

2298

2309

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

2299

2310

2300

2311

2301

eval CREATE TABLE t1 (

2312

eval create $temp table t1 (

2302

2313

a BIGINT(20) NOT NULL,

2303

2314

PRIMARY KEY (a)

2304

2315

) ENGINE=$engine_type DEFAULT CHARSET=UTF8;

2305

2316

2306

eval CREATE TABLE t2 (

2317

eval create $temp table t2 (

2307

2318

a BIGINT(20) NOT NULL,

2308

2319

b VARCHAR(128) NOT NULL,

2309

2320

c TEXT NOT NULL,

2335

2346

# Bug #15680 (SPATIAL key in innodb)

2336

2347

2337

2348

--error ER_TABLE_CANT_HANDLE_SPKEYS

2338

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

2349

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

2339

2350

}

2340

2351

2341

2352

2342

2353

# Test optimize on table with open transaction

2343

2354

2344

2355

2345

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

2356

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

2346

2357

BEGIN;

2347

2358

INSERT INTO t1 VALUES (1);

2348

2359

OPTIMIZE TABLE t1;

Older »