~drizzle-trunk/drizzle/development

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

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

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

update ignore t1 set id = 8, name = 'Sinisa' where id < 3;

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

update ignore t1 set id = id + 10, name = 'Ralph' where id < 4;

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

drop table t1;

# A bit bigger test

# The 'replace_column' statements are needed because the cardinality calculated

# by innodb is not always the same between runs

eval CREATE TABLE t1 (

id int(11) NOT NULL auto_increment,

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

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

PRIMARY KEY (id),

KEY parent_id (parent_id),

KEY level (level)

) engine=$engine_type;

INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1),(179,5,2);

update t1 set parent_id=parent_id+100;

select * from t1 where parent_id=102;

update t1 set id=id+1000;

-- error ER_DUP_ENTRY,1022

100

update t1 set id=1024 where id=1009;

101

select * from t1;

102

update ignore t1 set id=id+1; # This will change all rows

103

select * from t1;

104

update ignore t1 set id=1023 where id=1010;

105

select * from t1 where parent_id=102;

106

--replace_column 9 #

107

explain select level from t1 where level=1;

108

--replace_column 9 #

109

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

110

--replace_column 9 #

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

optimize table t1;

115

--replace_column 7 #

116

show keys from t1;

117

drop table t1;

118

119

120

# Test replace

121

122

123

eval CREATE TABLE t1 (

124

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

125

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

126

PRIMARY KEY (gesuchnr,benutzer_id)

127

) engine=$engine_type;

128

129

replace into t1 (gesuchnr,benutzer_id) values (2,1);

130

replace into t1 (gesuchnr,benutzer_id) values (1,1);

131

replace into t1 (gesuchnr,benutzer_id) values (1,1);

132

select * from t1;

133

drop table t1;

134

135

136

# test delete using hidden_primary_key

137

138

139

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

143

select * from t1;

144

check table t1;

145

drop table t1;

146

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;

151

create index skr on t1 (a);

152

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

153

analyze table t1;

154

--replace_column 7 #

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

165

166

if ($test_transactions)

167

{

168

169

# Test rollback

170

171

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;

176

select n, "after rollback" from t1;

177

insert into t1 values (4);

178

commit;

179

select n, "after commit" from t1;

180

commit;

181

insert into t1 values (5);

182

-- error ER_DUP_ENTRY

183

insert into t1 values (4);

184

commit;

185

select n, "after commit" from t1;

186

set autocommit=1;

187

insert into t1 values (6);

188

-- error ER_DUP_ENTRY

189

insert into t1 values (4);

190

select n from t1;

191

set autocommit=0;

192

193

# savepoints

194

195

begin;

196

savepoint `my_savepoint`;

197

insert into t1 values (7);

198

savepoint `savept2`;

199

insert into t1 values (3);

200

select n from t1;

201

savepoint savept3;

202

rollback to savepoint savept2;

203

--error 1305

204

rollback to savepoint savept3;

205

rollback to savepoint savept2;

206

release savepoint `my_savepoint`;

207

select n from t1;

208

-- error 1305

209

rollback to savepoint `my_savepoint`;

210

--error 1305

211

rollback to savepoint savept2;

212

insert into t1 values (8);

213

savepoint sv;

214

commit;

215

savepoint sv;

216

set autocommit=1;

217

# nop

218

rollback;

219

drop table t1;

220

221

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;

226

start transaction;

227

insert into t1 values (4);

228

flush tables with read lock;

229

230

# Current code can't handle a read lock in middle of transaction

231

#--error 1223;

232

commit;

233

unlock tables;

234

commit;

235

select * from t1;

236

drop table t1;

237

238

239

# Testing transactions

240

241

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;

246

rollback;

247

select id as afterrollback_id,nom as afterrollback_nom from t1;

248

set autocommit=0;

249

insert into t1 values(2,'mysql');

250

select id as afterautocommit0_id,nom as afterautocommit0_nom from t1;

251

rollback;

252

select id as afterrollback_id,nom as afterrollback_nom from t1;

253

set autocommit=1;

254

drop table t1;

255

256

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;

261

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

262

-- error ER_DUP_ENTRY

263

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

264

delete from t1;

265

delete from t1 where id = 'pippo';

266

select * from t1;

267

268

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

269

set autocommit=0;

270

delete from t1;

271

rollback;

272

select * from t1;

273

delete from t1;

274

commit;

275

select * from t1;

276

drop table t1;

277

278

279

# Test of active transactions

280

281

282

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

286

insert into t1 values (1);

287

rollback;

288

drop table t1;

289

rename table t2 to t1;

290

drop table t1;

291

set autocommit=1;

292

293

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;

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;

303

set autocommit=0;

304

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

305

COMMIT;

306

SELECT * FROM t1;

307

SELECT _userid FROM t1 WHERE _userid='marc@anyware.co.uk';

308

drop table t1;

309

set autocommit=1;

310

311

}

312

# End of transactional tests

313

314

315

# Test when reading on part of unique key

316

317

eval CREATE TABLE t1 (

318

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

319

name varchar(100),

320

phone varchar(100),

321

ref_email varchar(100) DEFAULT '' NOT NULL,

322

detail varchar(200),

323

PRIMARY KEY (user_id,ref_email)

324

)engine=$engine_type;

325

326

INSERT INTO t1 VALUES (10292,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10292,'shirish','2333604','shirish@yahoo.com','ddsds'),(10292,'sonali','323232','sonali@bolly.com','filmstar');

327

select * from t1 where user_id=10292;

328

INSERT INTO t1 VALUES (10291,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10293,'shirish','2333604','shirish@yahoo.com','ddsds');

329

select * from t1 where user_id=10292;

330

select * from t1 where user_id>=10292;

331

select * from t1 where user_id>10292;

332

select * from t1 where user_id<10292;

333

drop table t1;

334

335

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,

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;

343

drop table t1;

344

345

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;

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 not null;

356

select * from t1;

357

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

358

select * from t1;

359

drop table t1;

360

361

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;

367

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

368

select * from t1;

369

insert into t2 select * from t1;

370

select * from t2;

371

delete from t1 where b = 3;

372

select * from t1;

373

insert into t1 select * from t2;

374

select * from t1;

375

select * from t2;

376

drop table t1,t2;

377

378

379

# Search on unique key

380

381

382

eval CREATE TABLE t1 (

383

id int(11) NOT NULL auto_increment,

384

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

385

email varchar(64) DEFAULT '' NOT NULL,

386

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

387

PRIMARY KEY (id),

388

UNIQUE ggid (ggid)

389

) ENGINE=$engine_type;

390

391

insert into t1 (ggid,passwd) values ('test1','xxx');

392

insert into t1 (ggid,passwd) values ('test2','yyy');

393

-- error ER_DUP_ENTRY

394

insert into t1 (ggid,passwd) values ('test2','this will fail');

395

-- error ER_DUP_ENTRY

396

insert into t1 (ggid,id) values ('this will fail',1);

397

398

select * from t1 where ggid='test1';

399

select * from t1 where passwd='xxx';

400

select * from t1 where id=2;

401

402

replace into t1 (ggid,id) values ('this will work',1);

403

replace into t1 (ggid,passwd) values ('test2','this will work');

404

-- error ER_DUP_ENTRY

405

update t1 set id=100,ggid='test2' where id=1;

406

select * from t1;

407

select * from t1 where id=1;

408

select * from t1 where id=999;

409

drop table t1;

410

411

412

# ORDER BY on not primary key

413

414

415

eval CREATE TABLE t1 (

416

user_name varchar(12),

417

password text,

418

subscribed char(1),

419

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

420

quota bigint(20),

421

weight double,

422

access_date date,

423

access_time time,

424

approved datetime,

425

dummy_primary_key int(11) NOT NULL auto_increment,

426

PRIMARY KEY (dummy_primary_key)

427

) ENGINE=$engine_type;

428

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

429

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

430

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

431

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

432

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

433

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

434

drop table t1;

435

436

437

# Testing of tables without primary keys

438

439

440

eval CREATE TABLE t1 (

441

id int(11) NOT NULL auto_increment,

442

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

443

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

444

KEY (id),

445

KEY parent_id (parent_id),

446

KEY level (level)

447

) engine=$engine_type;

448

449

INSERT INTO t1 values (179,5,2);

450

update t1 set parent_id=parent_id+100;

451

select * from t1 where parent_id=102;

452

update t1 set id=id+1000;

453

update t1 set id=1024 where id=1009;

454

select * from t1;

455

update ignore t1 set id=id+1; # This will change all rows

456

select * from t1;

457

update ignore t1 set id=1023 where id=1010;

458

select * from t1 where parent_id=102;

459

--replace_column 9 #

460

explain select level from t1 where level=1;

461

select level,id from t1 where level=1;

462

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

463

select level,id from t1 where level=1 order by id;

464

delete from t1 where level=1;

465

select * from t1;

466

drop table t1;

467

468

469

# Test of index only reads

470

471

eval CREATE TABLE t1 (

472

sca_code char(6) NOT NULL,

473

cat_code char(6) NOT NULL,

474

sca_desc varchar(50),

475

lan_code char(2) NOT NULL,

476

sca_pic varchar(100),

477

sca_sdesc varchar(50),

478

sca_sch_desc varchar(16),

479

PRIMARY KEY (sca_code, cat_code, lan_code),

480

INDEX sca_pic (sca_pic)

481

) engine = $engine_type ;

482

483

INSERT INTO t1 ( sca_code, cat_code, sca_desc, lan_code, sca_pic, sca_sdesc, sca_sch_desc) VALUES ( 'PD', 'J', 'PENDANT', 'EN', NULL, NULL, 'PENDANT'),( 'RI', 'J', 'RING', 'EN', NULL, NULL, 'RING'),( 'QQ', 'N', 'RING', 'EN', 'not null', NULL, 'RING');

484

select count(*) from t1 where sca_code = 'PD';

485

select count(*) from t1 where sca_code <= 'PD';

486

select count(*) from t1 where sca_pic is null;

487

alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic);

488

select count(*) from t1 where sca_code='PD' and sca_pic is null;

489

select count(*) from t1 where cat_code='E';

490

491

alter table t1 drop index sca_pic, add index (sca_pic, cat_code);

492

select count(*) from t1 where sca_code='PD' and sca_pic is null;

493

select count(*) from t1 where sca_pic >= 'n';

494

select sca_pic from t1 where sca_pic is null;

495

update t1 set sca_pic="test" where sca_pic is null;

496

delete from t1 where sca_code='pd';

497

drop table t1;

498

499

500

# Test of opening table twice and timestamps

501

502

set @a:=now();

503

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

504

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

505

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

506

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

507

update t1 set a=5 where a=1;

508

select a from t1;

509

drop table t1;

510

511

512

# Test with variable length primary key

513

514

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

515

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

516

select * from t1 order by b desc;

517

optimize table t1;

518

--replace_column 7 #

519

show keys from t1;

520

drop table t1;

521

522

523

# Test of create index with NULL columns

524

525

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

526

insert into t1 values (1,2);

527

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

528

create index ax1 on t1 (i,j);

529

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

530

drop table t1;

531

532

533

# Test min-max optimization

534

535

536

eval CREATE TABLE t1 (

537

a int3 unsigned NOT NULL,

538

b int1 unsigned NOT NULL,

539

UNIQUE (a, b)

540

) ENGINE = $engine_type;

541

542

INSERT INTO t1 VALUES (1, 1);

543

SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1;

544

drop table t1;

545

546

547

# Test INSERT DELAYED

548

549

550

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

551

# Can't test this in 3.23

552

# INSERT DELAYED INTO t1 VALUES (1);

553

INSERT INTO t1 VALUES (1);

554

SELECT * FROM t1;

555

DROP TABLE t1;

556

557

558

559

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

560

561

562

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

563

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

564

--replace_column 9 #

565

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

566

drop table t1;

567

568

569

# Test lock tables

570

571

572

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

573

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

574

LOCK TABLES t1 WRITE;

575

--error ER_DUP_ENTRY

576

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

577

select id from t1;

578

select id from t1;

579

UNLOCK TABLES;

580

DROP TABLE t1;

581

582

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

583

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

584

LOCK TABLES t1 WRITE;

585

begin;

586

--error ER_DUP_ENTRY

587

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

588

select id from t1;

589

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

590

commit;

591

select id,id3 from t1;

592

UNLOCK TABLES;

593

DROP TABLE t1;

594

595

596

# Test prefix key

597

598

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

599

drop table t1;

600

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

601

show create table t1;

602

drop table t1;

603

604

605

# Test using temporary table and auto_increment

606

607

608

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

609

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

610

delete from t1 where a=3;

611

insert into t1 values (NULL);

612

select * from t1;

613

alter table t1 add b int;

614

select * from t1;

615

drop table t1;

616

617

#Slashdot bug

618

eval create table t1

619

(

620

id int auto_increment primary key,

621

name varchar(32) not null,

622

value text not null,

623

uid int not null,

624

unique key(name,uid)

625

) engine=$engine_type;

626

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

627

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

628

set insert_id=5;

629

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

630

delete from t1 where uid=102;

631

set insert_id=5;

632

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

633

set insert_id=6;

634

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

635

select * from t1;

636

drop table t1;

637

638

639

# Test DROP DATABASE

640

641

# ML: Test logics

642

# Check that the creation of a table with engine = $engine_type does

643

# in a certain database (already containing some tables using other

644

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

645

646

create database mysqltest;

647

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

648

insert into mysqltest.t1 values(1);

649

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

650

insert into mysqltest.t2 values(1);

651

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

652

insert into mysqltest.t3 values(1);

653

commit;

654

drop database mysqltest;

655

# Don't check error message

656

--error 1049

657

show tables from mysqltest;

658

659

660

# Test truncate table with and without auto_commit

661

662

663

set autocommit=0;

664

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

665

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

666

truncate table t1;

667

commit;

668

truncate table t1;

669

truncate table t1;

670

select * from t1;

671

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

672

delete from t1;

673

select * from t1;

674

commit;

675

drop table t1;

676

set autocommit=1;

677

678

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

679

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

680

truncate table t1;

681

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

682

select * from t1;

683

truncate table t1;

684

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

685

delete from t1;

686

select * from t1;

687

drop table t1;

688

689

690

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

691

692

693

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

694

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

695

--replace_column 9 #

696

explain select * from t1 order by a;

697

--replace_column 9 #

698

explain select * from t1 order by b;

699

--replace_column 9 #

700

explain select * from t1 order by c;

701

--replace_column 9 #

702

explain select a from t1 order by a;

703

--replace_column 9 #

704

explain select b from t1 order by b;

705

--replace_column 9 #

706

explain select a,b from t1 order by b;

707

--replace_column 9 #

708

explain select a,b from t1;

709

--replace_column 9 #

710

explain select a,b,c from t1;

711

drop table t1;

712

713

714

# Check describe

715

716

717

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

718

desc t1;

719

drop table t1;

720

721

722

# Test of multi-table-delete

723

724

725

eval CREATE TABLE t1 (

726

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

727

cname char(15) NOT NULL default '',

728

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

729

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

730

last_mod_date timestamp NOT NULL,

731

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

732

last_app_date timestamp NOT NULL,

733

last_app_id smallint(6) default '-1',

734

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

735

assigned_scps int(11) default '0',

736

status tinyint(4) default '0'

737

) ENGINE=$engine_type;

738

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

739

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

740

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

741

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

742

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

743

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

744

eval CREATE TABLE t2 (

745

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

746

cname char(15) NOT NULL default '',

747

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

748

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

749

last_mod_date timestamp NOT NULL,

750

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

751

last_app_date timestamp NOT NULL,

752

last_app_id smallint(6) default '-1',

753

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

754

assigned_scps int(11) default '0',

755

status tinyint(4) default '0'

756

) ENGINE=$engine_type;

757

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

758

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

759

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

760

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

761

select * from t1;

762

select * from t2;

763

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

764

select * from t1;

765

select * from t2;

766

select * from t2;

767

drop table t1,t2;

768

769

770

# A simple test with some isolation levels

771

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

772

# this works.

773

774

775

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

776

777

BEGIN;

778

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

779

SELECT @@tx_isolation,@@global.tx_isolation;

780

insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David');

781

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

782

COMMIT;

783

784

BEGIN;

785

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

786

insert into t1 (code, name) values (2, 'Erik'), (3, 'Sasha');

787

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

788

COMMIT;

789

790

BEGIN;

791

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

792

insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt');

793

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

794

COMMIT;

795

DROP TABLE t1;

796

797

798

# Test of multi-table-update

799

800

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

801

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

802

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

803

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

804

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

805

select * from t1;

806

select * from t2;

807

drop table t1,t2;

808

809

810

# Testing of IFNULL

811

812

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

813

insert into t1 values(20,null);

814

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

815

t2.b=t3.a;

816

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

817

t2.b=t3.a order by 1;

818

insert into t1 values(10,null);

819

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

820

t2.b=t3.a order by 1;

821

drop table t1;

822

823

824

# Test of read_through not existing const_table

825

826

827

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

828

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

829

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

830

drop table t1,t2;

831

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

832

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

833

insert into t1 values (10, 20);

834

insert into t2 values (10, 20);

835

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

836

drop table t1,t2;

837

838

if ($test_foreign_keys)

839

{

840

841

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

842

843

844

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

845

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

846

insert into t1 set id=1;

847

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

848

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

849

select * from t1;

850

select * from t2;

851

drop table t2,t1;

852

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

853

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

854

INSERT INTO t1 VALUES(1);

855

INSERT INTO t2 VALUES(1, 1);

856

SELECT * from t1;

857

UPDATE t1,t2 SET t1.id=t1.id+1, t2.t1_id=t1.id+1;

858

SELECT * from t1;

859

UPDATE t1,t2 SET t1.id=t1.id+1 where t1.id!=t2.id;

860

SELECT * from t1;

861

DROP TABLE t1,t2;

862

}

863

864

if ($test_transactions)

865

{

866

867

# Test of range_optimizer

868

869

870

set autocommit=0;

871

872

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

873

874

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

875

876

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

877

878

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

879

COMMIT;

880

881

INSERT INTO t1 VALUES("this-key", "will disappear");

882

INSERT INTO t2 VALUES("this-key", "will also disappear");

883

DELETE FROM t3 WHERE id1="my-test-1";

884

885

SELECT * FROM t1;

886

SELECT * FROM t2;

887

SELECT * FROM t3;

888

ROLLBACK;

889

890

SELECT * FROM t1;

891

SELECT * FROM t2;

892

SELECT * FROM t3;

893

SELECT * FROM t3 WHERE id1="my-test-1" LOCK IN SHARE MODE;

894

COMMIT;

895

set autocommit=1;

896

DROP TABLE t1,t2,t3;

897

}

898

899

900

# Check update with conflicting key

901

902

903

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

904

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

905

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

906

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

907

SELECT * from t1;

908

drop table t1;

909

910

911

# Test multi update with different join methods

912

913

914

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

915

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

916

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

917

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

918

919

# Full join, without key

920

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

921

select * from t1;

922

923

# unique key

924

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

925

select * from t1;

926

927

# ref key

928

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

929

select * from t1;

930

931

# Range key (in t1)

932

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

933

select * from t1;

934

select * from t2;

935

936

drop table t1,t2;

937

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

938

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

939

SET AUTOCOMMIT=0;

940

INSERT INTO t1 ( B_ID ) VALUES ( 1 );

941

INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );

942

ROLLBACK;

943

SELECT * FROM t1;

944

drop table t1,t2;

945

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

946

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

947

select distinct parent,child from t1 order by parent;

948

drop table t1;

949

950

951

# Test that MySQL priorities clustered indexes

952

953

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

954

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

955

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

956

insert into t2 (a) select b from t1;

957

insert into t1 (b) select b from t2;

958

insert into t2 (a) select b from t1;

959

insert into t1 (a) select b from t2;

960

insert into t2 (a) select b from t1;

961

insert into t1 (a) select b from t2;

962

insert into t2 (a) select b from t1;

963

insert into t1 (a) select b from t2;

964

insert into t2 (a) select b from t1;

965

insert into t1 (a) select b from t2;

966

insert into t2 (a) select b from t1;

967

insert into t1 (a) select b from t2;

968

insert into t2 (a) select b from t1;

969

insert into t1 (a) select b from t2;

970

insert into t2 (a) select b from t1;

971

insert into t1 (a) select b from t2;

972

insert into t2 (a) select b from t1;

973

insert into t1 (a) select b from t2;

974

select count(*) from t1;

975

--replace_column 9 #

976

explain select * from t1 where c between 1 and 2500;

977

update t1 set c=a;

978

--replace_column 9 #

979

explain select * from t1 where c between 1 and 2500;

980

drop table t1,t2;

981

982

983

# Test of UPDATE ... ORDER BY

984

985

986

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

987

988

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

989

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

990

SELECT * from t1;

991

drop table t1;

992

993

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

994

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

995

SET @tmp=0;

996

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

997

update t1 set b=99 where a=1 order by b asc limit 1;

998

update t1 set b=100 where a=1 order by b desc limit 2;

999

update t1 set a=a+10+b where a=1 order by b;

1000

select * from t1 order by a,b;

1001

drop table t1;

1002

1003

1004

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

1005

1006

1007

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

1008

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

1009

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

1010

1011

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

1012

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

1013

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

1014

update t1 set a=c, b=c;

1015

1016

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

1017

insert into t2 select * from t1;

1018

1019

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

1020

drop table t1,t2;

1021

1022

1023

# test autoincrement with TRUNCATE

1024

1025

1026

SET AUTOCOMMIT=1;

1027

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

1028

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

1029

truncate table t1;

1030

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

1031

SELECT * from t1;

1032

drop table t1;

1033

1034

1035

if ($test_foreign_keys)

1036

{

1037

1038

# Test dictionary handling with spaceand quoting

1039

1040

1041

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

1042

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

1043

#show create table t2;

1044

drop table t2,t1;

1045

1046

1047

# Test of multi updated and foreign keys

1048

1049

1050

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

1051

insert into `t1`values ( 1 ) ;

1052

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

1053

insert into `t2`values ( 1 ) ;

1054

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

1055

insert into `t3`values ( 1 ) ;

1056

--error 1451

1057

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

1058

--error 1451

1059

update t1,t2,t3 set t3.id=5, t2.id=6, t1.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;

1060

--error 1054

1061

update t3 set t3.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;

1062

drop table t3,t2,t1;

1063

1064

1065

# test for recursion depth limit

1066

1067

eval create table t1(

1068

id int primary key,

1069

pid int,

1070

index(pid),

1071

foreign key(pid) references t1(id) on delete cascade) engine=$engine_type;

1072

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

1073

(8,7),(9,8),(10,9),(11,10),(12,11),(13,12),(14,13),(15,14);

1074

-- error 1451

1075

delete from t1 where id=0;

1076

delete from t1 where id=15;

1077

delete from t1 where id=0;

1078

1079

drop table t1;

1080

}

1081

# End of FOREIGN KEY tests

1082

1083

1084

# Test timestamps

1085

1086

1087

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

1088

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

1089

(stamp))ENGINE=$engine_type;

1090

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

1091

# Note that timestamp 3 is wrong

1092

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

1093

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

1094

'20020204120000' GROUP BY col1;

1095

drop table t1,t2;

1096

1097

1098

# Test by Francois MASUREL

1099

1100

1101

eval CREATE TABLE t1 (

1102

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

1103

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

1104

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

1105

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

1106

`description` text,

1107

PRIMARY KEY (`id`),

1108

KEY `id_object` (`id_object`),

1109

KEY `id_version` (`id_version`)

1110

) ENGINE=$engine_type;

1111

1112

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

1113

1114

eval CREATE TABLE t2 (

1115

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

1116

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

1117

PRIMARY KEY (`id`),

1118

KEY `id_version` (`id_version`)

1119

) ENGINE=$engine_type;

1120

1121

INSERT INTO t2 VALUES("3524", "1"),("3525", "1"),("1794", "4"),("102", "5"),("1822", "6"),("3382", "9");

1122

1123

SELECT t2.id, t1.`label` FROM t2 INNER JOIN

1124

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

1125

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

1126

drop table t1,t2;

1127

1128

# Live checksum feature available + enabled

1129

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

1130

# Live checksum feature available + disabled

1131

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

1132

1133

# Live checksum feature not available + enabled

1134

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

1135

# Live checksum feature not available + disabled

1136

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

1137

1138

# Live checksum feature probably available + enabled

1139

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

1140

# Live checksum feature probably available + disabled

1141

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

1142

1143

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

1144

insert t2 select * from t1;

1145

insert t3 select * from t1;

1146

insert t4 select * from t1;

1147

insert t5 select * from t1;

1148

insert t6 select * from t1;

1149

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

1150

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

1151

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

1152

# #show table status;

1153

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

1154

1155

1156

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

1157

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

1158

1159

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

1160

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

1161

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

1162

drop table t1;

1163

1164

1165

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

1166

1167

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

1168

create table t2 like t1;

1169

show create table t2;

1170

drop table t1,t2;

1171

1172

if ($test_foreign_keys)

1173

{

1174

1175

# Test of automaticly created foreign keys

1176

1177

1178

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

1179

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

1180

show create table t1;

1181

show create table t2;

1182

create index id on t2 (id);

1183

show create table t2;

1184

create index id2 on t2 (id);

1185

show create table t2;

1186

drop index id2 on t2;

1187

--error 1025,1025

1188

drop index id on t2;

1189

show create table t2;

1190

drop table t2;

1191

1192

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

1193

show create table t2;

1194

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

1195

show create table t2;

1196

drop table t2;

1197

1198

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

1199

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

1200

show create table t2;

1201

drop table t2;

1202

1203

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

1204

show create table t2;

1205

drop table t2;

1206

1207

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

1208

show create table t2;

1209

drop table t2;

1210

1211

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

1212

show create table t2;

1213

drop table t2;

1214

1215

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

1216

show create table t2;

1217

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

1218

show create table t2;

1219

drop table t2;

1220

1221

# Test error handling

1222

1223

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

1224

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

1225

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

1226

--error 1005

1227

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

1228

1229

# bug#3749

1230

1231

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

1232

show create table t2;

1233

drop table t2;

1234

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

1235

show create table t2;

1236

drop table t2, t1;

1237

}

1238

# End of FOREIGN KEY tests

1239

1240

1241

1242

# Let us test binlog_cache_use and binlog_cache_disk_use status vars.

1243

# Actually this test has nothing to do with innodb per se, it just requires

1244

# transactional table.

1245

1246

flush status;

1247

show status like "binlog_cache_use";

1248

show status like "binlog_cache_disk_use";

1249

1250

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

1251

1252

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

1253

# transaction binlog will be flushed to disk...

1254

let $1=2000;

1255

disable_query_log;

1256

begin;

1257

while ($1)

1258

{

1259

eval insert into t1 values( $1 );

1260

dec $1;

1261

}

1262

commit;

1263

enable_query_log;

1264

show status like "binlog_cache_use";

1265

show status like "binlog_cache_disk_use";

1266

1267

# Transaction which should not be flushed to disk and so should not

1268

# increase binlog_cache_disk_use.

1269

begin;

1270

delete from t1;

1271

commit;

1272

show status like "binlog_cache_use";

1273

show status like "binlog_cache_disk_use";

1274

drop table t1;

1275

1276

1277

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

1278

1279

--error 1060

1280

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

1281

--error 1060

1282

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

1283

--error 1060

1284

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

1285

--error 1060

1286

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

1287

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

1288

--error 1060

1289

alter table t1 add key (c1,c1);

1290

--error 1060

1291

alter table t1 add key (c2,c1,c1);

1292

--error 1060

1293

alter table t1 add key (c1,c2,c1);

1294

--error 1060

1295

alter table t1 add key (c1,c1,c2);

1296

drop table t1;

1297

1298

1299

# Bug #4082: integer truncation

1300

1301

1302

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

1303

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

1304

select distinct concat(a, b) from t1;

1305

drop table t1;

1306

1307

if ($fulltext_query_unsupported)

1308

{

1309

1310

# BUG#7709 test case - Boolean fulltext query against unsupported

1311

# engines does not fail

1312

1313

1314

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

1315

--error 1214

1316

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

1317

DROP TABLE t1;

1318

}

1319

1320

if ($test_foreign_keys)

1321

{

1322

1323

# check null values #1

1324

1325

1326

--disable_warnings

1327

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

1328

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

1329

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

1330

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

1331

--enable_warnings

1332

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

1333

SELECT * FROM (SELECT t1.*,GROUP_CONCAT(t2.b_id SEPARATOR ',') as b_list FROM (t1 LEFT JOIN (t2) on t1.a_id = t2.b_a) GROUP BY t1.a_id ) AS xyz;

1334

DROP TABLE t2;

1335

DROP TABLE t1;

1336

}

1337

1338

1339

# Bug#11816 - Truncate table doesn't work with temporary innodb tables

1340

# This is not an innodb bug, but we test it using innodb.

1341

1342

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

1343

insert into t1 values (4711);

1344

truncate t1;

1345

insert into t1 values (42);

1346

select * from t1;

1347

drop table t1;

1348

# Show that it works with permanent tables too.

1349

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

1350

insert into t1 values (4711);

1351

truncate t1;

1352

insert into t1 values (42);

1353

select * from t1;

1354

drop table t1;

1355

1356

1357

# Bug #13025 Server crash during filesort

1358

1359

1360

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

1361

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

1362

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

1363

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

1364

drop table t1;

1365

1366

1367

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

1368

1369

1370

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

1371

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

1372

select min(a) from t1;

1373

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

1374

drop table t1;

1375

1376

# End of 4.1 tests

1377

1378

1379

# range optimizer problem

1380

1381

1382

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

1383

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

1384

select * from t1;

1385

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

1386

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

1387

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

1388

select count(*) from t1 where x < -16;

1389

select count(*) from t1 where x = -16;

1390

explain select count(*) from t1 where x > -16;

1391

select count(*) from t1 where x > -16;

1392

select * from t1 where x > -16;

1393

select count(*) from t1 where x = 18446744073709551601;

1394

drop table t1;

1395

1396

# Please do not remove the following skipped InnoDB specific tests.

1397

# They make the synchronization with innodb.test easier and give

1398

# an idea what to test on other storage engines.

1399

if (0)

1400

{

1401

1402

# Test for testable InnoDB status variables. This test

1403

# uses previous ones(pages_created, rows_deleted, ...).

1404

show status like "Innodb_buffer_pool_pages_total";

1405

show status like "Innodb_page_size";

1406

show status like "Innodb_rows_deleted";

1407

show status like "Innodb_rows_inserted";

1408

show status like "Innodb_rows_updated";

1409

1410

# Test for row locks InnoDB status variables.

1411

show status like "Innodb_row_lock_waits";

1412

show status like "Innodb_row_lock_current_waits";

1413

show status like "Innodb_row_lock_time";

1414

show status like "Innodb_row_lock_time_max";

1415

show status like "Innodb_row_lock_time_avg";

1416

1417

# Test for innodb_sync_spin_loops variable

1418

show variables like "innodb_sync_spin_loops";

1419

set global innodb_sync_spin_loops=1000;

1420

show variables like "innodb_sync_spin_loops";

1421

set global innodb_sync_spin_loops=0;

1422

show variables like "innodb_sync_spin_loops";

1423

set global innodb_sync_spin_loops=20;

1424

show variables like "innodb_sync_spin_loops";

1425

1426

# Test for innodb_thread_concurrency variable

1427

show variables like "innodb_thread_concurrency";

1428

set global innodb_thread_concurrency=1001;

1429

show variables like "innodb_thread_concurrency";

1430

set global innodb_thread_concurrency=0;

1431

show variables like "innodb_thread_concurrency";

1432

set global innodb_thread_concurrency=16;

1433

show variables like "innodb_thread_concurrency";

1434

1435

# Test for innodb_concurrency_tickets variable

1436

show variables like "innodb_concurrency_tickets";

1437

set global innodb_concurrency_tickets=1000;

1438

show variables like "innodb_concurrency_tickets";

1439

set global innodb_concurrency_tickets=0;

1440

show variables like "innodb_concurrency_tickets";

1441

set global innodb_concurrency_tickets=500;

1442

show variables like "innodb_concurrency_tickets";

1443

1444

# Test for innodb_thread_sleep_delay variable

1445

show variables like "innodb_thread_sleep_delay";

1446

set global innodb_thread_sleep_delay=100000;

1447

show variables like "innodb_thread_sleep_delay";

1448

set global innodb_thread_sleep_delay=0;

1449

show variables like "innodb_thread_sleep_delay";

1450

set global innodb_thread_sleep_delay=10000;

1451

show variables like "innodb_thread_sleep_delay";

1452

1453

}

1454

1455

1456

1457

# Test varchar

1458

1459

1460

let $default=`select @@storage_engine`;

1461

eval set storage_engine=$engine_type;

1462

source include/varchar.inc;

1463

1464

1465

# Some errors/warnings on create

1466

1467

1468

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

1469

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

1470

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

1471

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

1472

drop table t1;

1473

create table t1 (v varchar(65536));

1474

show create table t1;

1475

drop table t1;

1476

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

1477

show create table t1;

1478

drop table t1;

1479

1480

eval set storage_engine=$default;

1481

1482

# InnoDB specific varchar tests

1483

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

1484

drop table t1;

1485

1486

1487

# BUG#11039 Wrong key length in min()

1488

1489

1490

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

1491

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

1492

select min(a) from t1;

1493

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

1494

drop table t1;

1495

1496

1497

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

1498

1499

1500

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

1501

insert into t1 (b) values (1);

1502

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

1503

select * from t1;

1504

truncate table t1;

1505

insert into t1 (b) values (1);

1506

replace into t1 (b) values (2);

1507

replace into t1 (b) values (1);

1508

replace into t1 (b) values (3);

1509

select * from t1;

1510

drop table t1;

1511

1512

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

1513

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

1514

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

1515

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

1516

--error ER_DUP_ENTRY

1517

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

1518

select * from t1;

1519

drop table t1;

1520

1521

if ($no_autoinc_update)

1522

{

1523

1524

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

1525

1526

1527

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

1528

insert into t1 (val) values (1);

1529

update t1 set a=2 where a=1;

1530

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

1531

--error ER_DUP_ENTRY

1532

insert into t1 (val) values (1);

1533

select * from t1;

1534

drop table t1;

1535

}

1536

1537

1538

1539

# Bug#10465: DECIMAL, crash on DELETE (InnoDB only)

1540

1541

1542

--disable_warnings

1543

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

1544

--enable_warnings

1545

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

1546

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

1547

SELECT GRADE FROM t1 WHERE GRADE= 151;

1548

DROP TABLE t1;

1549

1550

1551

# Bug #12340 multitable delete deletes only one record

1552

1553

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

1554

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

1555

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

1556

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

1557

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

1558

select * from t1;

1559

drop table t1,t2;

1560

1561

if ($test_foreign_keys)

1562

{

1563

1564

# Test that the slow TRUNCATE implementation resets autoincrement columns

1565

# (bug #11946)

1566

1567

1568

eval CREATE TABLE t1 (

1569

id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)

1570

) ENGINE=$engine_type;

1571

1572

eval CREATE TABLE t2 (

1573

id INTEGER NOT NULL,

1574

FOREIGN KEY (id) REFERENCES t1 (id)

1575

) ENGINE=$engine_type;

1576

1577

INSERT INTO t1 (id) VALUES (NULL);

1578

SELECT * FROM t1;

1579

TRUNCATE t1;

1580

INSERT INTO t1 (id) VALUES (NULL);

1581

SELECT * FROM t1;

1582

1583

# continued from above; test that doing a slow TRUNCATE on a table with 0

1584

# rows resets autoincrement columns

1585

DELETE FROM t1;

1586

TRUNCATE t1;

1587

INSERT INTO t1 (id) VALUES (NULL);

1588

SELECT * FROM t1;

1589

DROP TABLE t2, t1;

1590

1591

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

1592

eval CREATE TABLE t1

1593

(

1594

id INT PRIMARY KEY

1595

) ENGINE=$engine_type;

1596

1597

--error 1005,1005

1598

eval CREATE TEMPORARY TABLE t2

1599

(

1600

id INT NOT NULL PRIMARY KEY,

1601

b INT,

1602

FOREIGN KEY (b) REFERENCES test.t1(id)

1603

) ENGINE=$engine_type;

1604

DROP TABLE t1;

1605

}

1606

# End of FOREIGN KEY test

1607

1608

# Please do not remove the following skipped InnoDB specific tests.

1609

# They make the synchronization with innodb.test easier and give

1610

# an idea what to test on other storage engines.

1611

if (0)

1612

{

1613

1614

1615

# Test that index column max sizes are honored (bug #13315)

1616

1617

1618

# prefix index

1619

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

1620

character set = latin1 engine = $engine_type;

1621

1622

# normal indexes

1623

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

1624

character set = latin1 engine = $engine_type;

1625

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

1626

character set = latin1 engine = $engine_type;

1627

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

1628

character set = latin1 engine = $engine_type;

1629

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

1630

character set = latin1 engine = $engine_type;

1631

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

1632

character set = latin1 engine = $engine_type;

1633

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

1634

character set = latin1 engine = $engine_type;

1635

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

1636

character set = latin1 engine = $engine_type;

1637

1638

1639

# multi-column indexes are allowed to be longer

1640

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

1641

character set = latin1 engine = $engine_type;

1642

1643

show create table t9;

1644

1645

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

1646

1647

# these should have their index length trimmed

1648

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

1649

character set = latin1 engine = $engine_type;

1650

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

1651

character set = latin1 engine = $engine_type;

1652

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

1653

character set = latin1 engine = $engine_type;

1654

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

1655

character set = latin1 engine = $engine_type;

1656

1657

show create table t1;

1658

1659

drop table t1, t2, t3, t4;

1660

1661

}

1662

# End of skipped test

1663

1664

# Please do not remove the following skipped InnoDB specific tests.

1665

# They make the synchronization with innodb.test easier and give

1666

# an idea what to test on other storage engines.

1667

if (0)

1668

{

1669

1670

# these should be refused

1671

--error 1071

1672

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

1673

character set = latin1 engine = $engine_type;

1674

--error 1071

1675

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

1676

character set = latin1 engine = $engine_type;

1677

--error 1071

1678

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

1679

character set = latin1 engine = $engine_type;

1680

--error 1071

1681

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

1682

character set = latin1 engine = $engine_type;

1683

1684

}

1685

1686

if ($test_foreign_keys)

1687

{

1688

1689

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

1690

1691

1692

eval CREATE TABLE t1

1693

(

1694

id INT PRIMARY KEY

1695

) ENGINE=$engine_type;

1696

1697

eval CREATE TABLE t2

1698

(

1699

v INT,

1700

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

1701

) ENGINE=$engine_type;

1702

1703

--error 1452

1704

INSERT INTO t2 VALUES(2);

1705

1706

INSERT INTO t1 VALUES(1);

1707

INSERT INTO t2 VALUES(1);

1708

1709

--error 1451

1710

DELETE FROM t1 WHERE id = 1;

1711

1712

--error 1217

1713

DROP TABLE t1;

1714

1715

SET FOREIGN_KEY_CHECKS=0;

1716

DROP TABLE t1;

1717

SET FOREIGN_KEY_CHECKS=1;

1718

1719

--error 1452

1720

INSERT INTO t2 VALUES(3);

1721

1722

DROP TABLE t2;

1723

}

1724

# End of FOREIGN tests

1725

1726

if ($test_transactions)

1727

{

1728

1729

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

1730

1731

connect (a,localhost,root,,);

1732

connect (b,localhost,root,,);

1733

connection a;

1734

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

1735

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

1736

set autocommit=0;

1737

checksum table t1;

1738

connection b;

1739

insert into t1 values(3);

1740

connection a;

1741

1742

# Here checksum should not see insert

1743

1744

checksum table t1;

1745

connection a;

1746

commit;

1747

checksum table t1;

1748

commit;

1749

drop table t1;

1750

1751

# autocommit = 1

1752

1753

connection a;

1754

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

1755

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

1756

set autocommit=1;

1757

checksum table t1;

1758

connection b;

1759

set autocommit=1;

1760

insert into t1 values(3);

1761

connection a;

1762

1763

# Here checksum sees insert

1764

1765

checksum table t1;

1766

drop table t1;

1767

1768

connection default;

1769

disconnect a;

1770

disconnect b;

1771

}

1772

1773

# tests for bugs #9802 and #13778

1774

1775

if ($test_foreign_keys)

1776

{

1777

# test that FKs between invalid types are not accepted

1778

1779

set foreign_key_checks=0;

1780

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

1781

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

1782

-- error 1005

1783

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

1784

set foreign_key_checks=1;

1785

drop table t2;

1786

1787

# test that FKs between different charsets are not accepted in CREATE even

1788

# when f_k_c is 0

1789

1790

set foreign_key_checks=0;

1791

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

1792

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

1793

-- error 1005

1794

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

1795

set foreign_key_checks=1;

1796

drop table t1;

1797

1798

# test that invalid datatype conversions with ALTER are not allowed

1799

1800

set foreign_key_checks=0;

1801

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

1802

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

1803

-- error 1025,1025

1804

alter table t1 modify column a int;

1805

set foreign_key_checks=1;

1806

drop table t2,t1;

1807

1808

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

1809

1810

set foreign_key_checks=0;

1811

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

1812

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

1813

alter table t1 convert to character set utf8;

1814

set foreign_key_checks=1;

1815

drop table t2,t1;

1816

1817

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

1818

1819

set foreign_key_checks=0;

1820

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

1821

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

1822

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

1823

-- error 1025

1824

rename table t3 to t1;

1825

set foreign_key_checks=1;

1826

drop table t2,t3;

1827

1828

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

1829

1830

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

1831

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

1832

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

1833

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

1834

1835

insert into t1 values(1);

1836

insert into t3 values(1);

1837

-- error 1452

1838

insert into t2 values(2);

1839

-- error 1452

1840

insert into t4 values(2);

1841

insert into t2 values(1);

1842

insert into t4 values(1);

1843

-- error 1451

1844

update t1 set a=2;

1845

-- error 1452

1846

update t2 set a=2;

1847

-- error 1451

1848

update t3 set a=2;

1849

-- error 1452

1850

update t4 set a=2;

1851

-- error 1451

1852

truncate t1;

1853

-- error 1451

1854

truncate t3;

1855

truncate t2;

1856

truncate t4;

1857

truncate t1;

1858

truncate t3;

1859

1860

drop table t4,t3,t2,t1;

1861

}

1862

# End of FOREIGN KEY tests

1863

1864

1865

# Please do not remove the following skipped InnoDB specific tests.

1866

# They make the synchronization with innodb.test easier and give

1867

# an idea what to test on other storage engines.

1868

if (0)

1869

{

1870

1871

1872

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

1873

1874

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

1875

b varchar(255) character set utf8,

1876

c varchar(255) character set utf8,

1877

d varchar(255) character set utf8,

1878

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

1879

drop table t1;

1880

--error ER_TOO_LONG_KEY

1881

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

1882

b varchar(255) character set utf8,

1883

c varchar(255) character set utf8,

1884

d varchar(255) character set utf8,

1885

e varchar(255) character set utf8,

1886

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

1887

1888

1889

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

1890

1891

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

1892

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

1893

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

1894

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

1895

1896

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

1897

-- error ER_DUP_ENTRY

1898

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

1899

insert into t2 values (0x41),(0x4120);

1900

-- error ER_DUP_ENTRY

1901

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

1902

insert into t3 values (0x41),(0x4100);

1903

-- error ER_DUP_ENTRY

1904

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

1905

insert into t4 values (0x41),(0x4100);

1906

select hex(s1) from t1;

1907

select hex(s1) from t2;

1908

select hex(s1) from t3;

1909

select hex(s1) from t4;

1910

drop table t1,t2,t3,t4;

1911

}

1912

1913

if (test_foreign_keys)

1914

{

1915

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

1916

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

1917

1918

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

1919

-- error 1452

1920

insert into t2 values(0x42);

1921

insert into t2 values(0x41);

1922

select hex(s1) from t2;

1923

update t1 set s1=0x123456 where a=2;

1924

select hex(s1) from t2;

1925

-- error 1451

1926

update t1 set s1=0x12 where a=1;

1927

-- error 1451

1928

update t1 set s1=0x12345678 where a=1;

1929

-- error 1451

1930

update t1 set s1=0x123457 where a=1;

1931

update t1 set s1=0x1220 where a=1;

1932

select hex(s1) from t2;

1933

update t1 set s1=0x1200 where a=1;

1934

select hex(s1) from t2;

1935

update t1 set s1=0x4200 where a=1;

1936

select hex(s1) from t2;

1937

-- error 1451

1938

delete from t1 where a=1;

1939

delete from t1 where a=2;

1940

update t2 set s1=0x4120;

1941

-- error 1451

1942

delete from t1;

1943

delete from t1 where a!=3;

1944

select a,hex(s1) from t1;

1945

select hex(s1) from t2;

1946

1947

drop table t2,t1;

1948

1949

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

1950

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

1951

1952

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

1953

insert into t2 values(0x41);

1954

select hex(s1) from t2;

1955

update t1 set s1=0x1234 where a=1;

1956

select hex(s1) from t2;

1957

update t1 set s1=0x12 where a=2;

1958

select hex(s1) from t2;

1959

delete from t1 where a=1;

1960

-- error 1451

1961

delete from t1 where a=2;

1962

select a,hex(s1) from t1;

1963

select hex(s1) from t2;

1964

1965

drop table t2,t1;

1966

}

1967

# End FOREIGN KEY tests

1968

1969

if ($test_foreign_keys)

1970

{

1971

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

1972

# generated foreign key identifier. (Bug #16387)

1973

1974

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

1975

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

1976

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

1977

ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;

1978

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

1979

ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0;

1980

SHOW CREATE TABLE t2;

1981

DROP TABLE t2,t1;

1982

}

1983

1984

1985

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

1986

1987

1988

connect (a,localhost,root,,);

1989

connect (b,localhost,root,,);

1990

connection a;

1991

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

1992

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

1993

commit;

1994

connection b;

1995

set autocommit = 0;

1996

update t1 set b = 5 where a = 2;

1997

connection a;

1998

delimiter |;

1999

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

2000

delimiter ;|

2001

set autocommit = 0;

2002

connection a;

2003

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

2004

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

2005

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

2006

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

2007

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

2008

connection b;

2009

commit;

2010

connection a;

2011

commit;

2012

drop trigger t1t;

2013

drop table t1;

2014

disconnect a;

2015

disconnect b;

2016

2017

# Another trigger test

2018

2019

connect (a,localhost,root,,);

2020

connect (b,localhost,root,,);

2021

connection a;

2022

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

2023

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

2024

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

2025

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

2026

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

2027

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

2028

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

2029

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

2030

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

2031

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

2032

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

2033

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

2034

2035

delimiter |;

2036

create trigger t1t before insert on t1 for each row begin

2037

INSERT INTO t2 SET a = NEW.a;

2038

end |

2039

2040

create trigger t2t before insert on t2 for each row begin

2041

DELETE FROM t3 WHERE a = NEW.a;

2042

end |

2043

2044

create trigger t3t before delete on t3 for each row begin

2045

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

2046

end |

2047

2048

create trigger t4t before update on t4 for each row begin

2049

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

2050

end |

2051

delimiter ;|

2052

commit;

2053

set autocommit = 0;

2054

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

2055

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

2056

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

2057

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

2058

insert into t5(a) values(20);

2059

connection b;

2060

set autocommit = 0;

2061

insert into t1(a) values(7);

2062

insert into t2(a) values(8);

2063

delete from t2 where a = 3;

2064

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

2065

commit;

2066

drop trigger t1t;

2067

drop trigger t2t;

2068

drop trigger t3t;

2069

drop trigger t4t;

2070

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

2071

connection default;

2072

disconnect a;

2073

disconnect b;

2074

2075

if ($test_foreign_keys)

2076

{

2077

2078

# Test that cascading updates leading to duplicate keys give the correct

2079

# error message (bug #9680)

2080

2081

2082

eval CREATE TABLE t1 (

2083

field1 varchar(8) NOT NULL DEFAULT '',

2084

field2 varchar(8) NOT NULL DEFAULT '',

2085

PRIMARY KEY (field1, field2)

2086

) ENGINE=$engine_type;

2087

2088

eval CREATE TABLE t2 (

2089

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

2090

FOREIGN KEY (field1) REFERENCES t1 (field1)

2091

ON DELETE CASCADE ON UPDATE CASCADE

2092

) ENGINE=$engine_type;

2093

2094

INSERT INTO t1 VALUES ('old', 'somevalu');

2095

INSERT INTO t1 VALUES ('other', 'anyvalue');

2096

2097

INSERT INTO t2 VALUES ('old');

2098

INSERT INTO t2 VALUES ('other');

2099

2100

--error ER_FOREIGN_DUPLICATE_KEY

2101

UPDATE t1 SET field1 = 'other' WHERE field2 = 'somevalu';

2102

2103

DROP TABLE t2;

2104

DROP TABLE t1;

2105

2106

2107

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

2108

2109

eval create table t1 (

2110

c1 bigint not null,

2111

c2 bigint not null,

2112

primary key (c1),

2113

unique key (c2)

2114

) engine=$engine_type;

2115

2116

eval create table t2 (

2117

c1 bigint not null,

2118

primary key (c1)

2119

) engine=$engine_type;

2120

2121

alter table t1 add constraint c2_fk foreign key (c2)

2122

references t2(c1) on delete cascade;

2123

show create table t1;

2124

2125

alter table t1 drop foreign key c2_fk;

2126

show create table t1;

2127

2128

drop table t1, t2;

2129

}

2130

# End FOREIGN KEY test

2131

2132

2133

# Bug #14360: problem with intervals

2134

2135

2136

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

2137

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

2138

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

2139

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

2140

select * from t1, t2

2141

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

2142

drop table t1, t2;

2143

2144

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

2145

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

2146

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

2147

primary key(id)) engine=$engine_type;

2148

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

2149

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

2150

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

2151

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

2152

commit;

2153

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

2154

WHERE mm.id IS NULL;

2155

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

2156

where mm.id is null lock in share mode;

2157

drop table t1,t2;

2158

2159

2160

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

2161

# update (because READ COMMITTED isolation level)

2162

2163

2164

connect (a,localhost,root,,);

2165

connect (b,localhost,root,,);

2166

connection a;

2167

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

2168

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

2169

commit;

2170

set autocommit = 0;

2171

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2172

update t1 set b = 5 where b = 1;

2173

connection b;

2174

set autocommit = 0;

2175

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2176

2177

# X-lock to record (7,3) should be released in a update

2178

2179

select * from t1 where a = 7 and b = 3 for update;

2180

connection a;

2181

commit;

2182

connection b;

2183

commit;

2184

drop table t1;

2185

connection default;

2186

disconnect a;

2187

disconnect b;

2188

2189

if ($test_transactions)

2190

{

2191

2192

# Test case where no locks should be released (because we are not

2193

# using READ COMMITTED isolation level)

2194

2195

2196

connect (a,localhost,root,,);

2197

connect (b,localhost,root,,);

2198

connection a;

2199

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

2200

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

2201

commit;

2202

set autocommit = 0;

2203

select * from t1 lock in share mode;

2204

update t1 set b = 5 where b = 1;

2205

connection b;

2206

set autocommit = 0;

2207

2208

# S-lock to records (2,2),(4,2), and (6,2) should not be released in a update

2209

2210

--error 1205

2211

select * from t1 where a = 2 and b = 2 for update;

2212

2213

# X-lock to record (1,1),(3,1),(5,1) should not be released in a update

2214

2215

--error 1205

2216

connection a;

2217

commit;

2218

connection b;

2219

commit;

2220

connection default;

2221

disconnect a;

2222

disconnect b;

2223

drop table t1;

2224

2225

2226

# Consistent read should be used in following selects

2227

2228

# 1) INSERT INTO ... SELECT

2229

# 2) UPDATE ... = ( SELECT ...)

2230

# 3) CREATE ... SELECT

2231

2232

connect (a,localhost,root,,);

2233

connect (b,localhost,root,,);

2234

connection a;

2235

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

2236

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

2237

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

2238

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

2239

commit;

2240

set autocommit = 0;

2241

select * from t2 for update;

2242

connection b;

2243

set autocommit = 0;

2244

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2245

insert into t1 select * from t2;

2246

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

2247

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

2248

select * from t2;

2249

commit;

2250

connection a;

2251

commit;

2252

connection default;

2253

disconnect a;

2254

disconnect b;

2255

drop table t1, t2, t3;

2256

2257

2258

# Consistent read should not be used if

2259

2260

# (a) isolation level is serializable OR

2261

# (b) select ... lock in share mode OR

2262

# (c) select ... for update

2263

2264

# in following queries:

2265

2266

# 1) INSERT INTO ... SELECT

2267

# 2) UPDATE ... = ( SELECT ...)

2268

# 3) CREATE ... SELECT

2269

2270

connect (a,localhost,root,,);

2271

eval SET SESSION STORAGE_ENGINE = $engine_type;

2272

connect (b,localhost,root,,);

2273

eval SET SESSION STORAGE_ENGINE = $engine_type;

2274

connect (c,localhost,root,,);

2275

eval SET SESSION STORAGE_ENGINE = $engine_type;

2276

connect (d,localhost,root,,);

2277

eval SET SESSION STORAGE_ENGINE = $engine_type;

2278

connect (e,localhost,root,,);

2279

eval SET SESSION STORAGE_ENGINE = $engine_type;

2280

connect (f,localhost,root,,);

2281

eval SET SESSION STORAGE_ENGINE = $engine_type;

2282

connect (g,localhost,root,,);

2283

eval SET SESSION STORAGE_ENGINE = $engine_type;

2284

connect (h,localhost,root,,);

2285

eval SET SESSION STORAGE_ENGINE = $engine_type;

2286

connect (i,localhost,root,,);

2287

eval SET SESSION STORAGE_ENGINE = $engine_type;

2288

connect (j,localhost,root,,);

2289

eval SET SESSION STORAGE_ENGINE = $engine_type;

2290

connection a;

2291

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

2292

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

2293

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

2294

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

2295

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

2296

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

2297

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

2298

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

2299

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

2300

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

2301

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

2302

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

2303

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

2304

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

2305

commit;

2306

set autocommit = 0;

2307

select * from t2 for update;

2308

connection b;

2309

set autocommit = 0;

2310

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2311

--send

2312

insert into t1 select * from t2;

2313

connection c;

2314

set autocommit = 0;

2315

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2316

--send

2317

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

2318

connection d;

2319

set autocommit = 0;

2320

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2321

--send

2322

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

2323

connection e;

2324

set autocommit = 0;

2325

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2326

--send

2327

insert into t5 (select * from t2 lock in share mode);

2328

connection f;

2329

set autocommit = 0;

2330

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2331

--send

2332

update t6 set e = (select b from t2 where a = d lock in share mode);

2333

connection g;

2334

set autocommit = 0;

2335

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2336

--send

2337

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

2338

connection h;

2339

set autocommit = 0;

2340

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2341

--send

2342

insert into t8 (select * from t2 for update);

2343

connection i;

2344

set autocommit = 0;

2345

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2346

--send

2347

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

2348

connection j;

2349

set autocommit = 0;

2350

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2351

--send

2352

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

2353

2354

connection b;

2355

--error 1205

2356

reap;

2357

2358

connection c;

2359

--error 1205

2360

reap;

2361

2362

connection d;

2363

--error 1205

2364

reap;

2365

2366

connection e;

2367

--error 1205

2368

reap;

2369

2370

connection f;

2371

--error 1205

2372

reap;

2373

2374

connection g;

2375

--error 1205

2376

reap;

2377

2378

connection h;

2379

--error 1205

2380

reap;

2381

2382

connection i;

2383

--error 1205

2384

reap;

2385

2386

connection j;

2387

--error 1205

2388

reap;

2389

2390

connection a;

2391

commit;

2392

2393

connection default;

2394

disconnect a;

2395

disconnect b;

2396

disconnect c;

2397

disconnect d;

2398

disconnect e;

2399

disconnect f;

2400

disconnect g;

2401

disconnect h;

2402

disconnect i;

2403

disconnect j;

2404

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

2405

}

2406

# End transactional tests

2407

2408

if (test_foreign_keys)

2409

{

2410

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

2411

--error 1005

2412

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

2413

2414

2415

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

2416

2417

2418

eval CREATE TABLE t1 (

2419

a BIGINT(20) NOT NULL,

2420

PRIMARY KEY (a)

2421

) ENGINE=$engine_type DEFAULT CHARSET=UTF8;

2422

2423

eval CREATE TABLE t2 (

2424

a BIGINT(20) NOT NULL,

2425

b VARCHAR(128) NOT NULL,

2426

c TEXT NOT NULL,

2427

PRIMARY KEY (a,b),

2428

KEY idx_t2_b_c (b,c(200)),

2429

CONSTRAINT t_fk FOREIGN KEY (a) REFERENCES t1 (a)

2430

ON DELETE CASCADE

2431

) ENGINE=$engine_type DEFAULT CHARSET=UTF8;

2432

2433

INSERT INTO t1 VALUES (1);

2434

INSERT INTO t2 VALUES (1, 'bar', 'vbar');

2435

INSERT INTO t2 VALUES (1, 'BAR2', 'VBAR');

2436

INSERT INTO t2 VALUES (1, 'bar_bar', 'bibi');

2437

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

2438

2439

SELECT * FROM t2 WHERE b = 'customer_over';

2440

SELECT * FROM t2 WHERE BINARY b = 'customer_over';

2441

SELECT DISTINCT p0.a FROM t2 p0 WHERE p0.b = 'customer_over';

2442

/* Bang: Empty result set, above was expected: */

2443

SELECT DISTINCT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';

2444

SELECT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';

2445

2446

drop table t2, t1;

2447

}

2448

2449

if ($no_spatial_key)

2450

{

2451

2452

# Bug #15680 (SPATIAL key in innodb)

2453

2454

--error ER_TABLE_CANT_HANDLE_SPKEYS

2455

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

2456

}

2457

2458

2459

# Test optimize on table with open transaction

2460

2461

2462

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

2463

BEGIN;

2464

INSERT INTO t1 VALUES (1);

2465

OPTIMIZE TABLE t1;

2466

DROP TABLE t1;

2467

2468

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

2469

# #

2470

# This is derivate of t/innodb.test and has to be maintained by #

2471

# MySQL guys only. #

2472

# #

2473

# Please synchronize this file from time to time with t/innodb.test. #

2474

# Please, DO NOT create a toplevel testcase innodb-mix2.test, because #

2475

# innodb.test does already these tests. #

2476

# #

2477

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

Older »